transferring a spreadsheet but specifically which sheet

[RuneDefour]

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]

DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel12, tablename, filename, True, "stock$A10:F100"

[RuneDefour]

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]

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]

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]

RuneDefour, please wrap your submitted code with tags.

[June7]

I don't delete table, I delete records.

CurrentDb.Execute "DELETE FROM " & tablename