Append data from an Excel file to another without opening files

[JustATheory]

I run a process that appends data from many excel spreadsheets to one. Columns are the same but can run from 1 record to as many as 4k. I loop through these currently and opening each file highlighting the data copy and pasting in an append process. I'd like to be able to pass the data from the original to the master without having to open these files. Is this possible?

Thanks,
Andy

 

[SkipVought]

Hi,

I do this often.

faq68-5829

This FAQ uses MS query, or you could use ActiveX Data Objects.

Skip,
[sub]
When a diminutive clarvoyant had disappeared from detention, headlines read...
Small Medium at Large[/sub]

 

[Spooku777]

If MS Query is not your style you could use the ActiveX objects as Skip suggested.

To open the workbook in the background:

Set XL = CreateObject("Excel.Application")
Set MasterWbk = XL.Workbooks.Open(FileName:="C:\Temp\MyMasterFile.xls")
FName = "C:\Temp\MyDataFile.xls"
Set Wbk = XL.Workbooks.Open(FName)

Since this does not work with the ActiveWorkbook then select, copy and paste won't work - unless you use the Clipboard object within VBA. (Personally I've had issues with Clipboard but check out GetFromClipboard function if you want stick with copy and paste concepts)

Alternatively, you could assign values by range/cell reference something like this. (This assumes you only want data from a single sheet in the data workbook but you can easily loop through the sheets).

'Find last row in Master
r = MasterWbk.Sheets(1).Cells(65536,1).end(xlup).row

'Data workbook
With Wbk
    Set Sht = .Sheets("SheetName")
    Set Rng = Sht.Range(Sht.Cells(1,1), Sht.SpecialCells(xlLastCell))
    'Data range
    For each cl in Rng
        MasterWbk.Sheets(1).Cells(cl.row + r,cl.column) = cl
    Next
End With

Note: This is not fully debugged and just a suggetion - tweek as needed.


Spook :-j

 

[B827]

Have a look at

http://support.microsoft.com/kb/306397

for using sql to query Excel. As I recall from a previous existance a few years ago this worked very well in classic asp pages and might work here.

Just a thought.

Sandy