I've been reading up on the subject and found several posts on using MSQuery or a Pivot Table, but can't seem to get it together.
I have hundreds of Excel Files of the same format, in the same folder that I need to extract data from. There are 5 columns 16 rows in length that I need to pull from each file into the one consolidated file. The way I am trying to format it is:
S/N CH LP TJ RJ DJ
1 1 # # # #
1 2 # # # #
1 3 # # # #
.... down to 16
1 16 # # # #
2 1 # # # #
2 2 # # # #
and so on...
So basically I have a certain number of rows for one file and then I will need Excel to drop down and insert the new file data immediately following the previous.
The code that I have thus far is as follows:
Sub DemoDIR()
Dim mFile
n = 0
'The Following steps through each .xls file in the dir
mFile = Dir("C:\Documents and Settings\eknox\Desktop\6510DataSet\*.xls")
Do While mFile <> ""
Selection.Activate
'Query Code to pull one column from each file and put into single worksheet of the summary workbook
With ActiveSheet.QueryTables.Add(Connection:=Array(Array( _
"ODBC;DSN=Excel Files;DBQ=C:\Documents and Settings\eknox\Desktop\6510DataSet\mFile;DefaultDir=C:\Documents and Settings" _
), Array( _
"\eknox\Desktop\6510DataSet;DriverId=790;MaxBufferSize=2048;PageTimeout=5;")), _
Destination:=Range("A1"))
.CommandText = Array("SELECT oCell.F1" & Chr(13) & "" & Chr(10) & "FROM oCell oCell")
.Name = "Query from Excel Files"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.PreserveColumnInfo = True
.Refresh BackgroundQuery:=False
End With
' this just displays the name of each file
MsgBox mFile
'Counter
n = n + 1
' go to next entry, that is, the next file ending with .xls
mFile = Dir
Loop
MsgBox n & (" Excel Files are Present in the Directory")
End Sub
The main thing I am having trouble with right now is the importing of the external data from multiple files in a directory. I keep getting errors and nothing seems to want to work. Let me knox if anyone needs clarification on the problem! Thanks for any guidance you can provide!!!
PugnaxX
I have hundreds of Excel Files of the same format, in the same folder that I need to extract data from. There are 5 columns 16 rows in length that I need to pull from each file into the one consolidated file. The way I am trying to format it is:
S/N CH LP TJ RJ DJ
1 1 # # # #
1 2 # # # #
1 3 # # # #
.... down to 16
1 16 # # # #
2 1 # # # #
2 2 # # # #
and so on...
So basically I have a certain number of rows for one file and then I will need Excel to drop down and insert the new file data immediately following the previous.
The code that I have thus far is as follows:
Sub DemoDIR()
Dim mFile
n = 0
'The Following steps through each .xls file in the dir
mFile = Dir("C:\Documents and Settings\eknox\Desktop\6510DataSet\*.xls")
Do While mFile <> ""
Selection.Activate
'Query Code to pull one column from each file and put into single worksheet of the summary workbook
With ActiveSheet.QueryTables.Add(Connection:=Array(Array( _
"ODBC;DSN=Excel Files;DBQ=C:\Documents and Settings\eknox\Desktop\6510DataSet\mFile;DefaultDir=C:\Documents and Settings" _
), Array( _
"\eknox\Desktop\6510DataSet;DriverId=790;MaxBufferSize=2048;PageTimeout=5;")), _
Destination:=Range("A1"))
.CommandText = Array("SELECT oCell.F1" & Chr(13) & "" & Chr(10) & "FROM oCell oCell")
.Name = "Query from Excel Files"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.PreserveColumnInfo = True
.Refresh BackgroundQuery:=False
End With
' this just displays the name of each file
MsgBox mFile
'Counter
n = n + 1
' go to next entry, that is, the next file ending with .xls
mFile = Dir
Loop
MsgBox n & (" Excel Files are Present in the Directory")
End Sub
The main thing I am having trouble with right now is the importing of the external data from multiple files in a directory. I keep getting errors and nothing seems to want to work. Let me knox if anyone needs clarification on the problem! Thanks for any guidance you can provide!!!
PugnaxX
