Transferring Data from Multiple Excel files to single excel files.

[Sunil Pradhan]

I have multiple excel data files e.g. a.xls, b.xls, c.xls, etc. & one master
data file.

a, b, c, etc are in one directory & master data is in another directory.
Also sometime I may have only a & c file available but no file b. How do i
transfer the data from a, b, c, etc to master data file without opening any
one of them. I know it can be done with ado connect. but i'm lost with
codings.

Please advise.

 

[Ron de Bruin]

Hi Sunil

Start here
http://www.rondebruin.nl/ado.htm

But I like the add-in or code examples at the top of the page that opne the workbook
Much more control then

 

[statum]

Here is some code that will import data from another worksheet on another
workbook. Note that you will need to put a click box somewhere on one of the
sheets of the workbook (NOT the sheet you want to import the data to) in
order to activate the this code.

Sub ImportData()

Sheets("SHEET1").Activate
Response = MsgBox("Are you sure you want to do this?" & Chr(13) & "This will
delete any current data on this worksheet", vbYesNo)
If Response = vbNo Then Exit Sub
Range("A1").Select
Filename = Application.InputBox(Prompt:="Enter the EXACT File Name of the
workbook you wish" & Chr(13) & "to import from the ExcelDATA folder on the S
drive: ", Type:=2)

With ActiveSheet.QueryTables.Add(Connection:=Array( _
"OLEDB;Provider=Microsoft.Jet.OLEDB.4.0;Password="""";User
ID=Admin;Data Source=S:\ExcelDATA\" & Filename & ".xls;M" _
, _
"ode=Share Deny Write;Extended Properties=""HDR=YES;"";Jet
OLEDB:System database="""";Jet OLEDB:Registry Path="""";Jet OLEDBatabase P"
_
, _
"assword="""";Jet OLEDB:Engine Type=35;Jet OLEDBatabase Locking
Mode=0;Jet OLEDB:Global Partial Bulk Ops=2;Jet OLEDB:Global Bulk " _
, _
"Transactions=1;Jet OLEDB:New Database Password="""";Jet
OLEDB:Create System Database=False;Jet OLEDB:Encrypt Database=False;Jet OL" _
, _
"EDBon't Copy Locale on Compact=False;Jet OLEDB:Compact Without
Replica Repair=False;Jet OLEDB:SFP=False" _
), Destination:=Range("A1"))
.CommandType = xlCmdTable
.CommandText = Array("SHEET1$A1:AA10000")
.Name = Filename
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = False
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = False
.AdjustColumnWidth = False
.RefreshPeriod = 0
.PreserveColumnInfo = True
.SourceDataFile = "S:\ExcelDATA\" & Filename & ".xls"
.Refresh BackgroundQuery:=False
.MaintainConnection = False
End With
End Sub