PDA

View Full Version : Copy Pasting Data across Excel Sheets using VBA (in background & without clipboard)?



pvuv
10-15-2013, 02:41 AM
Hello All,
I work for charity where I am trying to establish a process of consolidating data.

Case study:
1. I have 3 source Excel workbooks (A.xlsx, B.xlsx, C.xlsx) which contains names, tel numbers and amount donated. I have a master file (master.xlsx) which has a button (update), on clicking it copies data from A, B and C and pastes it Sheet 1, Sheet 2, sheet 3 respectively of Master.xlsx

2. Requirement 1: the data should be copied into master.xlsx without actually opening the A,B,C.xlsx since it will accelerate the process. “i.e. open the sheets in background and Copy”. This will also remove the need to initiate any open or save in the source sheets.

3. Requirement 2: since A,B,C .xlxs are large workbooks (>50mb) I am looking to refrain Windows() and .Activate & .Select method, .Copy & .Paste since it will also copy the formats and is a slower method using more memory which means slower speed. It also removes clipboard use (i think its called direct path but i need for a chunk of cells and not individual)

“SPEED IS THE UNDERLYING TARGET”
4. Requirement 3: A,B,C.xlsx have been known to shift in columns where data is added and subtracted constantly. Hence I want to extract data from A,B,C depending on Heading names.
a. Heading in Master.xlsx: first name, last name, Amount
b. Heading A,B,C.xlsx: name, last name, tel no, Code, Amount, (dont want to link code)

I can’t do a simple extract as that will paste Code aswell and if another column is added in middle, the location will move.

I am attaching A,B,C, Master file. It contains the macros that I have till now. .10708

Your help will be greatly appreciated. I am very new to XL VBA and found these techniques on this website.

Jan Karel Pieterse
10-15-2013, 07:47 AM
Have you considered using the Data, Get External data, From other sources, From Microsoft Query option?

p45cal
10-16-2013, 11:00 AM
For others, cross posted here: http://uk.answers.yahoo.com/question/index?qid=20131015020016AABTpPW (where the link to the file works).

For pvuv, some light reading: http://www.excelguru.ca/content.php?184 so please do what you need to do here and everywhere you've cross-posted to.

Try this for your 3 files/sheets (worked here). See also comments in the code:
Sub blah()
FFolder = "C:\vbaExpress47923\" 'my folder containing the 3 source files and the master file was thus; adjust to suit.
FNames = Array("A", "B", "C") 'the three file names.
FNo = 0
For Each sht In Sheets(Array("Sheet1", "Sheet2", "Sheet3")) 'these are the 3 destination sheets.
FName = FNames(FNo)
Set LO = sht.ListObjects.Add(SourceType:=0, Source:="ODBC;DSN=Excel Files;DBQ=" & FFolder & FName & ".xlsx;DefaultDir=" & FFolder & ";DriverId=1046;MaxBufferSize=2048;PageTimeout=5;", Destination:=sht.Range("$A$1"))
With LO.QueryTable 'this CommandText works for data on a sheet called Sheet1:
.CommandText = "SELECT `'Sheet 1$'`.`First Name`, `'Sheet 1$'`.`Last Name`, `'Sheet 1$'`.Amount FROM `" & FFolder & FName & ".xlsx`.`'Sheet 1$'` `'Sheet 1$'`"
.Refresh BackgroundQuery:=False
End With
'LO.Unlist 'optional if you want to convert the table into a normal range.
FNo = FNo + 1
Next sht
End Sub