473,573 Members | 2,849 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

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.Applicati on
Dim objWkb As Workbook
Dim objSht As Worksheet
Set rs = CurrentDb.OpenR ecordset("qryWa stageReport", _
dbOpenSnapshot)
intMaxCol = rs.Fields.Count
If rs.RecordCount > 0 Then
rs.MoveLast: rs.MoveFirst
intMaxRow = rs.RecordCount
Set objXL = New Excel.Applicati on
With objXL
.Visible = True
Set objWkb = .Workbooks.Add
Set objSht = objWkb.Workshee ts(1)
With objSht
.Range(.Cells(1 , 1), .Cells(intMaxRo w, _
intMaxCol)).Cop yFromRecordset 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).Interi or.ColorIndex = 34
Columns("E:E"). Select
Selection.Numbe rFormat = "$#,##0.00"
Columns("G:G"). Select
Selection.Numbe rFormat = "$#,##0.00"
Columns("I:I"). Select
Selection.Numbe rFormat = "$#,##0.00"
End With

Nov 13 '05 #1
4 8835
BerkshireGuy wrote in message
<11************ **********@f14g 2000cwb.googleg roups.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.Applicati on
Dim objWkb As Workbook
Dim objSht As Worksheet
Set rs = CurrentDb.OpenR ecordset("qryWa stageReport", _
dbOpenSnapshot)
intMaxCol = rs.Fields.Count
If rs.RecordCount > 0 Then
rs.MoveLast: rs.MoveFirst
intMaxRow = rs.RecordCount
Set objXL = New Excel.Applicati on
With objXL
.Visible = True
Set objWkb = .Workbooks.Add
Set objSht = objWkb.Workshee ts(1)
With objSht
.Range(.Cells(1 , 1), .Cells(intMaxRo w, _
intMaxCol)).Cop yFromRecordset 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).Interi or.ColorIndex = 34
Columns("E:E"). Select
Selection.Numbe rFormat = "$#,##0.00"
Columns("G:G"). Select
Selection.Numbe rFormat = "$#,##0.00"
Columns("I:I"). Select
Selection.Numbe rFormat = "$#,##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).Interi or.ColorIndex = 34
Columns("E:E"). Select
Selection.Numbe rFormat = "$#,##0.00"
Columns("G:G"). Select
Selection.Numbe rFormat = "$#,##0.00"
Columns("I:I"). Select
Selection.Numbe rFormat = "$#,##0.00"


The last 6 lines in the above code should be:

..Columns("E:E" ).Select
..Selection.Num berFormat = "$#,##0.00"
..Columns("G:G" ).Select
..Selection.Num berFormat = "$#,##0.00"
..Columns("I:I" ).Select
..Selection.Num berFormat = "$#,##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
35513
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 would like to open an existing MS Excel spreadsheet and extract information from specific worksheets and cells. I'm not really sure how to get started with this process. I ran the COM Makepy...
3
4527
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. Some of the cells contain numbers like 12345.6 and when DTS is done importing it into a field that is nvarchar the results are "12345.600000000001". I have tried: 1. Changing the format of the...
0
3080
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 Function Export_Excel_9(tbx1 As Variant, tbx2 As Variant, tbx3 As Variant, tbx4 As Variant, tbx5 As Variant, tbx6 As Variant, tbx7 As
5
4070
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 running my data to excel. However, I need the data to export into Excel in a certain format, i.e it needs to begin importing at cell A4, and in truth it would be great
2
1582
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. The field in access is a numeric with two decimals. When exported, everything to the right of the decimal is dropped (whether or not the number is "0"). In my Excel template, the entire sheet...
4
4383
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 be appreciated! Below is my code- Dim t As Integer Dim testCount As Boolean testCount = False For t = 0 To UserListView.Items.Count - 1
5
4162
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' or 'save' the generated Excel file. Actually I'm exporting by rendering my table to an htmlwriter and sending this to excel. This is working nearly perfect. There's a problem with Excel's...
1
3046
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 ok. However, the second recordset onwards does not not export line excelsheet.Cells(9, 2) = rsschedulesrecords.Fields(2).Value. Below is my code. Please help? Private Sub...
6
9009
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 is when i use the extracted data to create a chart using vba and it doesn't seem to display the data properly.. it is due to the text format when the data is extracted to excel.. how can i change it...
0
7679
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language...
0
7996
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. ...
0
8191
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that...
0
8049
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the...
1
5573
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes...
0
5284
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert...
0
3723
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
1
2185
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
0
1029
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating...

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.