at the minute i have this in my module
strPath = "C:\Users\USER\Documents\Database Creations\CTR\"
what i would like is
strPath = "inputform.folderlocation.value"
this would mean the user wouldn't have to mess with code and i wouldn't have to fix issues in the future.
I have considered using browse to folder but this is only a sample, there are many other values that might need updating.
Below is the full code i am using, any questions or suggestions would be great!
Thanks
Expand|Select|Wrap|Line Numbers
- Function DoImport()
- Dim strPathFile As String, strFile As String, strPath As String
- Dim blnHasFieldNames As Boolean
- Dim intWorksheets As Integer
- ' Replace 3 with the number of worksheets to be imported
- ' from each EXCEL file
- Dim strWorksheets(1 To 7) As String
- ' Replace 3 with the number of worksheets to be imported
- ' from each EXCEL file (this code assumes that each worksheet
- ' with the same name is being imported into a separate table
- ' for that specific worksheet name)
- Dim strTables(1 To 7) As String
- ' Replace generic worksheet names with the real worksheet names;
- ' add / delete code lines so that there is one code line for
- ' each worksheet that is to be imported from each workbook file
- strWorksheets(1) = "Audit"
- strWorksheets(2) = "ERROR"
- strWorksheets(3) = "FAILED"
- ' Replace generic table names with the real table names;
- ' add / delete code lines so that there is one code line for
- ' each worksheet that is to be imported from each workbook file
- strTables(1) = "Audittable"
- strTables(2) = "ERRORtable"
- strTables(3) = "FAILEDtable"
- ' Change this next line to True if the first row in EXCEL worksheet
- ' has field names
- blnHasFieldNames = True
- ' Replace C:\Documents\ with the real path to the folder that
- ' contains the EXCEL files
- strPath = "C:\Users\USER\Documents\Database Creations\CTR\"
- ' Replace 3 with the number of worksheets to be imported
- ' from each EXCEL file
- For intWorksheets = 1 To 7
- strFile = Dir(strPath & "*.xlsx")
- Do While Len(strFile) > 0
- strPathFile = strPath & strFile
- On Error Resume Next
- DoCmd.TransferSpreadsheet acImport, _
- acSpreadsheetTypeExcel12, strTables(intWorksheets), _
- strPathFile, blnHasFieldNames, _
- strWorksheets(intWorksheets) & "$"
- On Error GoTo 0
- strFile = Dir()
- Loop
- Next intWorksheets
- End Function