Automating Spreadsheet Import

  • Thread starter Thread starter Eric H
  • Start date Start date
E

Eric H

I am trying to automate the import of a spreadsheet into a database I am
creating. I am brand new to VBA, and am trying very hard to learn enough to
acomplish this task. I have found how to use the transferspreadsheet method
to automate the import, however, the file name and location of the
spreadsheets that I need to import may change from week to week. I found one
of the responces giving a link to some lengthy code that will allow you to
use and API call to open the open file dialog box, but I dont know enough to
be able to figure out how to use the information. How to I script something
into the transferspreadsheet to do this?

I dont currently have any code to post, as I'm still working on composing
it. I'm trying to get it mapped out in my head and on paper so I understand
what I'm doing before i start typing it in.

Any help you guys can give would be much appreciated. Even if its only to
refer me to a good book to use to learn VBA relatively quickly.

Thanks
Eric
 
I am trying to automate the import of a spreadsheet into a database I am
creating. I am brand new to VBA, and am trying very hard to learn enough to
acomplish this task. I have found how to use the transferspreadsheet method
to automate the import, however, the file name and location of the
spreadsheets that I need to import may change from week to week. I found one
of the responces giving a link to some lengthy code that will allow you to
use and API call to open the open file dialog box, but I dont know enough to
be able to figure out how to use the information. How to I script something
into the transferspreadsheet to do this?

I dont currently have any code to post, as I'm still working on composing
it. I'm trying to get it mapped out in my head and on paper so I understand
what I'm doing before i start typing it in.

Any help you guys can give would be much appreciated. Even if its only to
refer me to a good book to use to learn VBA relatively quickly.

Thanks
Eric
Click to expand...

Hi Eric,

If this is the Ken Getz API code from the Access MVP site, I use it
all the time. This morning I also happen to be working with the
transferSpreadsheet method as well. Below, I'll show you an example
and try to explain how I go about things.

Ok, first copy Ken's code and put it in a module. I call my module
mCommonDialog.

I start things off with a simple little form with a button on it. In
the button's On Click event is where I put the code to call the Common
Dialog API. My routine is much more complicated than that so this
code is made up of pieces that I've cut and pasted in here. I tried
to watch for line wrap but, I might have missed some.

<code>
Private Sub cmdGo_Click()
On Error GoTo ErrHandler

Dim sFilter As String
Dim sFileName As String
Dim sTableName As String

sFilter = ahtAddFilterItem(sFilter, "Excel Files (*.XLS)", "*.XLS")
sFileName = ahtCommonFileOpenSave( _
InitialDir:="C:\", _
Filter:=strFilter, OpenFile:=True, _
DialogTitle:="Please select the file...", _
Flags:=ahtOFN_HIDEREADONLY)

' You'll see that I set the Initial Directory to the C:\ drive.
' You will probably want to set it to a directory closer
' to where you expect to find these files.

' Here is where you set the name of the table you want to
' import the spreadsheet into. You could use an input box

sTableName = "tblWhatEver"

' Then I import it
DoCmd.TransferSpreadsheet acImport, , sTableName, sFileName, True

ExitPoint:
Exit Sub

ErrHandler:
' Handle your errors
Resume ExitPoint
End Sub
</code>

Hope this helps,
RD
 
Thanks RD. I have tried inserting this code and changing the table name, and
I am still not getting anything. Odds are, I missed something. When you put
the code to call the Common Dialog API, to do this, do I paste in the
complete code that we put into the module, or is there a way to reference
this? All I put into the button code was what you had posted.

Thanks for your help on this.

Eric
 
Thanks RD. I have tried inserting this code and changing the table name, and
I am still not getting anything. Odds are, I missed something. When you put
the code to call the Common Dialog API, to do this, do I paste in the
complete code that we put into the module, or is there a way to reference
this? All I put into the button code was what you had posted.

Thanks for your help on this.

Eric
Click to expand...

Well, one thing I noticed ... I messed up. Should be sFilter, not
strFilter. My bad.
See mod below:

sFileName = ahtCommonFileOpenSave( _
InitialDir:="C:\", _
Filter:=sFilter, OpenFile:=True, _
DialogTitle:="Please select the file...", _
Flags:=ahtOFN_HIDEREADONLY)

If everything else was done correctly, this should run just fine.

RD
 
Once I changed the one piece of code you showed, and realized that the button
was named Command0 instead of cmdGo and changed that, it worked perfect.

Thanks for all of your help!

Eric
 
You're quite welcome. Glad to be of help.

RD


Once I changed the one piece of code you showed, and realized that the button
was named Command0 instead of cmdGo and changed that, it worked perfect.

Thanks for all of your help!

Eric
Click to expand...
 
Back
Top