How to chose a file to import from

[Ozymandias]

I'm a rather novice Access user and I have run into something I can't figure out. I am creating a database application that allows users to export the results of a query to an Excel file. (I use the 'OutputTo' action in a macro.) I leave the 'Output File' field blank and it allows the user to browse and select a file name to save the data in.

My difficulty is that I want to allow them to allow them to again browse to a xls file to import the data back from. The only way I found to import my data is with the 'Transfer Spreadsheet' action, but that requires a hard-coded file name to import from. Do you guys know of any way to allow the user to browse to the file when importing data?

 

[Pat Hartman]

TransferSpreadsheet is also a method so it is available in code. In VBA you can use the common dialog API or OCX to browse and select a file and then use the TransferSpreadsheet Method to import it.

 

[Ozymandias]

Thanks! Using 'TransferSpreadsheet' looks like it will do exactly what I want. I played with it a bit and can hardcode it to work the way I want. However, being a noob at VBA programming, I'm having difficulty getting a common dialog to work.

I usually remember things best when I work at it till it works, so I have been searching online for tutorials and help files. (Honest, I am trying to get as much as I can on my own) I put together the following code, but it still does not work. I gather that I am missing including some kind of control, but I don't know what I need or how to add it. Then again, I may be doing something wrong completely. Any help?

"ActiveX component can't create object" is the error it gives when I try to run this.

Set CDialog1 = CreateObject("MSComDlg.CommonDialog")

CDialog1.DialogTitle = "Export Data to File"
CDialog1.Filter = "Excel Spreadsheet (*.xls)|*.xls"
CDialog1.ShowSave
stExportFile = CDialog1.FileName​

 

[theprez]

This is probably a reference problem. The Common Dialogue active X control needs to be registered. Choose Tools, then references from the code window and make sure MS Common Dialogue is selected. To then regeister, run the regsvr32.exe command on comdlg32.ocx using a MS Command window.

 

[Ozymandias]

I added it and still the same error... hmmm...

 

[theprez]

Did you register the control? Also try moving the control up in the reference list. Go to the same place you added the control, select it an use the arrows to move it up in the list.

 

[Pat Hartman]

Unless you have VB or the developer's suite installed, your PC won't include the proper license to add the common dialog control. You will need to use the API which doesn't require a license. Search here for the code. You just copy it and paste it into a module. Then look at the example to see the few lines of code you need to write to work with the dialog.

Also, using the control itself will cause issues with distributing your application since you would need to register the activeX on every system that runs your app. Again, this isn't necessary with the API so I always use the API. Access 2003 includes its own version of the common dialog and I don't think that these issues exist with it. However, I haven't tried it since most of my clients are using older versions of Access and I didn't want to run into compatability issues.

 

[Ozymandias]

I got it to work using the API code.. thanks for the help!