Formatting Excel Spreadsheet from AccessVBA Code

  • Thread starter Thread starter rwboyden via AccessMonster.com
  • Start date Start date
R

rwboyden via AccessMonster.com

!'m using VBA code to create an Excel Spreadsheet via Export from an Access
Query and to format it. The formatting code seems to be undocumented, but
I've been able to adapt Excel macro code to make it work, and the result is
spectacular. Unfortunately, when I need to enter a formula using a reference
to a fixed cell as a multiplier, I'm unable to get the formula to include the
required $ so that cells (or lines) copied from that cell will retain the
relative position of the fixed cell.

The VBA code used to generate the formula is

.ActiveCell.FormulaR1C1 = "=RC[-1]*R[" + strB + "]C"
where strB is the string value of the fixed cell row.
(This
formula may occur in a number of rows.)

The result in the spreadsheet is something like "= J252*K5", where K5 is the
fixed cell. In order to be able to copy rows containing this formula it
needs to read "=I252K$5" but I can't figure out how to get the VBA code to
insert the $. Any ideas?

And if anyone knows where Access VBA Code to format spreadsheets during
export is documented I'd be most grateful.

Many thanks

Bob Boyden
 
The best advice to be had on programming Excel is available in
newsgroups and other resources aimed at Excel.

If I were facing your issue I would get into the Excel environment and
complete the programming and debugging (getting help from Excel
resources as required) and then move the debugged code over to Access
and run and debug it again from there.

HTH
 
Back
Top