Importing data

[Antony Elson]

I am trying to find a way of allowing people to click on a
button that will then open windows explorer and then they
could open the spreadsheet that they want to import and it
would then import into a specific table.

All of the formatting would be the same in both table and
spreadsheet.

Does anybody have any ideas?

 

[Cheryl Fischer]

There is code at the Access Web which will open the Windows file dialog,
allowing your users to select a file. Here is a link:

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

After that, you could use the Access TransferSpreadsheet method to import
the spreadsheet into an Access table.

 

[Antony Elson]

Thanks for that, I will have a good look at it and let you
know if it has worked.

 

[Antony Elson]

I forgot to mention that I use access 2000, does this
effect how the code works.

I have to admit that I am a novice at this VB script, so
any additional advice would be appreciated.

 

[Cheryl Fischer]

The code found at the Access Web for browsing and selecting files will work
in Access 97 through 2002, probably 2003 although I do not have that version
installed yet. Simply copy *all* of the code on that page, beginning with
the '*** Code Start *** line through the '*** Code End *** line into a
public module. Give special attention to the TestIt() function, as it
will show you how to get a selected filename and use it in your code as a
string.

 

[Antony Elson]

Many thanks,

When I attempt to run this code I get the following error
message "Compile error: Only comments may appear after End
Sub, End Function, or End Oroperty" in relation to the
following string "Declare Function aht_apiGetOpenFileName
Lib "comdlg32.dll" _
Alias "GetOpenFileNameA" (OFN As tagOPENFILENAME) As
Boolean

Declare Function aht_apiGetSaveFileName Lib "comdlg32.dll"
_
Alias "GetSaveFileNameA" (OFN As tagOPENFILENAME) As
Boolean
Declare Function CommDlgExtendedError Lib "comdlg32.dll"
() As Long"

I must be doing something wrong, help!

 

[Cheryl Fischer]

I have seen that error when there is another sub or function preceding the
Type Declarations and Declares, which must be at the very top of the module.

I'd suggest that you create a new, empty module, naming it modFileHandling.
Copy all of the code from that link to this module, compile and save it and
you should be "good to go".

 

[Antony Elson]

Thanks once again for you help.

I think i have finally sorted it out.

When I attempted to run the code access wanted me to save
the module first, in doing this it I kept insertinf the
code inbetween the new line, when all I needed to do was
insert the new lines at the bottom. Sorted many thanks.

 

[Antony Elson]

One last Question, this procedure seem to now work, but
how do I use the TransferSpreadsheet function to import
the selected file to a specific table. As I understand it
the TransferSpreadsheet function requires a file path and
name.

 

[Cheryl Fischer]

The example found at the top of the article should return the filename and
fullpath to the variable: strInputFileName:

Dim strFilter As String
Dim strInputFileName as string

strFilter = ahtAddFilterItem(strFilter, "Excel Files (*.XLS)", "*.XLS")
strInputFileName = ahtCommonFileOpenSave( _
Filter:=strFilter, OpenFile:=True, _
DialogTitle:="Please select an input file...", _
Flags:=ahtOFN_HIDEREADONLY)

DoCmd.TransferSpreadsheet(<TransferType>, <SpreadsheetType>, <TableName>,
strInputFileName, <HasFieldNames>)