Run-time error '91' - Object variable or With bloc variable not set

[TTUK]

Hi all,

I'm struggling to get a SaveAs script to run as it keep coming up with this Run-time error '91'.

My issue: I need the script to create a new workbook with the pasted information, apply both the .bas and .txt imports and then SaveAs the filename in cell A1 then close.

Module name: IIR_Confirm

Error: Run-time error '91' - Object variable or With bloc variable not set

Highlighted line with the error below: ActiveWorkbook.SaveAs filename:=fullPath, FileFormat:=xlOpenXMLWorkbookMacroEnabled, CreateBackup:=False

Reference information
The fullPath = ("A1") shows the full filename of where I want to Active workbook to be saved.
e.g. \\dataserver\ManagementSystem\NYL-HSE Group\Accidents, Near Misses & Hazard Alerts\AIR-800\AIR-800_04.01.2018_OFA_Incident Investigation Report.xlsm
This changes each time a new report is generated within the Register.

Code:

Sub Followup_Confirm()

Application.ScreenUpdating = False
Application.DisplayAlerts = False


    Dim Msg As String, Ans As Variant


               Msg = "Confirm you would like to raise a Follow-up form"


    Ans = MsgBox(Msg, vbYesNo)


    Select Case Ans


    Case vbYes


        ' Copy date and paste in cell on row
        ActiveCell.FormulaR1C1 = "Form Raised"
        ActiveCell.Offset(0, 1).Select
        ActiveCell.FormulaR1C1 = "=R4C54"


        Selection.Copy


        Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
        Application.CutCopyMode = False


        Sheets("IIR_data").Select
        Range("A8").Select
        Selection.Copy
        Sheets("IIR_temp").Select
        Range("A1").Select
        Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
        Application.CutCopyMode = False


        
        ' Select the worksheet that is to be saved
        Sheets("IIR_temp").Select
        
        
        ActiveSheet.Copy ' Copies active sheet to a new workbook
             
        Range("A2:BG16").Select
        Selection.Copy
        
        Range("A2").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
        
Application.CutCopyMode = False
         
        'Import BAS file to new workbook
        Dim filename As String
        filename = "\\dataserver\ManagementSystem\NYL-HSE Group\Accidents, Near Misses & Hazard Alerts\IIR_Exchange.bas"
        Application.VBE.ActiveVBProject.VBComponents.Import (filename)
        
         
        'Import CLS file to new workbook
        With ActiveWorkbook.VBProject.VBComponents("ThisWorkbook").CodeModule
             .DeleteLines StartLine:=1, Count:=.CountOfLines
            .AddFromFile "\\dataserver\ManagementSystem\NYL-HSE Group\Accidents, Near Misses & Hazard Alerts\IIRSaveUpdate.txt"
        End With


        Range("AX2").Select


Application.ScreenUpdating = True
Application.DisplayAlerts = True


fullPath = Range("A1")
[COLOR=#ff0000][B]ActiveWorkbook.SaveAs filename:=fullPath, FileFormat:=xlOpenXMLWorkbookMacroEnabled, CreateBackup:=Fals[/B][/COLOR]e


ActiveWorkbook.Close


Sheets("Register").Select


MsgBox ("Completed")


    
End Select
End Sub

I hope someone can help me out with this! Thank you!!

 

[NdNoviceHlp]

U could Dim fullpath as String and

fullPath = CSTR(Sheets("IIR_temp").Range("A1"))

HTH. Dave

 

[TTUK]

Hi Dave,

Thank you for the reply.
I have added the code as per the above.

fullPath = CStr(Sheets("IIR_temp").Range("A1"))
ActiveWorkbook.SaveAs filename:=fullPath, FileFormat:=xlOpenXMLWorkbookMacroEnabled, CreateBackup:=False

However, it still debugs with an error with ActiveWorkbook.SaveAs filename:=fullPath, FileFormat:=xlOpenXMLWorkbookMacroEnabled, CreateBackup:=False

It's a Run-time error '91' Object variable or With block variable not set


Thanks,

 

[Norie]

When/how are you running the code?

 

[NdNoviceHlp]

Is this actually a path (ie. no drive) that can be saved to?
\\dataserver\ManagementSystem\NYL-HSE Group\Accidents, Near Misses & Hazard Alerts\AIR-800\AIR-800_04.01.2018_OFA_Incident Investigation Report.xlsm
Dave