Hello all,
Import Issue:
I am trying to figure out how to import data from a specific worksheet in all excel files in a single folder into separate tables via transferspreadsheet (VB). The problem I am facing is that the below will not work. Can anyone help me edit this?
Option Compare Database
Private Sub Command0_Click()
Dim blnHasFieldNames As Boolean
Dim strWorksheet As String, strTable As String
Dim strPath As String, strPathFile As String
blnHasFieldNames = True
strPath = "C:\Users\"
strWorksheet = "Data"
strFile = Dir(strPath & "*.xls")
Do While Len(strFile) > 0
strPathFile = strPath & strFile
strTable = "tbl_" & Left(strFile, InStrRev(strFile, ".xls") - 1)
DoCmd.TransferSpreadsheet acImport, _
acSpreadsheetTypeExcel9, strTable, strPathFile, _
blnHasFieldNames, strWorksheet & "$"
strFile = Dir()
Loop
End Sub
Export Issue:
I have some code below that will not work. I am trying to display a dialogbox to select a table within a database and then export that table to an excel spreadsheet with the same table name (excluding the .mdb extension). Can anyone help edit this code?Private Sub Form_Click()
Dim fDialog As Office.FileDialog
Dim varFile As Variant
Set fDialog = Application.FileDialog(msoFileDialogFilePicker)
fDialog.Filters.Add "All Files", "*.*"
If fDialog.Show = True Then
For Each varFile In fDialog.SelectedItems
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, _
"MyNewSpreadsheet", varFile, True
Next
End If
End Sub