import excel using button

[Clemens]

Hi,
Each week I perform an import from an excel sheet to my access table.

First I clear the table and then I'm performing the import manual.

Now I want to automate this step and following code I recieved:

DoCmd.RunSQL "DELETE * FROM Table"
DoCmd.TransferSpreadsheet acImport, , "Table", "c:\spreadsheet.xls".

This works for me, but the excel file location is fixed. How can this code
be change when pressing button I have to select the file I want to import?

This option is needed, because other users gives the file a different name
or location and for them it also needs to work

 

[gllincoln]

Hi Clemens,

If you are comfortable using VBA 0- depending on what version of Access you
are using - the FileDialog method might work for you.

Here is the related knowledgebase article

http://support.microsoft.com/kb/824272/en-us

If you have comdlg32.ocx installed on your computer (gets installed with
various products, the developer's edition of Access, Visual Studio, some
other stuff) then you may be able to set a reference to this file and use
that library.

Alternately, you can use the Windows API
http://support.microsoft.com/kb/303066/en-us

The Windows API is a bit more complex and cluttered to work with but it
probably is the best choice where you are distributing a database front end
to multiple users, who may have a variety of operating systems and anything
from Access 2000 to Access 2007 on their system.

Hope this helps,
Gordon

 

[Klatuu]

I would have to disagree. ActiveX controls come with a set of problems. An
API, IMHO, is much easier to work with. I would suggest this version. I
have been usign it for about 3 years and find it very stable:

http://www.mvps.org/access/api/api0001.htm