Hassan Niazi
10-08-2016, 08:51 AM
Hi All
I need serious help from experts here on the below mentioned issue.
I need to import data (which is in different excel files stored in a folder) into access tables using VBA code.Here is the exact issue I'm facing.
I've 5 excel workbooks in a folder each having 3 worksheets in it. These workbooks change from day to day basis, sometimes 2 sometimes 7 but worksheets remain common i.e 3.
These three worksheets have 4 rows which i don't want to import into access tables (Row 2 to Row 5). I don't want to delete them manually, I want VBA code to exclude these rows when importing.
From all workbooks i want to import different but specific columns into access for example,
From worksheet 1, i need data from Column C to column AL.
From Worksheets 2 and 3, i want data from Column E to Column H.
I don't know how many rows these columns have.
I'm using this code but it is not complete:
Sub Import()
Dim strPathFile As String, strFile As String, strPath As String
Dim blnHasFieldNames As Boolean
Dim intWorksheets As Integer
Dim CountFiles As Integer
strPath = "E:\Import"
strFile = Dir(strPath & "*.xlsx")
CountFiles = 0
Do While Len(strFile) > 0
strPathFile = strPath & strFile
CountFiles = CountFiles + 1
strFile = Dir()
Loop
Dim strWorksheets(1 To CountFiles) As String
Dim strTables(1 To 3) As String
strWorksheets(1) = "WS1"
strWorksheets(2) = "WS2"
strWorksheets(3) = "WS3"
strTables(1) = "T1"
strTables(2) = "T2"
strTables(3) = "T3"
blnHasFieldNames = True
strFile = Dir(strPath & "*.xlsx")
For intWorksheets = 1 To CountFiles
Do While Len(strFile) > 0
strPathFile = strPath & strFile
If strWorksheets(intWorksheets) = "T1" Then
DoCmd.TransferSpreadsheet acImport, _
acSpreadsheetTypeExcel12Xml, strTables(intWorksheets), _
strPathFile, blnHasFieldNames, _
strWorksheets(intWorksheets) & "C:AL"
strFile = Dir()
Else:
DoCmd.TransferSpreadsheet acImport, _
acSpreadsheetTypeExcel12Xml, strTables(intWorksheets), _
strPathFile, blnHasFieldNames, _
strWorksheets(intWorksheets) & "E:H"
strFile = Dir()
End If
Loop
Next intWorksheets
End Sub
Can somebody help me in this regard. Thanks in advance.
I need serious help from experts here on the below mentioned issue.
I need to import data (which is in different excel files stored in a folder) into access tables using VBA code.Here is the exact issue I'm facing.
I've 5 excel workbooks in a folder each having 3 worksheets in it. These workbooks change from day to day basis, sometimes 2 sometimes 7 but worksheets remain common i.e 3.
These three worksheets have 4 rows which i don't want to import into access tables (Row 2 to Row 5). I don't want to delete them manually, I want VBA code to exclude these rows when importing.
From all workbooks i want to import different but specific columns into access for example,
From worksheet 1, i need data from Column C to column AL.
From Worksheets 2 and 3, i want data from Column E to Column H.
I don't know how many rows these columns have.
I'm using this code but it is not complete:
Sub Import()
Dim strPathFile As String, strFile As String, strPath As String
Dim blnHasFieldNames As Boolean
Dim intWorksheets As Integer
Dim CountFiles As Integer
strPath = "E:\Import"
strFile = Dir(strPath & "*.xlsx")
CountFiles = 0
Do While Len(strFile) > 0
strPathFile = strPath & strFile
CountFiles = CountFiles + 1
strFile = Dir()
Loop
Dim strWorksheets(1 To CountFiles) As String
Dim strTables(1 To 3) As String
strWorksheets(1) = "WS1"
strWorksheets(2) = "WS2"
strWorksheets(3) = "WS3"
strTables(1) = "T1"
strTables(2) = "T2"
strTables(3) = "T3"
blnHasFieldNames = True
strFile = Dir(strPath & "*.xlsx")
For intWorksheets = 1 To CountFiles
Do While Len(strFile) > 0
strPathFile = strPath & strFile
If strWorksheets(intWorksheets) = "T1" Then
DoCmd.TransferSpreadsheet acImport, _
acSpreadsheetTypeExcel12Xml, strTables(intWorksheets), _
strPathFile, blnHasFieldNames, _
strWorksheets(intWorksheets) & "C:AL"
strFile = Dir()
Else:
DoCmd.TransferSpreadsheet acImport, _
acSpreadsheetTypeExcel12Xml, strTables(intWorksheets), _
strPathFile, blnHasFieldNames, _
strWorksheets(intWorksheets) & "E:H"
strFile = Dir()
End If
Loop
Next intWorksheets
End Sub
Can somebody help me in this regard. Thanks in advance.