excel into access, code placement

  • Thread starter Thread starter drabbacs
  • Start date Start date
D

drabbacs

I've seen the posts regarding the use of code to use the
open file dialogue box but I'm not sure how to put the
pieces together. I want the user to be able to click on a
command button, then display the open file dialogue, and
then import the excel file they specify into the database.

I'm assuming that I am supposed to tie an event procedure
to the on-click property of the command button. The event
procedure would then be code from
http://www.mvps.org/access/api/api0001.htm but as I've
said I'm not sure how to put it together. Is the short
piece of code from the top put under the form object and
the longer code enetered as a module or some other
arrangement. Also where does the file information get
retained?

Thanks for the help in advance
Drabbacs
 
A clarification:

I currently have the following event procedure tied to the
command button

Private Sub Command0_Click()
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)
End Sub


and a module with the longer code section from the website.
When I click I get the open file dialog box. Now how do I
get the file name the user specified for use in a
subsequent transferspreadsheet command?
 
The filename is in the variable strInputFileName (bit of a clue
there<g>). Just pass it to TransferSpreadsheet by inserting a line of
code like this before the End Sub:

docmd.TransferSpreadsheet TransferType:=acImport, _
TableName:= "MyTable", FileName:= strInputFileName
 
Awesome! Thanks so much for the help.

Now, is there a way to specify that row 1 contains field
names?

Drabbacs
-----Original Message-----
The filename is in the variable strInputFileName (bit of a clue
there<g>). Just pass it to TransferSpreadsheet by inserting a line of
code like this before the End Sub:

docmd.TransferSpreadsheet TransferType:=acImport, _
TableName:= "MyTable", FileName:= strInputFileName

A clarification:

I currently have the following event procedure tied to the
command button

Private Sub Command0_Click()
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)
End Sub


and a module with the longer code section from the website.
When I click I get the open file dialog box. Now how do I
get the file name the user specified for use in a
subsequent transferspreadsheet command?
Click to expand...

--
John Nurick [Microsoft Access MVP]

Please respond in the newgroup and not by email.
.
Click to expand...
 
The complete syntax for the TransferSpreadsheet method is:

DoCmd.TransferSpreadsheet [transfertype][, spreadsheettype], tablename,
filename[, hasfieldnames][, range]

To use John's syntax, you'd use

docmd.TransferSpreadsheet TransferType:=acImport, _
TableName:= "MyTable", FileName:= strInputFileName, _
HasFieldNames:=True


--
Doug Steele, Microsoft Access MVP

(No private e-mails, please)



drabbacs said:
Awesome! Thanks so much for the help.

Now, is there a way to specify that row 1 contains field
names?

Drabbacs
-----Original Message-----
The filename is in the variable strInputFileName (bit of a clue
there<g>). Just pass it to TransferSpreadsheet by inserting a line of
code like this before the End Sub:

docmd.TransferSpreadsheet TransferType:=acImport, _
TableName:= "MyTable", FileName:= strInputFileName

A clarification:

I currently have the following event procedure tied to the
command button

Private Sub Command0_Click()
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)
End Sub


and a module with the longer code section from the website.
When I click I get the open file dialog box. Now how do I
get the file name the user specified for use in a
subsequent transferspreadsheet command?

-----Original Message-----
I've seen the posts regarding the use of code to use the
open file dialogue box but I'm not sure how to put the
pieces together. I want the user to be able to click on a
command button, then display the open file dialogue, and
then import the excel file they specify into the
database.

I'm assuming that I am supposed to tie an event procedure
to the on-click property of the command button. The event
procedure would then be code from
http://www.mvps.org/access/api/api0001.htm but as I've
said I'm not sure how to put it together. Is the short
piece of code from the top put under the form object and
the longer code enetered as a module or some other
arrangement. Also where does the file information get
retained?

Thanks for the help in advance
Drabbacs
.
Click to expand...

--
John Nurick [Microsoft Access MVP]

Please respond in the newgroup and not by email.
.
Click to expand...
Click to expand...
 
Back
Top