Importing Excel spreadsheets from a folder

  • Thread starter Thread starter Westeral
  • Start date Start date
W

Westeral

I currently use Ken Snell's browse to a folder dialogue and pick a file to
import into a table. Since there are multiple files of the same format but
with different names I wanted to use his browse to a folder and import all
the Excel files in that folder and then delete them. I can get it to browse
to the right folder but it does not import or delete the file. His code
follows (I do have the module for the API also). Am I missing something or is
there an error in the code or something I have to add? Thanks.


Private Sub Command23_Click()
Dim strPathFile As String, strFile As String, strPath As String
Dim strTable As String, strBrowseMsg As String
Dim blnHasFieldNames As Boolean

' Change this next line to True if the first row in EXCEL worksheet
' has field names
blnHasFieldNames = True

strBrowseMsg = "Select the folder that contains the EXCEL files:"
strPath = BrowseFolder(strBrowseMsg)

If strPath = "" Then
MsgBox "No folder was selected.", vbOK, "No Selection"
Exit Sub
End If

' Replace tablename with the real name of the table into which
' the data are to be imported
strTable = "tblCaseReviews"

strFile = Dir(strPath & "*.xls")
Do While Len(strFile) > 0
strPathFile = strPath & strFile
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, strTable,
strPathFile, blnHasFieldNames

' Uncomment out the next code step if you want to delete the
' EXCEL file after it's been imported
Kill strPathFile

strFile = Dir()
Loop




End Sub
 
thanks alot mr ken.I Was searching for such code
but I need your help

my excel sheets are saved in the text (tab delimited ) format
thats why i am getting error saying that the imported files are not in the
expected format.
please help as I Have more that 200 files to be imported.
thanks again

note that I am talking about the code above




Ken Snell said:
I've fixed the code example at my website. Thanks, guys.

--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/
Click to expand...
 
Use TransferText to import tab-delimited text files. It's syntax is similar
to TransferSpreadsheet, but you'll need to use an Import Specification to
tell ACCESS that the file is delimited by tab characters.

--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/
 
Back
Top