PDA

View Full Version : transferring a spreadsheet but specifically which sheet



RuneDefour
04-06-2023, 04:15 AM
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,......

June7
04-06-2023, 10:51 AM
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel12, tablename, filename, True, "stock$A10:F100"

RuneDefour
04-07-2023, 06:16 AM
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel12, tablename, filename, True, "stock$A10:F100"


how should i change the code so that when a file is imported for the 2nd time. that the previous table is updated with the new values?

June7
04-07-2023, 10:47 AM
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.

RuneDefour
04-11-2023, 02:33 AM
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

Aussiebear
04-11-2023, 05:00 AM
RuneDefour, please wrap your submitted code with tags.

June7
04-11-2023, 11:44 AM
I don't delete table, I delete records.

CurrentDb.Execute "DELETE FROM " & tablename