Function and/or Macro to transfer multiple excel to access2007

[sap1958]

Here is my code

Function TransferMultiples()
On Error GoTo TransferIn_Err

DoCmd.TransferSpreadsheet acImport, 10, "invoices", "C:\pcsas_export_files\Quest\vaclients_invoices_\.xls", True, ""

TransferIn_Exit:
Exit Function

TransferIn_Err:
MsgBox Error$
Resume TransferIn_Exit

End Function

I am getting an invalid path error message when I run this function. The spreadsheets are located in
C:\pcsas_export_files\Quest\ The spreadsheets have names like
invoices_20020430.xls
invoices_20030430.xls
invoices_20040430.xls
Any ideas of how I can improve my path? In addition is there a way to accomplish this same thing using an access macro. I can then convert it to vba code

 

[PHV]

A starting point:

Function TransferMultiples()
Dim strDir As String, strFile As String
strDir = "C:\pcsas_export_files\Quest\"
strFile = Dir(strDir & "*.xls")
While strFile <> ""
   DoCmd.TransferSpreadsheet acImport, 10, "invoices", strDir & strFile, True, ""
   strFile = Dir
WEnd
End Function

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[sap1958]

worked like a charm. One more question. Lets say we use this same function and the spreadsheets are set up as follows
row a1 has blank space
a2 Production Reporting
a3 ID NAME ProdID
a4 has a blank space
a5 122 John 123a
and so on.....

essentially I need to delete rows a1, a2 and a4 and want to include rows a3, a5 and so on. I believe I would need some time of delete statement in access. There are too many excel sheets to perform the suppression in excel

 

[Gammachaser]

You have to open the spreadsheet using Access and then delete the rows and save the sheet.

Sub DeleteRows()
Dim xlApp As New Excel.Application
Dim xlBook As Excel.Workbook
Dim xlSheet As Excel.Worksheet

Set xlBook = xlApp.Workbooks.Open([i]MySpreadsheet.xls[/i])
Set xlSheet = xlBook.Sheets(1)
xlSheet.Range("A4").EntireRow.Delete
xlSheet.Range("A2").EntireRow.Delete
xlBook.Close True
xlApp.Quit
Set xlSheet = Nothing
Set xlBook = Nothing
Set xlApp = Nothing
End Sub

Remember to delete higher number rows first because the rows shift up and take on new numbers on each delete. For example, once you delete row 2, what was row 3 is now row 2.