how should I change it so that it takes the table from sheet "stock" with a range from A10 to F100?
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel12, tableName, fileName, True,......
how should I change it so that it takes the table from sheet "stock" with a range from A10 to F100?
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel12, tableName, fileName, True,......
Last edited by Aussiebear; 04-11-2023 at 01:16 PM. Reason: Added the code tags
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel12, tablename, filename, True, "stock$A10:F100"
Last edited by Aussiebear; 04-11-2023 at 05:19 AM. Reason: Added code tags
How to attach file: Reading and Posting Messages (vbaexpress.com), click Go Advanced below post edit window. To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.
Records will be appended to existing table. TransferSpreadsheet does not edit existing data.
If Excel data is cumulative, then perhaps you need to delete existing records from table before import.
How to attach file: Reading and Posting Messages (vbaexpress.com), click Go Advanced below post edit window. To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.
sounds logical to me. I've tried this but it won't work it doesn't want to delete the previous table.
Option Compare Database Option Explicit Public Sub ImportExcelSpreadsheet(fileName As String, tableName As String) ' Delete the table if it already exists If DCount("*", "MSysObjects", "Name='" & tableName & "'") > 0 Then DoCmd.DeleteObject acTable, tableName End If ' Import the data from the Excel file DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel12, tableName, fileName, True, "stock$A10:F100" Exit Sub End Sub
Last edited by Aussiebear; 04-11-2023 at 04:59 AM. Reason: Added code tags to supplied code
RuneDefour, please wrap your submitted code with tags.
Remember To Do the Following....
Use [Code].... [/Code] tags when posting code to the thread.
Mark your thread as Solved if satisfied by using the Thread Tools options.
If posting the same issue to another forum please show the link
I don't delete table, I delete records.
CurrentDb.Execute "DELETE FROM " & tablename
How to attach file: Reading and Posting Messages (vbaexpress.com), click Go Advanced below post edit window. To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.