Working with Excel objects from Access

[Jey]

Hi,
I'm trying to do some "pre-import" formatting to an excel sheet before the
data gets pulled into Access. I recorded the steps I want as a macro in
excel, then copied the code into my Access VBA code. Some of it works fine
after some treaking, some doesn't!

Here are the relevant bits of code:

Dim objXL As Object
Set objXL = CreateObject("Excel.Application")
objXL.Workbooks.Open (strInputFileName)
objXL.Visible = True

With objXL.ActiveWorkbook.Worksheets("SurveyDataLoad")
.Visible = True
.Activate
.Cells.Select
.Cells.Copy
.Cells.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
End With

objXL.CutCopyMode = False

With objXL.ActiveWorkbook.Worksheets("SurveyDataLoad")
.Cells.NumberFormat = "General"
.Cells.Replace What:="#N/A", Replacement:="", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
End With

The problem seems to be with the arguments to the PasteSpecial and Replace
methods... I get "complile error, variable not defined". Can anyone tell me
how to get those to work?

I have to keep it as late binding because the application has to work in
Access/Excel 2003 and 2007!

Thanks in advance,
Jey

 

[Stuart McCall]

Hi,
I'm trying to do some "pre-import" formatting to an excel sheet before the
data gets pulled into Access. I recorded the steps I want as a macro in
excel, then copied the code into my Access VBA code. Some of it works fine
after some treaking, some doesn't!

Here are the relevant bits of code:

Dim objXL As Object
Set objXL = CreateObject("Excel.Application")
objXL.Workbooks.Open (strInputFileName)
objXL.Visible = True

With objXL.ActiveWorkbook.Worksheets("SurveyDataLoad")
.Visible = True
.Activate
.Cells.Select
.Cells.Copy
.Cells.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
End With

objXL.CutCopyMode = False

With objXL.ActiveWorkbook.Worksheets("SurveyDataLoad")
.Cells.NumberFormat = "General"
.Cells.Replace What:="#N/A", Replacement:="", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
End With

The problem seems to be with the arguments to the PasteSpecial and Replace
methods... I get "complile error, variable not defined". Can anyone tell
me
how to get those to work?

I have to keep it as late binding because the application has to work in
Access/Excel 2003 and 2007!

Thanks in advance,
Jey

Open Excel's VBE and use the immediate window to discover the values for
xlPasteValues, xlNone, xlPart and xlByRows. Then switch to Access's VBE and
declare them as consts at the top of the module, like:

Private Const xlPasteValues = <whatever>
....

Because you're using late binding (ie no reference) the values aren't
defined, so you must roll your own.

 

[Jey]

I never would have thought of that! Thanks very much.
Jey