VBA Code to use the MS Access Import Wizard

[gblack]

Ugh! I've honestly looked for a very long time; perhaps I am just a horrible web investigator (likely).

I have come across many posts on how to use VBA to navigate to a spreadsheet file for import, but none that show how to programmatically access the Import Wizard, so I can choose the specific worksheet I want to import, from the workbook I just navigated to.

That said... does anyone have the code to navigate to an Excel file (like one would do manually by going to External Data>> Import Excel) and then run through the MS Access Import Wizard?

If so, I would be incredibly thankful!

Respectfully,
Gary

 

[Deleted member 147267]

maybe this?
https://social.msdn.microsoft.com/F...ing-import-spreadsheet-wizard?forum=accessdev


Kind of doubt you'll be able to actually manipulate the wizard gui itself but will have to settle for replicating its methods and properties.
Probably the link is what you were referring to. If so, sorry, trying to split my attention between here and the US border wall debate on tv.

 

[Pat Hartman]

You just need to use the TransferSpreadsheet method. It has all the arguments you need. Your code needs to supply the values for the various variables before executing the Import/Export. There is no need to ever manipulate the wizard.

 

[June7]

Navigate to a file using Windows File Dialog, capture filename selected in the File Dialog to a variable, use that with TransferSpreadsheet method. Lots of code examples out there http://learnexcelmacro.com/wp/2016/12/how-to-open-file-explorer-in-vba/

 

[theDBguy]

Hi. If you must use the wizard, try the following command.

DoCmd.RunCommand acCmdImport...

 

[gblack]

OK maybe I am not understanding the code that's out there and I apologize, in advance here, if some of you have already provided the answer and I am just not getting it.

What I've seen in my searches is:

I can use code to go out and manually choose a file, using the Application.FileDialog property (which is great... it's what I want and seems straightforward)...

But then after the user chooses an excel file...

I see code that seems to open the file to figure out the range of data...work worksheet name...etc... I don't want my code to do that, I'd rather the user run through the MS Access import wizard, to ensure the spreadsheet is imported correctly.

Will the "DoCmd.RunCommand acCmdImport" do that for me? And if so, how do I reference the specific workbook I have chosen to be applied to the wizard?

To explain what I want, more accurately, I've attached three screenshots.

What I'd like to do is get the VBA code that allows the user to click a button, on my form, and be able to do the actions in the attached screenshots.

Thanks to everyone for responding... it's very much appreciated!
-G

 

[gblack]

OK I feel really stupid... Now
TheDBguy gave me what I needed, but I didn't realize it.

I came across a post that uses:
DoCmd.RunCommand acCmdImportAttachText

Then realized there's a:
DoCmd.RunCommand acCmdImportAttachExcel

That's all I needed. Sorry to bother everyone!

ugh!

 

[theDBguy]

OK I feel really stupid... Now
TheDBguy gave me what I needed, but I didn't realize it.

I came across a post that uses:
DoCmd.RunCommand acCmdImportAttachText

Then realized there's a:
DoCmd.RunCommand acCmdImportAttachExcel

That's all I needed. Sorry to bother everyone!

ugh!

Hi. Glad to hear you got it sorted out. Good luck with your project.