By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
434,905 Members | 2,064 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 434,905 IT Pros & Developers. It's quick & easy.

Excel Automatation and Changing Format Of Cells

P: n/a
I have the following Access code that exports to Excel, inserts a
title, changes the color of the title, and then changes the format of
several columns to currency.

The following code sometimes works and sometimes doesnt. When it
doesnt, it gives me a 1004 "Method of Columns" of object variable
failed error.
My only guess is a timing issue. For instance, sometimes Excel is
ready for Access's next "command" and somtimes it's not.

Is there better code to do this (my first access export) or should I
just error trap it?

Thanks
'Send records to the first
'sheet in a new workbook
'
Dim rs As Recordset
Dim intMaxCol As Integer
Dim Range As Range
Dim intMaxRow As Integer
Dim objXL As Excel.Application
Dim objWkb As Workbook
Dim objSht As Worksheet
Set rs = CurrentDb.OpenRecordset("qryWastageReport", _
dbOpenSnapshot)
intMaxCol = rs.Fields.Count
If rs.RecordCount > 0 Then
rs.MoveLast: rs.MoveFirst
intMaxRow = rs.RecordCount
Set objXL = New Excel.Application
With objXL
.Visible = True
Set objWkb = .Workbooks.Add
Set objSht = objWkb.Worksheets(1)
With objSht
.Range(.Cells(1, 1), .Cells(intMaxRow, _
intMaxCol)).CopyFromRecordset rs
End With
End With
End If

'Brian's Code
With objSht
.Rows(1).Insert
.Cells(1, 10).Value = "Agency Wastage By Producer Type"
.Cells(1, 10).Font.Size = 20
.Rows(1).Interior.ColorIndex = 34
Columns("E:E").Select
Selection.NumberFormat = "$#,##0.00"
Columns("G:G").Select
Selection.NumberFormat = "$#,##0.00"
Columns("I:I").Select
Selection.NumberFormat = "$#,##0.00"
End With

Nov 13 '05 #1
Share this Question
Share on Google+
4 Replies


P: n/a
BerkshireGuy wrote in message
<11**********************@f14g2000cwb.googlegroups .com> :
I have the following Access code that exports to Excel, inserts a
title, changes the color of the title, and then changes the format of
several columns to currency.

The following code sometimes works and sometimes doesnt. When it
doesnt, it gives me a 1004 "Method of Columns" of object variable
failed error.
My only guess is a timing issue. For instance, sometimes Excel is
ready for Access's next "command" and somtimes it's not.

Is there better code to do this (my first access export) or should I
just error trap it?

Thanks
'Send records to the first
'sheet in a new workbook
'
Dim rs As Recordset
Dim intMaxCol As Integer
Dim Range As Range
Dim intMaxRow As Integer
Dim objXL As Excel.Application
Dim objWkb As Workbook
Dim objSht As Worksheet
Set rs = CurrentDb.OpenRecordset("qryWastageReport", _
dbOpenSnapshot)
intMaxCol = rs.Fields.Count
If rs.RecordCount > 0 Then
rs.MoveLast: rs.MoveFirst
intMaxRow = rs.RecordCount
Set objXL = New Excel.Application
With objXL
.Visible = True
Set objWkb = .Workbooks.Add
Set objSht = objWkb.Worksheets(1)
With objSht
.Range(.Cells(1, 1), .Cells(intMaxRow, _
intMaxCol)).CopyFromRecordset rs
End With
End With
End If

'Brian's Code
With objSht
.Rows(1).Insert
.Cells(1, 10).Value = "Agency Wastage By Producer Type"
.Cells(1, 10).Font.Size = 20
.Rows(1).Interior.ColorIndex = 34
Columns("E:E").Select
Selection.NumberFormat = "$#,##0.00"
Columns("G:G").Select
Selection.NumberFormat = "$#,##0.00"
Columns("I:I").Select
Selection.NumberFormat = "$#,##0.00"
End With


The error is most probably caused by your implicit referencing of
the selection and column objects/properties of Excel in the last
bit of code. I'd bet you have at least one extra instance of Excel
in memory too, after running this (check with Task Manager.

Every Excel object or property will need to be prefixed with the
relevant object variable, in this case (same format) you should
be able to replace the last part with:

.Columns("E:I").NumberFormat = "$#,##0.00"

(remember - within the With block, and prefixed with a dot (.),
meaning it's "dangling" off the worksheet object.

--
Roy-Vidar

Nov 13 '05 #2

P: n/a


BerkshireGuy wrote:
I have the following Access code that exports to Excel, inserts a
title, changes the color of the title, and then changes the format of
several columns to currency.
The following code sometimes works and sometimes doesnt. When it
doesnt, it gives me a 1004 "Method of Columns" of object variable
failed error. 'Brian's Code
With objSht
.Rows(1).Insert
.Cells(1, 10).Value = "Agency Wastage By Producer Type"
.Cells(1, 10).Font.Size = 20
.Rows(1).Interior.ColorIndex = 34
Columns("E:E").Select
Selection.NumberFormat = "$#,##0.00"
Columns("G:G").Select
Selection.NumberFormat = "$#,##0.00"
Columns("I:I").Select
Selection.NumberFormat = "$#,##0.00"


The last 6 lines in the above code should be:

..Columns("E:E").Select
..Selection.NumberFormat = "$#,##0.00"
..Columns("G:G").Select
..Selection.NumberFormat = "$#,##0.00"
..Columns("I:I").Select
..Selection.NumberFormat = "$#,##0.00"

Note the addition of the "."

Nov 13 '05 #3

P: n/a
Well I added that, now I am getting "Method or data member not found."

Nov 13 '05 #4

P: n/a
Roy,

Didnt see your reply,

That worked fine final lines of code:

..Columns("E:E").NumberFormat = "$#,##0.00"
.Columns("G:G").NumberFormat = "$#,##0.00"
.Columns("I:I").NumberFormat = "$#,##0.00"
.Columns("K:K").NumberFormat = "$#,##0.00"
.Columns("M:M").NumberFormat = "$#,##0.00"
.Columns("O:O").NumberFormat = "$#,##0.00"
.Columns("Q:Q").NumberFormat = "$#,##0.00"
.Columns("S:S").NumberFormat = "$#,##0.00"

Thank you both for the help!

-Brian

Nov 13 '05 #5

This discussion thread is closed

Replies have been disabled for this discussion.