Import from excel to access via access form 2

[reta]

I am currently building a access db in access. A form will pop up prompting the user to enter in the file name of the excel spreadsheet and once they press a button all of the information from the spreadsheet will update and added to the existing information in that table. The problem is i dont know how to import a particular file in excel format to access.
I hope this makes sense......
Reta

 

[PHV]

Take a look at the DoCmd.Transfer Spreadsheet method.
You may also consider link an access table to the spreadsheet.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[reta]

i have looked at that method but i need to allow the user to chose a file, which then allows my db to get that information and put it in the db.
Thanks
Raeda

 

[reta]

this is the code that i used:
Sub test_click()

Dim xlobj
Dim wsobj
Dim rngobj
Dim LastRow As Long
Set xlobj = GetObject("C:\Documents and Settings\raedad\Desktop\test2.xls")
Set wsobj = xlobj.Worksheets("Sheet1")
LastRow = wsobj.Range("A65536").End(xlUp).Row

Set rngobj = wsobj.Range("A1:AY" & LastRow)

rngobj.Name = "MyRange"
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel97, "InvoiceData", "C:\Documents and Settings\raedad\Desktop\test2.xls", False, "MyRange"
'docmd.TransferSpreadsheet acImport ,
Set xlobj = Nothing
End Sub

but i am getting an error
Field 'F1' doesn't exist in destination table 'InvoiceData'
I have not idea how to fix this...
can anyone help
Reta

 

[firefry]

Try this:
Dim fso 'File System Object
Dim strTargetFile, strRootDir, strEntirePath
'setup file Path and name
strRootDir = "C:\Documents and Settings\raedad\Desktop\"

strTargetFile = txtFileToOpen.Value & ".xls" 'If user enters file to open in txtbox
strEntirePath = strRootDir & strTargetFile

'Not needed unless you want to import a template
'-----------------------
Set fso = CreateObject("Scripting.FileSystemObject")
If Not (fso.FileExists(strEntirePath)) Then
Dummy = fso.CopyFile(strRootDir & "Template\BlankTemplate.xls", strEntirePath, vbTrue)
End If
'----------------------

DoCmd.TransferSpreadsheet acImport, , "InvoiceData", strEntirePath, True

 

[reta]

Hi firefry,
I used your code but im still getting an error.
Field 'F1' doesn't exist in destination table 'InvoiceData'.
hope you can help
Reta

 

[PHV]

Do the transfer in a new (temporary) table.
As your spreadsheet seems not have a header row, the fields are named F1, F2, ...

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[reta]

Thanks for your help. I deleted the table and tested the code again and it created a new table automatically.
Thanks for your help
Reta