[QUOTE=slxia1;221396]Hi rpeare,
Thanks for getting back to me.
Take "April" as the folder and I have 20140401, after it is processed, it remains there. The second day, I create 20140402 and so forth.
At the end of the month, I want to bulk import all into ACCESS.
Below is where I am up, it is able to import (VERY HAPPY), but there are 2 issues with it at the moment.
1. When importing, it does NOT recognise the first row has headings. Hence returning the result, "cannot find F1 in Table1".
2. I am not sure how to include cycling the files. Reading that link you gave me, I understand a little bit, but unsure how to incorperate the code into the below.
1. Where you have FALSE in your transferspreadsheet command it should be TRUE if you want to ignore the first row, FALSE if you want to include the first row. From what you're saying your data starts on the second row so you want that value to be TRUE
Code:
Sub import()
Dim strFile As String
strFile = InputBox("Workbookname.xls")
DoCmd.TransferSpreadsheet 0, acSpreadsheetTypeExcel9, "FUM", "F:\April\" & strFile & ".xlsx", False, "Sheet1!"
End Sub
Let's not go into subfolders for this example This code will cycle through c:\scripts and get your file names. If it is correctly doing that (in other words it's correctly listing all the files in the folder)
Code:
Set objFSO = CreateObject("Scripting.FileSystemObject")
objStartFolder = "C:\Scripts"
Set objFolder = objFSO.GetFolder(objStartFolder)
Wscript.Echo objFolder.Path
Set colFiles = objFolder.Files
For Each objFile in colFiles
sFileName = objFile.Name
if instr(sfilename, ".xls") > 0 'this is a clunky way of saying if the file is an excel file
DoCmd.TransferSpreadsheet 0, acSpreadsheetTypeExcel9, "FUM", "c:\scripts\" & sFileName, True, "Sheet1!"
end if
Next
Wscript.Echo
Be advised that if the root spreadsheet(s) get updated you will have to be a little cannier than bulk importing data if you will be performing the same import for the same file more than once with the possibility that the data has changed over time.