472,353 Members | 1,403 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 472,353 software developers and data experts.

Excel Automatation and Changing Format Of Cells

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
4 8759
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


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
Well I added that, now I am getting "Method or data member not found."

Nov 13 '05 #4
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 thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

13
by: Allison Bailey | last post by:
Hi Folks, I'm a brand new Python programmer, so please point me in the right direction if this is not the best forum for this question.... I...
3
by: Don.Vonderburg | last post by:
I am having a problem importing an Excel spreadsheet. I have a column in an Excel sheet with alphanumeric text and some of the cells are numeric....
0
by: ImraneA | last post by:
Hi there Many thanks to those people who contributed to this group, helped me greatly. Enclose, my code, hope it helps others :- Public...
5
by: Jonny | last post by:
Hello, I have created a button on my form which when pressed does the following : 1) Run a pre-defined Macro, which is picking up a query and...
2
by: EJO | last post by:
TIA, it is much appreciated! I export data from A2k to Excel 2k, and everything runs nicely...except when i export a particular A2k number field....
4
by: Powerguy | last post by:
Hi all, I've spent countless hours trying to get this silly EXCEL process to close once I have used it. If anyone can help it would really REALLy...
5
by: =?Utf-8?B?c2NobWlkdGU=?= | last post by:
Hi How can I Export an HTML Table to excel? My goal is a button, and when the user clicks this button a popup appears asking the user to 'open'...
1
by: accessvbanewbie | last post by:
I would like to export a recordset from access to excel but after each record is exported I want to insert a new row. The first recordset does this...
6
by: and1 | last post by:
hello .. hmm currently i'm working on vba in excel, apparently i use ADO to extract a table data from access to excel and it works fine. the problem...
0
by: Naresh1 | last post by:
What is WebLogic Admin Training? WebLogic Admin Training is a specialized program designed to equip individuals with the skills and knowledge...
0
jalbright99669
by: jalbright99669 | last post by:
Am having a bit of a time with URL Rewrite. I need to incorporate http to https redirect with a reverse proxy. I have the URL Rewrite rules made...
0
by: antdb | last post by:
Ⅰ. Advantage of AntDB: hyper-convergence + streaming processing engine In the overall architecture, a new "hyper-convergence" concept was...
0
by: Matthew3360 | last post by:
Hi there. I have been struggling to find out how to use a variable as my location in my header redirect function. Here is my code. ...
2
by: Matthew3360 | last post by:
Hi, I have a python app that i want to be able to get variables from a php page on my webserver. My python app is on my computer. How would I make it...
0
by: AndyPSV | last post by:
HOW CAN I CREATE AN AI with an .executable file that would suck all files in the folder and on my computerHOW CAN I CREATE AN AI with an .executable...
0
hi
by: WisdomUfot | last post by:
It's an interesting question you've got about how Gmail hides the HTTP referrer when a link in an email is clicked. While I don't have the specific...
0
by: Matthew3360 | last post by:
Hi, I have been trying to connect to a local host using php curl. But I am finding it hard to do this. I am doing the curl get request from my web...
0
by: Carina712 | last post by:
Setting background colors for Excel documents can help to improve the visual appeal of the document and make it easier to read and understand....

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.