Hi.
I have thousands of excel files in one directory. I want to build up the Access table from excel files.
Each excel file has one worksheet, but I don't need every column and low of the worksheet.
I am trying to import a FILTERED Excel worksheet of every excel files in that directory into an access table using visual basic code.
I was able to build up (someone helped me) visual basic code to import multiple excel files to one single Access table without FILTERING.
Someone can say that I can just import without filtering and do query in Access.
The problem is that each excel worksheet has too many row and columns that I don't need it, and I need to import thousands of excel files to one access table. Eventually I will face Access table size problem.
I need to improve my visual basic code to import only selected rows and columns that I want to import into Access table.
my worksheet is in the following format
ID Month Year Day OPEN HIGH LOW ...VOL..... SETTLE TradeDate.
OD 7 2011 15 ................... 546 7/15/2011
OD 8 2011 18 ..................658 7/15/2011
..
FD 7 2011 15 .................... 987 7/15/2011
..
..
SM 7 2011 15 .................... 632 7/15/2011
...
KW 7 2011 15 .................... 838 7/15/2011
....
All I need to import is to find "FD" and "SM" in the 1st column (ID) and get the rest of the row (or even better one is to get only ID, Month, Year, Day, Settle, and Tradedate data in that row)
Anyone could help me to filter the data in worksheet and import into Access table?
(I use Access 2007 version)
thank you very much.
FYI, the following is the visual basic code that WORKS for importing multiple excel files to one single Access table without FILTERING.
Sub Import_multiple_excel_files()
Const strPath As String = "C:\Price Data\NYMEX\2011_test" 'Directory Path
Dim strFile As String 'Filename
Dim strFileList() As String 'File Array
Dim intFile As Integer 'File Number
'Loop through the folder & build file list
'strFile = Dir(strPath & "*.csv")
strFile = Dir(strPath & "*.xlsx")
While strFile <> ""
'add files to the list
intFile = intFile + 1
ReDim Preserve strFileList(1 To intFile)
strFileList(intFile) = strFile
strFile = Dir()
Wend
'see if any files were found
If intFile = 0 Then
MsgBox "No files found"
Exit Sub
End If
'cycle through the list of files & import to Access
'creating a new table called importedLMP_t
For intFile = 1 To UBound(strFileList)
DoCmd.TransferText acImportDelimi, , _
"importedNYMEXinAccess", strPath & strFileList(intFile), True
'Check out the TransferText/TransferSpreadsheet options in the Access
'Visual Basic Help file for a full description & list of
'optional settings
Next
MsgBox UBound(strFileList) & " Files were Imported"
End Sub
I have thousands of excel files in one directory. I want to build up the Access table from excel files.
Each excel file has one worksheet, but I don't need every column and low of the worksheet.
I am trying to import a FILTERED Excel worksheet of every excel files in that directory into an access table using visual basic code.
I was able to build up (someone helped me) visual basic code to import multiple excel files to one single Access table without FILTERING.
Someone can say that I can just import without filtering and do query in Access.
The problem is that each excel worksheet has too many row and columns that I don't need it, and I need to import thousands of excel files to one access table. Eventually I will face Access table size problem.
I need to improve my visual basic code to import only selected rows and columns that I want to import into Access table.
my worksheet is in the following format
ID Month Year Day OPEN HIGH LOW ...VOL..... SETTLE TradeDate.
OD 7 2011 15 ................... 546 7/15/2011
OD 8 2011 18 ..................658 7/15/2011
..
FD 7 2011 15 .................... 987 7/15/2011
..
..
SM 7 2011 15 .................... 632 7/15/2011
...
KW 7 2011 15 .................... 838 7/15/2011
....
All I need to import is to find "FD" and "SM" in the 1st column (ID) and get the rest of the row (or even better one is to get only ID, Month, Year, Day, Settle, and Tradedate data in that row)
Anyone could help me to filter the data in worksheet and import into Access table?
(I use Access 2007 version)
thank you very much.
FYI, the following is the visual basic code that WORKS for importing multiple excel files to one single Access table without FILTERING.
Sub Import_multiple_excel_files()
Const strPath As String = "C:\Price Data\NYMEX\2011_test" 'Directory Path
Dim strFile As String 'Filename
Dim strFileList() As String 'File Array
Dim intFile As Integer 'File Number
'Loop through the folder & build file list
'strFile = Dir(strPath & "*.csv")
strFile = Dir(strPath & "*.xlsx")
While strFile <> ""
'add files to the list
intFile = intFile + 1
ReDim Preserve strFileList(1 To intFile)
strFileList(intFile) = strFile
strFile = Dir()
Wend
'see if any files were found
If intFile = 0 Then
MsgBox "No files found"
Exit Sub
End If
'cycle through the list of files & import to Access
'creating a new table called importedLMP_t
For intFile = 1 To UBound(strFileList)
DoCmd.TransferText acImportDelimi, , _
"importedNYMEXinAccess", strPath & strFileList(intFile), True
'Check out the TransferText/TransferSpreadsheet options in the Access
'Visual Basic Help file for a full description & list of
'optional settings
Next
MsgBox UBound(strFileList) & " Files were Imported"
End Sub
Last edited: