Setting COM Reference is not enough?

[prabha_friend]

Suppose I am setting reference to an Excel application in an Access VBA project (an mdb) and creating an excel application in a procedure called sub_proc1(). Suppose I want to work with Excel application and its objects created by the proc1 in an another procedure called. Suppose Test_format(). But I get the error 'Object variable or with Variable not set'. Is setting the COM Reference is not only enought. I want to understand fundamentals too. Kindly help.
Sub test_format()
Dim cformat As String
cformat = "0.00_);[Red](0.00)"
Excel.Application.ActiveWorkbook.ActiveSheet.Cells(6, 3).Select
Selection.CellFormat = cformat
End Sub

 

[JPearson]

Ive done very little with controling excel from access in VB but my first thought when i look at the code I do have it to call excel as an object.

Dim AppExcel As Object
Set AppExcel = CreateObject("Excel.Application")
 
AppExcel.workbooks.Open FileName
or
AppExcel.Workbooks.Activate FileName

Something along those lines maybe?

 

[ghudson]

Try something like this to edit the Excel file from Access...

    Dim xlApp As Object
    Dim xlSheet As Object

    Set xlApp = CreateObject("Excel.Application")
    Set xlSheet = xlApp.Workbooks.Open(sPathFile).Sheets(1)
    
    With xlApp
            .Application.Sheets("MyWorkseet").Select
            .Application.Cells.Select
            .Application.Selection.ClearFormats
            .Application.Selection.Font.Name = "Arial"
            .Application.Rows("1:1").Select
            .Application.Selection.Font.Bold = True
            .Application.Range("J:T,V:V,W:W,Y:AA").Select
            .Application.Selection.NumberFormat = "mm/dd/yyyy"
            .Application.Cells.Select
            .Application.Selection.RowHeight = 12.75
            .Application.Selection.Columns.AutoFit
            .Application.Range("B2").Select
            .Application.ActiveWindow.FreezePanes = True
            .Application.Range("A1").Select
            .Application.ActiveWorkbook.Save
            .Application.ActiveWorkbook.Close
            .Quit
    End With

    Set xlApp = Nothing
    Set xlSheet = Nothing