auto-generate excel files

S

shank

I have a report that separates some 50 companies. No problem!

Problem: I need to generate excel files for each company with this same
data. Is this possible? Each company would get their own file. Obviously, I
don't want to cut-n-paste. I would like to auto-generate filenames as well.
If possible, where do I start?

thanks!
 
V

Vincent Johns

shank said:
I have a report that separates some 50 companies. No problem!

Problem: I need to generate excel files for each company with this same
data. Is this possible? Each company would get their own file. Obviously, I
don't want to cut-n-paste. I would like to auto-generate filenames as well.
If possible, where do I start?

thanks!
Click to expand...

Sorry, I don't have a complete solution for you, but I think that this
(especially generating the file names) is beyond what you can easily do
in a Macro. It looks as if a Module is called for.

As long as you don't mind writing VBA code, yes, it's easy to do what
you want. But I would export the datasets generated by the Query
underlying your Report, not attempt to do anything with the Report itself.

-- Vincent Johns <[email protected]>
Please feel free to quote anything I say here.
 
S

shank

Vincent Johns said:
Sorry, I don't have a complete solution for you, but I think that this
(especially generating the file names) is beyond what you can easily do in
a Macro. It looks as if a Module is called for.

As long as you don't mind writing VBA code, yes, it's easy to do what you
want. But I would export the datasets generated by the Query underlying
your Report, not attempt to do anything with the Report itself.

-- Vincent Johns <[email protected]>
Please feel free to quote anything I say here.
Click to expand...
I can't write VBA, but if I had samples or such I may be able to limp
through it.
Has anyone done anything like this?
thanks!
 
V

Vincent Johns

shank said:
....


I can't write VBA, but if I had samples or such I may be able to limp
through it.
Has anyone done anything like this?
thanks!
Click to expand...


Here's another thought, though you'll have to enter the file names
yourself. I assume you have or can write a Parameter Query which, given
a company name, will display the data for that company.

You might be able to define a Macro to invoke that Query, then run Tools
--> Office Links --> Excel to copy the results to Excel and display them
there. You could then manually (or via an Excel macro) use File -->
Save as ... to create the file to send that company. For a one-time
effort, this might be easier than writing a Module, though not if you
have to do it every week or every day.

Another thought -- you could probably just as easily do this via a macro
in Excel, opening the Access Query in Excel and saving the results.
(Excel would translate your actions to VBA code, which you could then edit.)

Sorry, I don't have time to write the code right now, and some of the
details would depend on how you have your information organized.

-- Vincent Johns <[email protected]>
Please feel free to quote anything I say here.
 
S

shank

Vincent Johns said:
Here's another thought, though you'll have to enter the file names
yourself. I assume you have or can write a Parameter Query which, given a
company name, will display the data for that company.

You might be able to define a Macro to invoke that Query, then run
Tools --> Office Links --> Excel to copy the results to Excel and display
them there. You could then manually (or via an Excel macro) use File -->
Save as ... to create the file to send that company. For a one-time
effort, this might be easier than writing a Module, though not if you have
to do it every week or every day.

Another thought -- you could probably just as easily do this via a macro
in Excel, opening the Access Query in Excel and saving the results. (Excel
would translate your actions to VBA code, which you could then edit.)

Sorry, I don't have time to write the code right now, and some of the
details would depend on how you have your information organized.

-- Vincent Johns <[email protected]>
Click to expand...

Thanks! You gave me some great ideas for short cuts.
 
P

PC Datasheet

The Transferspreadsheet function will do what you want to do. Look it up in
the Help file.

If you have a company table, you are in luck because you can use the compnay
names as part of the file name in the TransferSpreadsheet function. If you
don't have the table, you are going to have to type them in. If this is a
reoccuring thing, you will only have to do it once though.

--
PC Datasheet
Your Resource For Help With Access, Excel And Word Applications
[email protected]
www.pcdatasheet.com

If you don't get the help you need in the newsgroup, I can help you for a
very reasonable fee. Over 1000 Access users have come to me for help.
 
Top