Open Excel and fill in certain cells (inherited code)

[Gina Maylone]

Hello all,



I have a client who refuses to give up their Excel spreadsheet for estimating (even thought I've given them a perfectly good interface within Access), so I must give them a button that takes them to the template stored on their network (Cost Sheet_Template) and completes certain cells with the Estimate information from Access. I inherited this code and have made a few minor changes to the field names, and path information. The correct worksheet will open but the data will not populate. I don't know what I'm missing here (or just don't understand the whole thing), would love another set of eyes!

' Late Binding (Needs no reference set)
Dim oXL As Object
Dim oExcel As Object
Dim sFullPath As String
Dim sPath As String
Dim addr As String
 
' Create a new Excel instance
Set oXL = CreateObject("Excel.Application")
 
' Only XL 97 supports UserControl Property
On Error Resume Next
oXL.UserControl = True
On Error GoTo 0
 
' Full path of excel file to open
'On Error GoTo ErrHandle
sFullPath = Me.networkpath & "\Templates\Cost Sheet_Template.xlsm"
 
' Open it
Set xl = CreateObject("Excel.Sheet")
With oXL
.Visible = True
.Workbooks.Open (sFullPath)
End With
    Dim fl_s As String
    Dim draw_num As String
    Dim draw_date As Date
    
Dim fso
Dim file As String
file = Me.Estpath & ".xlsm"
Set fso = CreateObject("Scripting.FileSystemObject")
If Not fso.FileExists(file) Then
    If Len(Forms!currentjobs!Addendums.Form!AddendumNum) > 0 Then
        If Len(Forms!currentjobs!Addendums.Form!AddendumFloors) > 0 Then
            fl_s = Forms!currentjobs!Addendums.Form!AddendumFloors
        Else
            fl_s = Me.FLOORS
        End If
        If Len(Forms!currentjobs!Addendums.Form!AddendumDrawings) > 0 Then
            draw_num = Forms!currentjobs!Addendums.Form![AddendumDrawings]
        Else
            draw_num = Me.[DRAWINGnum]
        End If
        If Len(Forms!currentjobs!Addendums.Form!AddendumDrawingsDate) > 0 Then
            draw_date = Forms!currentjobs!Addendums.Form!AddendumDrawingsDate
        Else
        draw_date = Me.[DRAWING DATE]
        End If
    Else
        'fl_s = Me.FLOORS
        'draw_num = Me.[DRAWINGnum]
        'draw_date = Me.[DRAWING DATE]
    End If
    If Forms!currentjobs!addjobid > 0 Then
        If Forms!currentjobs!addjobid = 1 Then
        Set objXLApp = CreateObject("Excel.Application")
        Set objXLBook = objXLApp.Workbooks.Open(sFullPath)
       
        ElseIf Me.[Forms!currentjobs!Addendums.Form!Addendumnum] > 1 Then
        Set objXLApp = CreateObject("Excel.Application")
        Set objXLBook = objXLApp.Workbooks.Open(sFullPath & Left(Me.[JOBID], 7) & "R" & Format(Forms!currentjobs!Addendums.Form!AddendumNum - 1) & "\Estimates\" & Left(Me.[JOBID], 7) & "R" & Format(Forms!currentjobs!Addendums.Form!AddendumNum - 1) & ".xls")
        End If
            If Len(Forms!currentjobs!Addendums.Form!AddendumNum) > 0 Then
        If Len(Forms!currentjobs!Addendums.Form!AddendumFloors) > 0 Then
            fl_s = Forms!currentjobs!Addendums.Form!AddendumFloors
        Else
            fl_s = Me.FLOORS
        End If
        If Len(Forms!currentjobs!Addendums.Form!AddendumDrawings) > 0 Then
            draw_num = Forms!currentjobs!Addendums.Form![AddendumDrawings]
        Else
            draw_num = Me.[DRAWINGnum]
        End If
        If Len(Forms!currentjobs!Addendums.Form!AddendumDrawingsDate) > 0 Then
            draw_date = Forms!currentjobs!Addendums.Form!AddendumDrawingsDate
        Else
        draw_date = Me.[DRAWING DATE]
        End If
    Else
        fl_s = Me.FLOORS
        draw_num = Me.[DRAWINGnum]
        draw_date = Me.[DRAWING DATE]
    End If
        With objXLBook
        .ActiveSheet.Range("B1") = Me.[JOBID]
        .ActiveSheet.Range("B2") = Me.[JOB NAME]
        .ActiveSheet.Range("B3") = Me.[JOB ADDRESS 1]
        .ActiveSheet.Range("B4") = Me.FLOORS
        .ActiveSheet.Range("B5") = Me.ENGINEER
        .ActiveSheet.Range("G1") = Forms!currentjobs!GCTABLE1.Form![AllGCs]
        .ActiveSheet.Range("G2") = Forms!currentjobs!GCTABLE1.Form![gc1contact]
        .ActiveSheet.Range("G3") = draw_num
        .ActiveSheet.Range("G4") = draw_date
        .ActiveSheet.Range("G5") = Forms!currentjobs!Addendums.Form!AddendumNum
        .ActiveSheet.Range("l5") = Forms!currentjobs!Addendums.Form!AddendumNum
         DoEvents
        
        End With
objXLBook.SaveAs [sFullPath] & ".xlsm"
 'objXLBook.SaveAs "sfullpath &" \ "& [Forms]![currentjobs]![JOB NAME] & .xls"
objXLApp.Application.Visible = True
End If
End If
ErrExit:
Set oXL = Nothing
Exit Sub
 
ErrHandle:
oXL.Visible = False
MsgBox Err.Description
GoTo ErrExit
End Sub

I have verified the cell references are correct and there is data in Access.

Thank you in advance.

[June7]

The code worked before the minor changes?

Now it doesn't but no error message?

I have better luck if I use actual name of sheet instead of ActiveSheet.

[rpeare]

Perhaps a sample database with some garbage data in it too?