VBA code for import of Excel spredsheet

[Guest]

Hi
I am trying to make a bit of VBA code that will import a Excel spredheet,
the user
want a button that promt her for the spredsheet that she want to import. And
after that imports the spredsheet in a table that are allready made i Acces.

Can anyone help ??

Kind regards

 

[Jeff L]

Have you looked at TransferSpreadsheet in Help?

 

[Guest]

Here is a link to the API that can be used to present the standard File
Open/Save dialog that you can use to navigate to the file to be imported:

http://www.mvps.org/access/api/index.html

Here is an example from one of my apps that shows how it can be used:

Dim varGetFileName As Variant

'See if data for the month and year already exits
On Error GoTo LoadNonVAII_Error

'Get the file to import
Do While True
varGetFileName = ahtCommonFileOpenSave(ahtOFN_OVERWRITEPROMPT, , _
"Excel Spreadsheets (*.xls) *.xls", , _
"xls", , "Load Non Vought Invoice Data", , True)
If varGetFileName = "" Then
If MsgBox("Cancel Import?", vbQuestion + vbYesNo, "Load Non
Vought Data") _
= vbYes Then
Exit Function
End If
Else
Exit Do
End If
Loop

'Delete the old data
CurrentDb.Execute ("DELETE * FROM NonVAII"), dbFailOnError
'Import the New Data
DoCmd.TransferSpreadsheet acImport, 8, "NonVAII", _
varGetFileName, True

MsgBox "Import Complete", vbOKOnly, "Load Non Vought Data"