File will not open after created with VBA

troyh68

New Member
Joined
Nov 14, 2016
Messages
24
I have created a file from VBA code that will not open after it is created. I found that you have to setup FileFormat in the code to fix this issue however I am now I am getting a compile error (Wrong number of arguments or invalid property assignment) on the SaveCopyAs My code is below. As you can tell I have tried every different combination I can find.

VBA Code:
Sub IncidentReport()
'Save to File Destination
 
Dim FileName As String
Dim Path As String

'Application.DisplayAlerts = False
FileName = Range("AH2").Value & ".xlsx"
Path = "Z:\TESTING\"
'ActiveWorkbook.SaveCopyAs FileName:="Z:\TESTING\" & Range("AH2").Value & ".xlsx", _
    FileFormat:=1, CreateBackup:=False
'ActiveWorkbook.SaveAs FileName:= _
    "Z:\SAFETY\Incident Reports\" & FileName, FileFormat:= _
    xlOpenXMLWorkbook, CreateBackup:=False
'Application.DisplayAlerts = True
ActiveWorkbook.SaveCopyAs Path & FileName, FileFormat:=xlOpenXMLWorkbook

End Sub
 

Attachments

  • SaveCopyAs error.png
    SaveCopyAs error.png
    14.7 KB · Views: 4

Excel Facts

Quick Sum
Select a range of cells. The total appears in bottom right of Excel screen. Right-click total to add Max, Min, Count, Average.
When using SaveCopyAs you don't specify the file format as the copy being created will have the same format as the file being copied.

What is it you are trying to do?
 
Upvote 0
I figured it out. I Only did a save instead of the SaveCopyAs and it worked.
 
Upvote 0
Solution
I was trying to save the Current Workbook which is an .xlsm file to a .xlsx file however now I need to deal with the Question about continuing to save the file as a macro-free workbook, click yes message. I Do want to save the file Macro Free. I just don't know how to get the answer to always be yes.
 
Last edited:
Upvote 0
Perhaps you should look at using a template?

That template would be code free but you could use the xlsm file to populate it and then save it.

Not sure if that would work for you as I'm not sure what kind of process you are following.
 
Upvote 0
... now I need to deal with the Question about continuing to save the file as a macro-free workbook, click yes message.

@troyh68, did you try the SaveAs method, like in:

VBA Code:
Application.DisplayAlerts = False
ActiveWorkbook.SaveAs Path & FileName, FileFormat:=xlOpenXMLWorkbook
Application.DisplayAlerts = True
 
Upvote 0

Forum statistics

Threads
1,212,933
Messages
6,110,752
Members
448,295
Latest member
Uzair Tahir Khan

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top