Help with code in an Access module!!

[mmax98]

Hello,
I am fairly new a code. I am having a problem with VB code in Access. I am trying to format a report by using the Run Excel command button, but I am running across a compile error "Sub or Function not defined." on Range. Is it necessary to call a function for Range? If so, can someone please help me!!

I would appreciate any input and resolution.

Regards,
MMAX

Private Sub Command3_Click()
On Error GoTo Err_Command3_Click

Dim oApp As Object

Set oApp = CreateObject("Excel.Application&quot
oApp.Visible = True
'Only XL 97 supports UserControl Property
On Error Resume Next
oApp.UserControl = True

'Create a new workbook for import
Workbooks.Add
ChDir "C:\"
Workbooks.OpenText Filename:="C:\component.xls", Origin:=xlWindows, _
StartRow:=68, DataType:=xlFixedWidth, FieldInfo:=Array(Array(0, 1), Array(7 _
, 1), Array(23, 1), Array(53, 1), Array(69, 1), Array(77, 1), Array(91, 1), Array(98, 1), _
Array(106, 1), Array(115, 1), Array(124, 1), Array(134, 1), Array(143, 1), Array(152, 1))
ActiveWorkbook.SaveAs Filename:="C:\component.xls", FileFormat:=xlNormal, _
Password:="", WriteResPassword:="", ReadOnlyRecommended:=False, _
CreateBackup:=False
Range("F9&quot.Select
ActiveWorkbook.RunAutoMacros Which:=xlAutoClose
End Sub

 

[Rhonin]

I have not played with this much.
You have to specify the range.
Ex: Set objXLRange = objYourSheet.Range(.....
Hope this helps.


Rhonin
"too many questions, too little time..."

 

[DerekAOE]

Firstly ensure that you have referenced the Excel Object Library. In VBA window, select Tools - References, then tick Excel n.0 Object Library in the list, where n is the version number (n=9 for Excel 2K).

Secondly, you need to fully qualify the objects you are referencing so that Access knows what to use. For instance, in your code, use ...

Dim oApp as Excel.Application
Dim oWkBk as Excel.Workbook

Set oApp = CreateObject("Excel.Application&quot

With oApp
.Visible = True
'Only XL 97 supports UserControl Property
On Error Resume Next
On Error Resume Next
.UserControl = True

'Create a new workbook for import
set oWkBk = .Workbooks.Add
ChDir "C:\"

with oWkBk
.OpenText Filename:="C:\component.xls", Origin:=xlWindows, _
StartRow:=68, DataType:=xlFixedWidth, FieldInfo:=Array(Array(0, 1), Array(7 _
, 1), Array(23, 1), Array(53, 1), Array(69, 1), Array(77, 1), Array(91, 1), Array(98, 1), _
Array(106, 1), Array(115, 1), Array(124, 1), Array(134, 1), Array(143, 1), Array(152, 1))
ActiveWorkbook.SaveAs Filename:="C:\component.xls", FileFormat:=xlNormal, _
Password:="", WriteResPassword:="", ReadOnlyRecommended:=False, _
CreateBackup:=False
.Range("F9&quot.Select
end with
.ActiveWorkbook.RunAutoMacros Which:=xlAutoClose
end with

I have not tested this code but you get the idea. For more info consult the Excel Object model to find out which methods apply to which objects. For instance, the Range object can be a property of more than one thing (Workbook, Worksheet, etc) which is why you need to fully qualify the reference in your code.

HTH