VBA saveas causing corruption

[anichols]

Hello all,

I have a code opening a workbook then creating a copy under a new file name, but whenI go to open the new file, it says it is corrupted. No idea why. Any help would be greatly appreciated.

Sub VEDC()
    Dim wrkMyWorkBook As Workbook
    If Dir(Sheets("Sheet1").Range("B15").Value, vbDirectory) = vbNullString Then
        MsgBox "The full path of """ & Sheets("Sheet1").Range("B15").Value & """ doesn't exist!!"
        Exit Sub
    End If
    On Error Resume Next 'Ignore any errors (i.e. if workbook is not open)
        Set wrkMyWorkBook = Workbooks(Sheets("Sheet1").Range("B15").Value)
    On Error GoTo 0 'Turn error reporting back on
    'If the 'wrkMyWorkBook' variable is Nothing then the workbook is not open, therefore we'll...
    If wrkMyWorkBook Is Nothing Then
        '...open it
        Set wrkMyWorkBook = Workbooks.Open(Filename:=Sheets("Sheet1").Range("B15").Value)
    End If
    'Application.Wait (Now + TimeValue("0:0:30"))
    
    ActiveWorkbook.SaveAs Filename:=Workbooks("Daily Reports - Copy.xlsm").Worksheets("Sheet1").Range("B16").Value, FileFormat:=xlWorkbookNormal
   'Set wrkMyWorkBook = ActiveWorkbook.SaveAs(Filename:=Sheets("Sheet1").Range("B16").Value)
    
End Sub

 

[Dan_W]

Hi
Looking at the code, it seems to be trying to open the file that is referenced in cell B15, and then saving with the filename/path at B16. Is that right? If so, perhaps you could try reverting to what I assume was the original code, but which is now commented out / in green, to see if that works? So, change the last two lines before `End Sub` to:

   ' ActiveWorkbook.SaveAs Filename:=Workbooks("Daily Reports - Copy.xlsm").Worksheets("Sheet1").Range("B16").Value, FileFormat:=xlWorkbookNormal
   wrkMyWorkBook.SaveAs Filename:=Sheets("Sheet1").Range("B16").Value

If that doesn't solve it, can you tell me what error code Excel presents you with, and exactly what it says. Also, what is the value (i.e., the filepath/name) written in cell B16?
Thanks.

 

[anichols]

Thank you for your response. That ultimately didn't work.
When I tried that code as instructed, the file does get created, but uses the cell reference in the new file to name, then gets saved to a local folder (though that workbook is functional)

When I try using the first line and commenting out the second, the file gets saved and renamed perfectly and the new file remains open. Once I close it and reopen it from the folder, it displays as "Excel cannot open the file 'daily report.xlsx' because the file format or file extension is not valid. Verify that the file has not been corrupted and that the file extension matches the format of the file."

When I try using both I get both the corrupted file and the file with the wrong name saved locally, although this time saved in a 97-03 excel format...
This is B16. though a formula behind this cell changes the path regularly.

P:\Servicing - 2. Bank Specific\VEDC - 105\5. Reporting\c. Daily Reports\2021\2021.03\03.18.2021 Daily Report.xlsx

Really not sure why I'm having issues. All I am really trying to do is take one file and make a copy in a new location with a new name based on my cell references.

 

[RoryA]

Try:

ActiveWorkbook.SaveAs Filename:=Workbooks("Daily Reports - Copy.xlsm").Worksheets("Sheet1").Range("B16").Value, FileFormat:=xlOpenXMLWorkbook

 

[anichols]

That worked brilliantly! Out of curiosity, what's the difference between XML workbook and XL normal?

 

[RoryA]

The Normal option uses whatever is set as the default file format in your save options. It would seem that that is the old .xls format but your save name includes the .xlsx extension, hence the mismatch.