auto-generate excel files

[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!

 

[Vincent Johns]

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!

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.

 

[shank]

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.

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!

 

[Vincent Johns]

....


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!

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.

 

[shank]

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]>

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

 

[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
(e-mail address removed)
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.

 

[StopThisAdvertising]

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
(e-mail address removed)
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.

These 1000 (if at all a real figure..) is only the result of
-- 4 years abusing the newsgroups.
-- 4 years blatantly advertising and jobhunting.

You just don't care about the newsgroups. You have no ethics at all.
You only care about making money, and you act as if the groups are your private hunting ground.

-- You abuse this group and others for job-hunting and advertising over and over again
-- You are insulting lots of people here when they ask you to stop this
-- You posted as Steve, Ron, Tom, Rachel, Kathy, Kristine, Heather and ??? while asking questions
(the latest 'star's': 'Access Resource' and Tom (e-mail address removed))
-- You try to sell a CD ($125,--) with FREE code you gathered from these groups here
-- There even has been a 'Scam-alert' about you which has been explained recently in the thread 'To all':
http://groups.google.com/group/comp.databases.ms-access/msg/46038ba2954261f9?hl=en
-- Also recently it became clear that you have been spamming innocent people asking questions:
http://groups.google.com/group/comp.databases.ms-access/msg/4f76d0ed3e5f58ad?hl=en

So why would ANYBODY ever trust a person like you and hire you?
********************************************************

To all: Explanation and more on this answer to Steve:
http://home.tiscali.nl/arracom/stopsteve.html

Arno R