Append Query to insert data from excel

[ootkhopdi]

Hello

i want to create a append query in access 2003
to insert data into an existing table from Excel workbook

My Table name is TokenDetail in Access 2003
and Excel File is TokenCreation

i want to create query with msg box and requered file path for data becuase my excel files have various path and name

how can i create it

Thanks in Advance


Thanks in Advace

 

[pr2-eugin]

You might need a combination of FileDialog and DoCmd.TransferSpreadsheet methods to get you sorted.

 

[ootkhopdi]

Thanks
but i can't Understand solutions

Can u give me detail as step by step
and code...

 

[pr2-eugin]

Give me step by step information of what you want to do. The code is already given/provided !

 

[ootkhopdi]

Dear Paul

As your Query , I give my Prob in stepwise
1-I have an workbook name TokenCreation with Worksheet Token
2-I have an access 2003 database with Table named TokenDetail

now
i update data in Excel Worksheet TokenCreation...
and want to insert this data into Database table TokenDetail
but i cant get data in access database.

I hope now u can understand my problem

 

[pr2-eugin]

Try this,

Public Sub importData()
[COLOR=Green]'*****************************************
'Code Courtesy of
'  Paul Eugin
'
'Needed reference -
'   Microsoft Office **.* Object library
'*****************************************[/COLOR]
    Dim fileDump As FileDialog, recVar As Long
    Set fileDump = FileDialog(msoFileDialogOpen)
    
    If fileDump.Show Then
        Dim uploadXL As String
        uploadXL = fileDump.SelectedItems(1)
        DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, "Token", uploadXL, True
    End If
End Sub

Make sure you add the reference.

 

[ootkhopdi]

Dear Paul
how can i use this code
pls give me detail step wise process as i m very beginner in access

thanks in advance

 

[pr2-eugin]

Try your first VBA : http://www.baldyweb.com/FirstVBA.htm

 

[ootkhopdi]

Dear Paul

when i run your code it shows error
on line
fileDump As FileDialog

what is the error