What's a good way to handle this?

A

Abbey Normal

Hi all -
I have some users who need to create labels from the different spreadsheets
they create during the year. Our label software works nicely with Access.
I've been able to import the spreadsheet, but now I need to massage the data
a bit, I'm figuring I should write a module. but how can i get this to
execute automatically? do i have to create a switchboard for them or can they
just run the module somehow? Thanks for your input,
 
K

Kara

I recently had to do this for my own labels, and found a query worked pretty
easily. It probably depends on how you need to massage the data. I just
needed to concatenate fields, change the cases, switch abbreviations to full
names, and so forth. Hope this helps.
 
A

Abbey Normal

Well, even if I could use a query for my purposes, how can I get it to
execute automatically? Also, I need to parse a couple of fields out,
concantenate the results and then put the results in a result field only if
that result field is blank. Can query do that? Thanks,
 
K

Ken Snell [MVP]

Various options exist within ACCESS. Queries can concatenate and parse data
(using calculated fields), functions and subroutines in modules can do this
too, and so on.

As for automatically running, depends upon what that means... the user
clicks a button to run the label-making program? The user selects a choice
from a menu item on the toolbar or menu bar? The program runs when you open
ACCESS database file? The program runs itself at night when no one is
around?

A number of details are needed so that we might be able to assist you.
 
A

Abbey Normal

Well, they have to create these labels on demand a few times a year. I
created a switchboard, and then there is an option for each type of label
they want to create. could i create a module/function/whatever that would
execute the keystrokes File>Get External Data> Import... and then when they
have completed their importing it would execute the module/query that would
parse the data.. (then the labels would be ready for printing). Am I on the
right track? Thanks,
 
K

Ken Snell [MVP]

Right track? Yes.

1) Importing can be done via macro or VBA code. If it's a text file that you
want to import, use TransferText action (see Help for info on how to use
this). You can set up a specification that defines the format of the file so
that ACCESS will know how to import the data. The data can be imported to a
new table or to an existing table; I usually let ACCESS import to a
temporary table and then use an append query to copy the data to a permanent
table so that I can do the parsing that you mention you need to do. But
exact steps are really up to you, the designer of the database. If you're
importing an EXCEL file, then use TransferSpreadsheet action.


2) For parsing, I would either do as I mentioned above (use an append query
to copy the data to a permanent table, parsing the data as needed as part of
the append query), or I would just use the query that will generate the
records for the labels to do the parsing. Either way, a query should be used
for the parsing. You can use functions in a module to do more complicated
parsing that may be a bit difficult or confusing to do in a query; public
functions can be called from within queries.


So yes, you could use a button on a switchboard to do the import (and
parsing if you want to do parsing during an append query that copies the
imported data to a permanent table), and then a button to generate labels.

--

Ken Snell
<MS ACCESS MVP>
 
A

Abbey Normal

Wow! that TransferSpreadsheet macro works beautifully. I don't suppose there
is any way on that to cause a dialog box so they can find the spreadsheet
they need. Should I be looking VBA instead? I see the FileDialog method, use
that to get the file name and then use the TransferSpreadsheet method? Thanks
 
D

Douglas J. Steele

In my opinion you should always use VBA rather than macros. VBA gives you
the ability to gracefully handle error conditions, which macros cannot do.

And rather than use the FileDialog method, I'd recommend using the API calls
that Ken Getz documents in http://www.mvps.org/access/api/api0001.htm at
"The Access Web". There can be problems with the FileDialog if you move your
application from one machine to another. Simply copy all the code into a new
module in your application: it really doesn't matter if you understand how
it works, just that you can use it!

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)
 
A

Abbey Normal

Okay, I like that even better. I was able to modify it so that it defaults
to .xls for them, but what do I use to retrieve the actual path/file name and
use that - which i am assuming i should use in the TransferSpreadsheet
method? Believe me, I'm very grateful for this help.
 
D

Douglas J. Steele

Look at the example at the very top of the page:

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)

There, the actual path/file name will be strInputFileName


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)
 
A

Abbey Normal

Thank you - sometimes I overlook the obvious!

Douglas J. Steele said:
Look at the example at the very top of the page:

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)

There, the actual path/file name will be strInputFileName
Click to expand...
 
Top