Transferspreadsheet/Import Question

[Badswell]

For a project review, an Excel template is sent to multiple people. Each person fills out this file and returns it. Within the Excel file, there are multiple worksheet of information that are imported into Access. These worksheets are setup to match the formats and field types of the Access tables they would be imported into. For each Excel file that is filled out, there is a possibility of 10 seperate worksheets that would be imported. There is no defined location for these files to be stored because these project reviews are not done regularly. Rather than having users fight with the import wizard (trying to dummy proof this as much as possible), I am looking to create a button that does the following:

1. Prompt the user to browse in order to select a specific file (ex: C:\2004 Reviews\Solo.xls)
2. Based on the selected file, import the worksheet "Access_Supplier_Info" into the table "PS_Supplier_Info"

If I can get coding to get one of these imports to work, it should be easy enough to modify it from importing "Worksheet A into Table A" to importing "Worksheet B into Table B." I'm just lost on the user browsing for a file, auto-specifying a worksheet to be imported & even getting the TransferSpreadsheet action or method to work.

 

[Calvin]

You can use the "Common Dialog" API to browse for the target file, search this site for examples of it.

You may have problems with specifying which sheet to import using TransferSpreadsheet unless the Range is defined ahead of time in the xls file because I beleive the TranferSpreadsheet method only works with the first sheet in the file or predefined ranges.

Another option maybe to add/reference the "Excel x.0 Object Library" to your project and write a custom function that connects to the file in question and through code you can set the "ActiveSheet" to the sheet containing the data specific to that step and loop through reading and importing a given range of rows and columns.

Get your Common Dialog working and then come back for more help on importing there are lots of options.

 

[Badswell]

Okay I got the common dialog working. That was pretty painless. For the time being, the path of the selected file is being brought into a text box named "Text1." Still not sure on how to bring this path that is selected into the import feature.

Using name ranges for the sheets would work on 5 of the 10 imports that would need to be done. These 5 would work because it is 1 record per file, so the named range could be static. However, the only 5 imports vary in the number of records depending on who fills out the Excel form. I see how transferspreadsheet by using the macro tool and a named range, but since the import wizard allows for selecting a worksheet in the excel file, I would imagine that VB coding would allow for this too somehow?


**edit
I have coding that works to import the 5 statically named ranges. Rather than having Access come up with its default error messages for bad imports (data type/deletion or key violations errors), I'd like to be able to test for this. Once "No" is hit for the do not continue option, another window comes up with Run-Time Error 2501 - "The TransferSpreadsheet action was canceled." Is there a way to test for run time errors? If so, I believe if the warnings were turned to false, a check could be done for this error to display a msgbox rather than all the default prompts and questions that pop up.