transfer spreadsheet/import command

  • Thread starter Thread starter H0MELY
  • Start date Start date
H

H0MELY

Greetings all and thank you for looking at my post. Two part question.
First question is that I want to import a .csv file into a pre-existing table
in access. The fields are all setup as text, I do not want access to format
any of the data, just shove it into the cells. Here is my issue...

I would like the user to have the ability to locate the file, but that is
it. I want the type of file being selected and the table that the file is
being imported into to be hard coded. If they select a file that is not
formatted correctly I want an error to pop up that says bad file and allows
them to pick a new file.

The file will be a coma seperated text file with 8 columns.

At the end of processing the imported data I want to export the file. I
want it exported as an excel spreadsheet with a pre-chosen name...but again I
want them to be able to decide where to save it.

Any words of wisdom would be greatly appreciated. My last question would be
suggestions for the best Access VBA book for a beginner-intermediate user.
What has happened is that a lot of the tools I built for myself are now going
to be distributed, which means I have to clean them up a little.
Unfortunately I can see no other way to accomplish what I want without VBA.
Baptism by fire you may say. Thank you again for looking =)

-John
 
Hi John,

Here's a link to the code on the MVPS.org site which I've used to do the
same thing. Worth playing with.

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

I know for my app that I'm only going to be importing excel spreadsheets so
I limit the File type to *.XLS and then call to the function direct from the
DoCmd.TransferSpreadsheet in place of the filename. My
DoCmd.TransferSpreadsheet looks like this:

Dim stTableName As String
stTableName = tblMain
DoCmd.TransferSpreadsheet acImport, , stTableName, OpenTextFile, True

The system then looks to the code (written by Ken Getz as detailed on the
MVPS site--see above), opens the dialog to find the file as I've asked it to
do, let's the user browse to wherever they'd like to browse, select the right
file (limited by the file type which I specify in the code as being limited
to *.xls), they click open and it neatly drops the file into the right spot
in the TransferSpreadsheet.

I, unfortunately, don't have much experience with exporting and so I've
never tried to use this for saving... only opening. But maybe you'll be able
to play with this to get to the other end of your process as well. I also
haven't searched the MVPS site for similar code for saving... but it might be
there... worth taking a peak. Hope that helps! Let me know if any of that
needs more of an explanation.
 
Back
Top