Application-defined or object-defined error, Access VBA

Asatoma Sadgamaya
Asatoma Sadgamaya used Ask the Experts™
on
Hi, Please find below code.

Public Sub Dat()
Dim ObjExcel
   
    Set ObjExcel = CreateObject("Excel.Application")
    ObjExcel.Visible = False
    ObjExcel.Workbooks.Open "C:\aa\bb\cc\dd.xlsx"

    Set ObjSheet = ObjExcel.ActiveWorkbook.Worksheets(2)
    ObjSheet.Activate

         ObjSheet.Range("1:1").HorizontalAlignment = xlCenter
         ObjSheet.Range("1:1").VerticalAlignment = xlCenter
      
         ObjSheet.Range("D2").Select
         ObjExcel.ActiveWindow.FreezePanes = True
         ObjExcel.ActiveWindow.DisplayGridlines = False
       
    With ObjSheet
        .Range("Q2") = "=NETWORKDAYS(M2,O2,Holidays!$A$2:$A$909)": .Range("Q2", "Q" & .Cells(.Rows.Count, 1).End(xlUp).Row).FillDown
        .Range("S2") = "=IF(M2="""",""Missing Date"",IF(O2="""",""Missing Date"",IF(Q2<0,""Before"",IF(Q2>R2,""Outside"",""Within""))))": 
        .Range("S2", "S" & .Cells(.Rows.Count, 1).End(xlUp).Row).FillDown
        .Range("T2") = "=N2&P2": .Range("T2", "T" & .Cells(.Rows.Count, 1).End(xlUp).Row).FillDown
        .Range("U2") = "=IF(N2=P2,P2,IF(T2=""Act"",""Act"",""""))": .Range("U2", "U" & .Cells(.Rows.Count, 1).End(xlUp).Row).FillDown
        .Range("V2") = "=U2&"" ""&S2": .Range("V2", "V" & .Cells(.Rows.Count, 1).End(xlUp).Row).FillDown
        
        .Range("Y2") = "=NETWORKDAYS(O2,W2,Holidays!$A$2:$A$909)": .Range("Y2", "Y" & .Cells(.Rows.Count, 1).End(xlUp).Row).FillDown
[b]Error on this line-->[/b].Range("AA") = "=IF(O2="""",""Missing Date"",IF(W2="""",""Missing Date"",IF(Y2<0,""Before"",IF(Y2>Z2,""Outside"",""Within""))))": 
        .Range("AA2", "AA" & .Cells(.Rows.Count, 1).End(xlUp).Row).FillDown
        .Range("AB") = "=P2&X2": .Range("AB2", "AB" & .Cells(.Rows.Count, 1).End(xlUp).Row).FillDown
        .Range("AC") = "=IF(P2=X2,X2,IF(AB2=""Act"",""Act"",""""))": .Range("AC2", "AC" & .Cells(.Rows.Count, 1).End(xlUp).Row).FillDown
        .Range("AD") = "=AC2&"" ""&AA2": .Range("AD2", "AD" & .Cells(.Rows.Count, 1).End(xlUp).Row).FillDown
End with

ObjExcel.ActiveWorkbook.Save
    ObjExcel.ActiveWorkbook.Close
    ObjExcel.Quit
    
    Set ObjExcel = Nothing
    Set ObjSheet = Nothing
    
End Sub

Open in new window



Thank you
A
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
NorieAnalyst Assistant

Commented:
Could you please add code tags?

Author

Commented:
Hi Norie, I am sorry,  I did not get I am afraid. I have added complete script.
Daniel PineaultPresident / Owner CARDA Consultants Inc.
Distinguished Expert 2018

Commented:
Asatoma

Norie means when you add code to a post select it and click on the  Style Code button so it gets formatted properly in your actual posts.  It just makes things easier to read.  Below is an example with your original code block.

Public Sub Dat()
    Dim ObjExcel

    Set ObjExcel = CreateObject("Excel.Application")
    ObjExcel.Visible = False
    ObjExcel.Workbooks.Open "C:\aa\bb\cc\dd.xlsx"

    Set ObjSheet = ObjExcel.ActiveWorkbook.Worksheets(2)
    ObjSheet.Activate

    ObjSheet.Range("1:1").HorizontalAlignment = xlCenter
    ObjSheet.Range("1:1").VerticalAlignment = xlCenter

    ObjSheet.Range("D2").Select
    ObjExcel.ActiveWindow.FreezePanes = True
    ObjExcel.ActiveWindow.DisplayGridlines = False

    With ObjSheet
        .Range("Q2") = "=NETWORKDAYS(M2,O2,Holidays!$A$2:$A$909)": .Range("Q2", "Q" & .Cells(.Rows.Count, 1).End(xlUp).Row).FillDown
        .Range("S2") = "=IF(M2="""",""Missing Date"",IF(O2="""",""Missing Date"",IF(Q2<0,""Before"",IF(Q2>R2,""Outside"",""Within""))))":
        .Range("S2", "S" & .Cells(.Rows.Count, 1).End(xlUp).Row).FillDown
        .Range("T2") = "=N2&P2": .Range("T2", "T" & .Cells(.Rows.Count, 1).End(xlUp).Row).FillDown
        .Range("U2") = "=IF(N2=P2,P2,IF(T2=""Act"",""Act"",""""))": .Range("U2", "U" & .Cells(.Rows.Count, 1).End(xlUp).Row).FillDown
        .Range("V2") = "=U2&"" ""&S2": .Range("V2", "V" & .Cells(.Rows.Count, 1).End(xlUp).Row).FillDown

        .Range("Y2") = "=NETWORKDAYS(O2,W2,Holidays!$A$2:$A$909)": .Range("Y2", "Y" & .Cells(.Rows.Count, 1).End(xlUp).Row).FillDown
        Error on this line-->.Range("AA") = "=IF(O2="""",""Missing Date"",IF(W2="""",""Missing Date"",IF(Y2<0,""Before"",IF(Y2>Z2,""Outside"",""Within""))))":
        .Range("AA2", "AA" & .Cells(.Rows.Count, 1).End(xlUp).Row).FillDown
        .Range("AB") = "=P2&X2": .Range("AB2", "AB" & .Cells(.Rows.Count, 1).End(xlUp).Row).FillDown
        .Range("AC") = "=IF(P2=X2,X2,IF(AB2=""Act"",""Act"",""""))": .Range("AC2", "AC" & .Cells(.Rows.Count, 1).End(xlUp).Row).FillDown
        .Range("AD") = "=AC2&"" ""&AA2": .Range("AD2", "AD" & .Cells(.Rows.Count, 1).End(xlUp).Row).FillDown
    End With

    ObjExcel.ActiveWorkbook.Save
    ObjExcel.ActiveWorkbook.Close
    ObjExcel.Quit

    Set ObjExcel = Nothing
    Set ObjSheet = Nothing
End Sub

Open in new window


Also., considering your code is using late binding, did you define all the Excel constants (xlCenter, xlUp, ...) somewhere?
Does your code compile?
Ensure you’re charging the right price for your IT

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Most Valuable Expert 2012
Top Expert 2014

Commented:
Are you sure there is an AA column in the Excel sheet?

Author

Commented:
Thanks Daniel,
Yes it does compile. I have mentioned where the error occures
Ta

Author

Commented:
Yes Scott, of course
Most Valuable Expert 2015
Distinguished Expert 2018

Commented:
You have not dimmed ObjSheet … which also tells me, that you miss in the top of the module:

Option Explicit

Open in new window

Insert that, compile and correct.
Fabrice LambertConsulting
Distinguished Expert 2017

Commented:
instead of fully writing the workbook with VBA, what about saving it as a "template", open it and just copy the data ?

Author

Commented:
Hi Gustav, could you explain bit more please?

The script was working fine before, the error start pop up when I introduce this bit inside the with statement


       
 .Range("Y2") = "=NETWORKDAYS(O2,W2,Holidays!$A$2:$A$909)": .Range("Y2", "Y" & .Cells(.Rows.Count, 1).End(xlUp).Row).FillDown
Error on this line-->.Range("AA") = "=IF(O2="""",""Missing Date"",IF(W2="""",""Missing Date"",IF(Y2<0,""Before"",IF(Y2>Z2,""Outside"",""Within""))))":
        .Range("AA2", "AA" & .Cells(.Rows.Count, 1).End(xlUp).Row).FillDown
        .Range("AB") = "=P2&X2": .Range("AB2", "AB" & .Cells(.Rows.Count, 1).End(xlUp).Row).FillDown
        .Range("AC") = "=IF(P2=X2,X2,IF(AB2=""Act"",""Act"",""""))": .Range("AC2", "AC" & .Cells(.Rows.Count, 1).End(xlUp).Row).FillDown
        .Range("AD") = "=AC2&"" ""&AA2": .Range("AD2", "AD" & .Cells(.Rows.Count, 1).End(xlUp).Row).FillDown

Open in new window


Thank you
Analyst Assistant
Commented:
This isn't valid code, there is no range 'AA'.
.Range("AA")

Open in new window

Author

Commented:
Norie you are a star. You got those eagle eye.

Thank you
Most Valuable Expert 2012
Top Expert 2014

Commented:
I said the same thing earlier ("Are you sure there is an AA column in your sheet").
NorieAnalyst Assistant

Commented:
Eh, I don't know how this works but perhaps Scott should be given the points rather than myself.

Author

Commented:
Sorry scott,

I took it in the wrong way. There is a AA column in the sheet. But the correction need was .Range("AA2")= instead .Range("AA")=

Sorry for the hassle

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial