Hi everyone,
Looking for some help or ideas, on the folloiwng problem.
Have an Access 2003 application that creates an Excel file containing
data from an Access parameter query. This is accomplished with the
following line:
DoCmd.TransferSpreadsheet acExport, , "MyQuery", "ExcelFileName", True
That's the easy part. The problem is, the audience this Excel file is
intended for requires the workbook created by the above line to not
only contain the proper data (provided by the query), but also, for
the worksheet containing that data to be formatted in a specific way.
I would like to be able to write some VBA code to format the worksheet
to the required specs. The actual code that does the formatting can be
easily created by recording the steps of a macro in Excel while I
format the sheet as desired. Now, I would like to be able to run that
code from inside Access, right after the above line
(DoCmd.TransferSpreadsheet acExport....) runs.
1) Ideally, I would like Access itself, via the VBA code, to
manipulate (format) the Excel sheet just created, so this could happen
in any computer, whether Excel is installed in it or not. Is this
possible? How?
2) If that is not possible, and Excel NEEDS to be launched from the
Access VBA code to achieve this, can it be done in a completely silent
mode, so the Access application user is not aware that Excel launched,
formatted a sheet and closed? If that is the only manner to achieve my
goal, how can this be accomplished?