Open Import Excel from Macro

[shepa006]

This is similar to other posts but I couldn't find one that answered exactly what I'm trying to do.

Ultimately I need to let the user import an Excel worksheet, without being an Admin on their machine, and capture the name of the imported worksheet and the name of the table they created/imported to. My thoughts were
1) From a macro, or VBA, open the built in Excel import wizard (External Data > Excel [Import & Link Group]). From what I can tell opening this from a macro should be possible. However, when I create a new macro and go to the Action Catalog > Actions > Data Import/Export there isn't an option in the catalog for importing anything from Excel. So I tried going through VBA, I don't see anything about being able to launch the Excel Import wizard there either. Since the Excel file AND worksheet will be changing and need to be specified by the user I haven't found anything in VBA that gets me to that level. I did find examples on using the import worksheet command but that assumes that you already know the worksheet name (the excel file path can be figured out with a file dialog).

Any ideas? I don't know why the Action Catalog doesn't have a reference for opening the import excel wizard.

2) Assuming we can get Part 1, above, working how could I capture the worksheet name or at the very least the name of the table?

Thanks.

[ranman256]

You don't want it import to whatever the sheet name is.
you want to import to the same table over and over. Add importDate field too.

save the workbook to the same folder, with the same workbook name...say ImportBook.xlsx
then run the macro that has
importspreadsheet, //path/ImportBook.xlsx.

this is the easiest method.... Save XL file, click macro button.

[shepa006]

I agree that would be ideal and how I would normally handle it. However for this situation I need to import their original table for archiving and then I have some light weight ETL functions written for validating the data from their 'archived' table and standardizing values before loading it into the final table. So I can't directly import to an existing table nor can I assume that the incoming table will be formatted properly or consistently named (file or worksheet).

[ItsMe]

So the biggest hurdle is getting the Worksheet name? I suppose there would be a .Name property for the worksheet that would be accessible via VBA. Maybe a For Each MyWorksheet in MyWorkbook...