A2K- Excel: format a worksheet column as a percentage via VBA

[bf1]

Hi-
Access 2000. I am creating an Excel worksheet via VBA code. Through
the code, I would like to format a column in the worksheet as a
percentage (so it displays 12.4%, as opposed to 0.124, for example).
I've seemingly scoured the sources I know of looking for the solution,
but to no avail.

I think the autoformat attribute (method?) is the key, but I can't get
that to work, nor can I find documentation on it.

Dim objXLSheet As Excel.Worksheet
<snip>
objXLSheet.Range("D").AutoFormat Number:= <I don't know?!>
<snip>

Any ideas?

TIA, and thanks to all who contribute their knowledge here day in and
day out.
Beth F.

 

[Arvin Meyer]

Hi-
Access 2000. I am creating an Excel worksheet via VBA code. Through
the code, I would like to format a column in the worksheet as a
percentage (so it displays 12.4%, as opposed to 0.124, for example).
I've seemingly scoured the sources I know of looking for the solution,
but to no avail.

I think the autoformat attribute (method?) is the key, but I can't get
that to work, nor can I find documentation on it.

Dim objXLSheet As Excel.Worksheet
<snip>
objXLSheet.Range("D").AutoFormat Number:= <I don't know?!>
<snip>

Any ideas?

You may do better in an Excel group, but here goes for my automation
knowledge:

Dim objXLSheet As Excel.Worksheet
objXLSheet.Columns("D").Select
Selection.NumberFormat = "0.00%"

That should format .0124 to 12.40%
--
Arvin Meyer, MCP, MVP
Microsoft Access
Free Access Downloads
http://www.datastrat.com
http://www.mvps.org/access

 

[bf1]

Arvin, thank you for your response. It worked, except I am now having
another problem.

It works once per Access session. I run the code, and the spreadsheet
col. is formatted as it should be. Then, when I run the routine again,
I get the ol' "Object variable or With block variable not set" error.
If I quit Access, it works again, but only once until I quit Access
again. I suspect that the user might want to run the routine more
than once with different parameters without quitting each time...

I've tinkered with it, and it looks like if I comment out just the line
"Selection.NumberFormat = "0.00%", I can run the procedure again and
again with no error without quitting and restarting Access. But of
course, I need that line to properly format my column.

It seems like I am not closing some variable/object or something, but I
can't see where.

Here's a code snippet- edited for simplicity, I hope:

Dim objXLApp As Excel.Application
Dim objXLSheet As Excel.Worksheet
Dim rsTIS As ADODB.Recordset

Set objXLApp = New Excel.Application
objXLApp.Workbooks.Add
Set rsTIS = New ADODB.Recordset

strSql = "SELECT parent_commodity, commodity, VL, costs " & _
" FROM tis " & _
" WHERE " & strWhereString & _
" ORDER BY parent_commodity, costs desc, commodity "


strPrevParentComm = "xyz"
rsTIS.Open strSql, CurrentProject.Connection
rsTIS.MoveFirst

Do Until rsTIS.EOF
strCurrParentComm = rsTIS.Fields("parent_commodity")

If strCurrParentComm <> strPrevParentComm Then
' total costs for current commodity for all VLs
dblCommTotal = DSum("costs", "TIS", strWhereString & _
" AND parent_commodity = """ & strCurrParentComm & """")

' start a new sheet within the workbook
Set objXLSheet = Nothing
Set objXLSheet = objXLApp.Worksheets.Add
objXLSheet.Name = Left(strCurrParentComm, 23) & "- Impact"
End If

' write out the current row MINUS the costs field- need to manipulate
the cost field before writing it
For lngColumn = 0 To rsTIS.Fields.Count - 2
objXLSheet.Range("A" & lngNumRows).Offset(0, lngColumn).Value =
rsTIS.Fields(lngColumn).Value
Next lngColumn

' NOW, write the cost field as a pct of the whole commodity cost
objXLSheet.Range("A" & lngNumRows).Offset(0, 3).Value =
rsTIS.Fields(3).Value / dblCommTotal

' format column D (the cost field) as a pct.
objXLSheet.Range("D" & lngNumRows).Select
Selection.NumberFormat = "0.00%"

strPrevParentComm = strCurrParentComm
rsTIS.MoveNext
Loop

rsTIS.Close
objXLApp.Visible = True

Exit_b_report_click:
DoCmd.Hourglass False
Set rsTIS = Nothing
Set objXLSheet = Nothing
Set objXLApp = Nothing
Exit Sub

b_report_click_ERR:
MsgBox Err.Number & "- " & Err.Description
Resume Exit_b_report_click

End Sub

 

[bf1]

Update--
Still thanks to Arvin, I stumbled upon a slightly different syntax to
format an Excel column as a percentage from Access VBA.
This syntax works without causing the problems I described in my
previous post, which perhaps stemmed from selecting a range(?):

Dim objXLSheet As Excel.Worksheet
<snip>
objXLSheet.Columns("D").NumberFormat = "0.00%"

Again, thanks to all who contribute. You really make my job a lot
easier and more interesting.
Beth F.