automation with access

[cmledbetter]

Setting up automation with access to format excel file being produced
following Doug Steele's code from article Access Answers: Excelling
Automatically related to formatting excel spreadsheet during export process.
in this article Doug describes a process to help identify code for formatting
Excel workbook by recording excel macro to help create vb code related to the
formatting desired.

This process has worked very well for me up until I needed to convert a
range of cells from numbers stored as text to a number format. The code used
to accomplish this in excel is as follows

Range("M5").Select
ActiveCell.FormulaR1C1 = "1"
Range("M5").Select
Selection.Copy
Columns("J:J").Select
Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlMultiply, _
SkipBlanks:=False, Transpose:=False

This copies a cell with a number value of 1 then will use the paste special
option to multiply a range of cells by 1 to convert these cells to a number
format.

When I converted the code snippet to use in my access module I used the
following code and it errors related to variable not identified at the
"Paste:=xlPasteAll" statement

' Export all of the data from the recordset to the worksheet
.Cells(2, 1).CopyFromRecordset rsCurr
' Make the first row Bold
.Rows("1:1").Font.Bold = True
' Autofit all of the columns to the data
' Format columns for field length and data type
.Range(.Columns(1),
..Columns(1).End(-4161)).Columns.Autofit
.Columns("C:C").NumberFormat = "0"
.Columns("C:C").NumberFormat = "000"
.Columns("B:B").NumberFormat = "000000"
.Columns("G:G").NumberFormat = "000"
.Columns("J:J").PasteSpecial Paste:=xlPasteAll,
Operation:=xlMultiply, SkipBlanks:=False, Transpose:=False
.Columns("J:J").NumberFormat = "0000"
.Columns("H:H").NumberFormat = "mm/dd/yyyy"

I have searched multiple resources on the net in effort to learn or
understand enough of coding to correct this issue but at present
unsuccessful.

I think I understand why this code is not working but unable to determine
how to make it work

Please advise

 

[cmledbetter]

Part of problem solved. I was pretty sure that I need the excel reference
library to be open but it was not and since I had two instances of the VB
editor open I was not able to add reference libraries. I have added the
reference library. But now I seem to have a problem passing the numeric value
of "1" to the paste special line

 

[Tony Toews [MVP]]

Part of problem solved. I was pretty sure that I need the excel reference
library to be open but it was not and since I had two instances of the VB
editor open I was not able to add reference libraries. I have added the
reference library.

Glad to hear you've got it going.

But now I seem to have a problem passing the numeric value
of "1" to the paste special line

Please post the lines of code causing you the troubles.

Tony
--
Tony Toews, Microsoft Access MVP
Tony's Main MS Access pages - http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/
For a convenient utility to keep your users FEs and other files
updated see http://www.autofeupdater.com/
Granite Fleet Manager http://www.granitefleet.com/

 

[cmledbetter]

Tony my apologies

For some reason I had not received notification that you had responded to my
post. I have since solved the problem a different way. I am using a make
table query to extract the data I need and perform the data type conversion
during this process as well as some content mapping conversion. Then I use
the automation code to format my Excel output is required for the vendor.

I appreciate your response but problem solved