Transferring Data from Multiple Excel files to single excel files.


S

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.
 
Ad

Advertisements

Ad

Advertisements

S

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 OLEDB:Database P"
_
, _
"assword="""";Jet OLEDB:Engine Type=35;Jet OLEDB:Database 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" _
, _
"EDB:Don'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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top