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:
I hope someone can help me out with this! Thank you!!
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:
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!!