Check if the workbook has ever been saved

[Vange]

Hi,

I have followed this forum for a while and found many great solutions to expand my VBA experience. However, I cannot find any solution to the following situation.

I have a macro that downloads a .xlsx file and copy the worksheet to the users activeworkbook. If the user is working in an old file, e.g. in .xls, the function needs to copy all content of the downloaded sheet into a new sheet on the activeworkbook.

To test the filetype I use

If Not wb1.FileFormat = xlExcel12 Then

This works perfectly, except for when the workbook is new and has never been saved. In this situation, the user (internal use) will be working in a .xlsx file, so the function should just copy the sheet directly.

But how do I test whether the file has ever been saved? The above code results in an error when the workbook has never been saved, maybe this can be utilized instead of a seperate if/then test?

I have tried the following without luck:

If Not wb1.Path = "" Then

 

[mole999]

silly idea, maybe force a application.save as the first thing

 

[VoG]

You could try like this

MsgBox ActiveWorkbook.BuiltinDocumentProperties("last save time")

If the file has not been saved this will generate an error so you will need to use an error handler.

 

[Vange]

Thank you both for answering. I have been rather busy since I posted my question, so haven't had time to follow up untill now.

silly idea, maybe force a application.save as the first thing

That would definately be the easiest solution, but I would prefer not having to force save, as the files are sometimes only needed for a very short time and therefore does not need saving.

You could try like this

MsgBox ActiveWorkbook.BuiltinDocumentProperties("last save time")

If the file has not been saved this will generate an error so you will need to use an error handler.

Thank you for the idea. I thought error handling might be the way forward, but I am quite unfamiliar with error handling codes. Do you by any chance have a link with explanation or examples on relevant error handling codes, to guide me in the right direction?

 

[VoG]

Something like this

Sub test()
Dim s As String
On Error GoTo EHandler
s = ActiveWorkbook.BuiltinDocumentProperties("last save time")
MsgBox s
Exit Sub
EHandler:
MsgBox "File not saved"
End Sub

 

[Vange]

Something like this

Sub test()
Dim s As String
On Error GoTo EHandler
s = ActiveWorkbook.BuiltinDocumentProperties("last save time")
MsgBox s
Exit Sub
EHandler:
MsgBox "File not saved"
End Sub

Thank you very much. I will give it a try and see if I can make it work.