Application-defined or object-defined error, Access VBA

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
Asatoma SadgamayaAnalystAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

NorieAnalyst Assistant Commented:
Could you please add code tags?
Asatoma SadgamayaAnalystAuthor Commented:
Hi Norie, I am sorry,  I did not get I am afraid. I have added complete script.
Daniel PineaultPresident / Owner CARDA Consultants Inc.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?
Get a highly available system for cyber protection

The Acronis SDI Appliance is a new plug-n-play solution with pre-configured Acronis Software-Defined Infrastructure software that gives service providers and enterprises ready access to a fault-tolerant system, which combines universal storage and high-performance virtualization.

Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
Are you sure there is an AA column in the Excel sheet?
Asatoma SadgamayaAnalystAuthor Commented:
Thanks Daniel,
Yes it does compile. I have mentioned where the error occures
Ta
Asatoma SadgamayaAnalystAuthor Commented:
Yes Scott, of course
Gustav BrockCIOCommented:
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 LambertConsultingCommented:
instead of fully writing the workbook with VBA, what about saving it as a "template", open it and just copy the data ?
Asatoma SadgamayaAnalystAuthor 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
NorieAnalyst Assistant Commented:
This isn't valid code, there is no range 'AA'.
.Range("AA")

Open in new window

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Asatoma SadgamayaAnalystAuthor Commented:
Norie you are a star. You got those eagle eye.

Thank you
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
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.
Asatoma SadgamayaAnalystAuthor 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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.