convert whole excel file to vba (tough question?)

[Florianbur]

Hi, i have a little problem that i am not sure of how to solve.

I have an access database running, which eventually is giving m
reports. These reports are built to look like the original reports
which were excel reports.

These access reports are pretty uncomfortable though, and so i woul
like to create excel reports out of the database in access.

So I thought about taking the old excel files, exporting the list wit
the results (only the numbers, not any formating or so) in a new shee
in that excel file, and linking all data in the excel report to th
sheet with the info. I would then hide the excel sheet, and all I'
have to do, is to export the data in the future in that hiden sheet an
zoom, i got the report in excel. I thought this to be a pretty goo
way, but maybe one of u knows a better one too.

Now the thing is, I don't want to keep the excel file around. Like
want a button in the access database "create excel report", which the
in VBA starts excel, creates a new workbook and creates the report tha
i described above.

However, I don't want to enter all the formating info etc. manually i
VBA skript.

So what I hope to be possible is to open the old excel file an
excecute a function like "simulate buildup of this page in VBA". Th
result should be a makro that constructs a new excel file that look
exactly like that one, specifying all the colors, fonts, texts, etc...

Does something like this exist? Is there a way to do it? Or am I jus
way off? What is the best way to adress such a problem?

Thanks for reading this, anyho

 

[Florianbur]

I would be really gratefull for any opionions, even if it should not b
possible to do..

 

[Earl Kiosterud]

Florian,

What??? You have a database in Access, and want to have reports thru Access?
What's "uncomfortable" about them. Access has better screen layout
flexibility (forms) and print layout flexibility (reports) than does Excel,
which is limited pretty much to showing and printing the sheet the way it is
with a few variations. If this is in fact a database application, and if
you don't need a lot of data analysis, generally you wouldn't want to bring
your stuff into Excel. Say why you don't like the Access reports.

 

[Stephen Bullen]

Hi Florianbur,

Now the thing is, I don't want to keep the excel file around. Like I
want a button in the access database "create excel report", which then
in VBA starts excel, creates a new workbook and creates the report that
i described above.

However, I don't want to enter all the formating info etc. manually in
VBA skript.

I'm afraid that you'll have to do one of the above, though I guess it's
possible to include the template workbook in an OLEObject field in your
database and extract it when you want to use it (but I don't think I'd
recommend that approach <g>).

Regards

Stephen Bullen
Microsoft MVP - Excel
www.BMSLtd.ie