Import / Append Using VBA

[MikeDuffield]

Hi all,

I've figured out how to append data using VBA, it's this simple bit of code:

DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel12, "NewMasterData", "Shortage List.xlsx", True

However, I want to do two things:

1) Prompt the user to select which file to import.
2) Not append any values that are already in the table.

Can anyone help please?


Thanks in advance,
Mike.

 

[Trevor G]

This may work in part to get data into your access database allowing users to select the file.

There is no error trapping so you may need to consider if they click cancel etc.

Sub Dial1()
Dim strFile As String
strFile = Application.FileDialog(msoFileDialogFilePicker).Show
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel12, "NewMasterData", strFile, True
End Sub

Read this it may help. As for duplicates in the query wizard there is a duplicates query which may help you.

http://www.wiseowl.co.uk/blog/s209/type-filedialog.htm

 

[MikeDuffield]

Thanks Trevor,

I'm getting an error though:

"Method 'FileDialog' of object '_Application' failed"

Should I have enabled a reference or something to allow this to work?


Thanks for your time!

 

[pr2-eugin]

Try creating a file dialog object first and then go on from there..

Dim fileDump As FileDialog
Set fileDump = Application.FileDialog(msoFileDialogOpen)
If fileDump.Show Then
    DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel12, "NewMasterData", fileDump.SelectedItems(1), True
End If

Also make sure that you add Microsoft Object Library 14 in the reference.

 

[MikeDuffield]

Thanks guys!

I had to enable "Microsoft Office Objects 12.0" in references, I assume because it's a slightly older version.

Seems to work perfectly, thanks again to you both.