I have started with a macro as shown below. Now i need to change the file
location and filenames in possibly a loop. I have about 250 of these files to
combine into one. Is there a way for me to read the filename and locations in
a loop.
I really appreciate this J
Thanks
Faz
Sub Importdata()
'
' NewBook Macro
'
'
Workbooks.Add
'
' gotosheet1
'
'
Sheets("Sheet1").Select
Range("A1").Select
'
' FileLocations
'
'
With ActiveSheet.ListObjects.Add(SourceType:=0, Source:=Array( _
"OLEDB;Provider=Microsoft.ACE.OLEDB.12.0;Password="""";User
ID=Admin;Data
Source=G:\Research\LII\Research\LII\Administration\Clients\NYSE" _
, _
"RDA\File Locations.xls;Mode=Share Deny Write;Extended
Properties=""HDR=YES;"";Jet OLEDB:System database="""";Jet OLEDB:Registry
Path" _
, _
"="""";Jet OLEDB
atabase Password="""";Jet OLEDB:Engine Type=35;Jet
OLEDB
atabase 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:Encry" _
, _
"pt Database=False;Jet OLEDB
on't Copy Locale on Compact=False;Jet
OLEDB:Compact Without Replica Repair=False;Jet OLEDB:SFP=Fals" _
, "e;Jet OLEDB:Support Complex Data=False"),
Destination:=Range("$A$1")). _
QueryTable
.CommandType = xlCmdTable
.CommandText = Array("'File Locations$'")
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.PreserveColumnInfo = True
.SourceDataFile = _
"G:\Research\LII\Research\LII\Administration\Clients\NYSERDA\File
Locations.xls"
.ListObject.DisplayName = "Table_File_Locations"
.Refresh BackgroundQuery:=False
End With
'
' gotosheet2
'
'
Sheets("Sheet2").Select
Range("A1").Select
'
' LIIFN1
'
'
With ActiveSheet.QueryTables.Add(Connection:= _
"TEXT;G:\Research\LII\Research\LII\Administration\Clients\NYSERDA\NYSERDA
Files\22-Jul-2008\24_50DPF_22Jul_LII_2008-07-22 15 39 50 NYSERDA Mass Vs
Time.csv" _
, Destination:=Range("Sheet2!$A$1"))
.Name = "24_50DPF_22Jul_LII_2008-07-22 15 39 50 NYSERDA Mass Vs Time"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.TextFilePromptOnRefresh = False
.TextFilePlatform = 932
.TextFileStartRow = 1
.TextFileParseType = xlDelimited
.TextFileTextQualifier = xlTextQualifierDoubleQuote
.TextFileConsecutiveDelimiter = False
.TextFileTabDelimiter = True
.TextFileSemicolonDelimiter = True
.TextFileCommaDelimiter = True
.TextFileSpaceDelimiter = False
.TextFileColumnDataTypes = Array(1, 1, 1, 1)
.TextFileTrailingMinusNumbers = True
.Refresh BackgroundQuery:=False
End With
Sheets("Sheet2").Select
'
' ECMFN1
'
'
Range("F19").Select
ActiveWindow.WindowState = xlNormal
With ActiveSheet.ListObjects.Add(SourceType:=0, Source:=Array( _
"OLEDB;Provider=Microsoft.ACE.OLEDB.12.0;Password="""";User
ID=Admin;Data
Source=G:\Research\LII\Research\LII\Administration\Clients\NYSE" _
, _
"RDA\NYSERDA Files\ECM
Data\22july08\pp_22JUL08_Test-24_100DPF---1_M01-M02.xls;Mode=Share Deny
Write;Extended Properties=""HDR=YES" _
, _
";"";Jet OLEDB:System database="""";Jet OLEDB:Registry Path="""";Jet
OLEDB
atabase Password="""";Jet OLEDB:Engine Type=35;Jet OLEDB
at" _
, _
"abase 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 OLEDB
on't Copy Locale on Compact=False;Jet OLEDB:" _
, _
"Compact Without Replica Repair=False;Jet OLEDB:SFP=False;Jet
OLEDB:Support Complex Data=False" _
), Destination:=Range("$E$1")).QueryTable
.CommandType = xlCmdTable
.CommandText = Array("'pp_22JUL08_Test-24_100DPF---1_M$'")
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.PreserveColumnInfo = True
.SourceDataFile = _
"G:\Research\LII\Research\LII\Administration\Clients\NYSERDA\NYSERDA
Files\ECM Data\22july08\pp_22JUL08_Test-24_100DPF---1_M01-M02.xls"
.ListObject.DisplayName =
"Table_pp_22JUL08_Test_24_100DPF___1_M01_M02"
.Refresh BackgroundQuery:=False
End With
Columns("F:F").Select
Selection.NumberFormat = "hh:mm:ss;@"
Range("G19").Select
Columns("F:F").ColumnWidth = 10.71
'
' SEMTECHFN1SN1
'
'
With ActiveSheet.ListObjects.Add(SourceType:=0, Source:=Array( _
"OLEDB;Provider=Microsoft.ACE.OLEDB.12.0;Password="""";User
ID=Admin;Data
Source=G:\Research\LII\Research\LII\Administration\Clients\NYSE" _
, _
"RDA\NYSERDA Files\hd eng data\hd eng info july22 23
08.xls;Mode=Share Deny Write;Extended Properties=""HDR=YES;"";Jet
OLEDB:System" _
, _
" database="""";Jet OLEDB:Registry Path="""";Jet OLEDB
atabase
Password="""";Jet OLEDB:Engine Type=35;Jet OLEDB
atabase Locking Mode=" _
, _
"0;Jet OLEDB:Global Partial Bulk Ops=2;Jet OLEDB:Global Bulk
Transactions=1;Jet OLEDB:New Database Password="""";Jet OLEDB:Create S" _
, _
"ystem Database=False;Jet OLEDB:Encrypt Database=False;Jet
OLEDB
on't Copy Locale on Compact=False;Jet OLEDB:Compact Without Rep" _
, "lica Repair=False;Jet OLEDB:SFP=False;Jet OLEDB:Support Complex
Data=False") _
, Destination:=Range("$AA$1")).QueryTable
.CommandType = xlCmdTable
.CommandText = Array("'24_dpf100jul22t1$'")
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.PreserveColumnInfo = True
.SourceDataFile = _
"G:\Research\LII\Research\LII\Administration\Clients\NYSERDA\NYSERDA
Files\hd eng data\hd eng info july22 23 08.xls"
.ListObject.DisplayName = "Table_hd_eng_info_july22_23_08"
.Refresh BackgroundQuery:=False
End With
End Sub