Help - Import Excel Sheet

[modest]

I'd like to use VBA to import an excel file to an Access table.

So far I have a form with a button, the button opens up a window to get the file. I assume I use the transferspreadsheet method, but I'm running into problems:

strFilter = ahtAddFilterItem(strFilter, "Microsoft Excel File (*.xls)", "*.xls")
strFile = ahtCommonFileOpenSave(InitialDir:=CurrentDb.Name,Filter:=strFilter, OpenFile:=True, DialogTitle:="Select location of Inventory data file to import")

DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, strSheetName, strFile



This is how TransferSpreadsheet is defined:
Sub TransferSpreadsheet([TransferType As AcDataTransferType = acImport], [SpreadsheetType As AcSpreadSheetType = acSpreadsheetTypeExcel97], [TableName], [FileName], [HasFieldNames], [Range], [UseOA])

so... where it says "TableName", is that the worksheet name I'm importing?... or, is that the Access table name I want to import to? Secondly, what is the "HasFieldNames"? And Third, the beginning of the file I wish to import has 3 blank rows (which I hear is not good for importing). How do I delete these rows from Access.

Characteristics of the excel file:
It has numerous columns that stay fixed, but the amount of records (rows) will vary from week to week.



any help is appreciated

modest

 

[The_Doc_Man]

the beginning of the file I wish to import has 3 blank rows (which I hear is not good for importing). How do I delete these rows from Acce

Import the sheet to an intermediate table with no primary key. Then APPEND the data using a criterion that you won't append any record from the intermediate unless certain fields in it are NOT blank / empty / null / whatever they show up as. Alternatively, before you do the append, go through the temporary table with an Erase query that removes rows you do not desire to be present in the final destination. Sort of an intermediate filtering process, I guess you could call it.

Secondly, what is the "HasFieldNames"?

If you are creating a table, the first ROW of the sheet either does or does not contain headers you can use for field names. Yes/No parameter. Has no meaning if you are not creating a new table.

where it says "TableName", is that the worksheet name I'm importing?... or, is that the Access table name I want to import to?

Access table name.

It has numerous columns that stay fixed, but the amount of records (rows) will vary from week to week.

Using the intermediate table method, you can erase the rows of the temp table. Then import the sheet. Then decide what rows to append in your real table. (OR replace... if that is appropriate in some cases.) Once you have pared down the intermediate to things you want to keep, the number of things doesn't matter. Only the fact that you now have what you want to keep.

 

[shivalee]

DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, strSheetName, strFile


here strsheetname is the name of the access table in which u shall import your excel file.

strFile is the excel file you r wishing to import, u can give the whole path.
i am attaching my code for your help:

Private Sub Import_Spreadsheet(tblname As String)
Dim fldr, fl
Dim extfile As String
Dim lstmodif As Date, lstimp As Date
Dim ssfolder As String

ssfolder = "D:\Documents and Settings\shivaleegupta\Desktop\Shivalee\barun project\database\"
extfile = ssfolder & tblname & ".xls"

Set fldr = CreateObject("Scripting.FileSystemObject")
Set fl = fldr.GetFile(extfile)

Debug.Print fl.DateLastModified
Debug.Print DLookup("[last_imported]", "Timer_last_import", _
"[table_Name]='" & tblname & "'")

'lstmodif = format(fl.DateLastModified, "yyyy/mm/dd")
'lstimp = format(DLookup("[last_imported]", "Timer_last_import", _
' "[table_Name]='" & tblname & "'"), "yyyy/mm/dd")
'use the command given below. it uses time as well
'this will ensure importing many times in the same day

lstmodif = format(fl.DateLastModified, "yyyy/mm/dd hh:nn:ss")
lstimp = format(DLookup("[last_imported]", "Timer_last_import", _
"[table_Name]='" & tblname & "'"), "yyyy/mm/dd hh:nn:ss")


'Set fldr = CreateObject("Scripting.FileSystemObject")
'Set fl = fldr.GetFile(extfile)
'lstmodif = format(fl.DateLastModified, "mm/dd/yyyy")
'lstimp = format(DLookup("[last_imported]", "Timer_last_import", _
' "[table_Name]='" & tblname & "'"), "mm/dd/yyyy")
'as date values have come in as text, we cant use this structure
'have used the one above to get correct dates etc.

If lstmodif > lstimp Then
'clear table
strSQL = "DELETE * FROM " & tblname
CurrentDb.Execute strSQL
'import from spreadhseet
DoCmd.TransferSpreadsheet acImport, 8, tblname, extfile, True
'update import file tinmestamp in Timer_last_import
strSQL = "UPDATE timer_Last_Import SET Last_Imported = #"
strSQL = strSQL & lstmodif & "# WHERE Table_Name = '"
strSQL = strSQL & tblname & "'"
CurrentDb.Execute strSQL
Else
MsgBox "No new data found for table " & tblname, vbExclamation, "Import aborted"
End If
End Sub

 

[modest]

both of those are logical and they work, but it would be much easier if i could just delete the first 2 rows of an excel sheet. the scope of my project has changed and i've been informed that the user will be able to just import the x-cel sheet, but i'd like to pre-format it using (I guess) DAO.


So if anyone knows how to delete the first two rows of an excel sheet from VBA in Access, post away