Check if the workbook has ever been saved

Vange

New Member
Joined
Oct 7, 2014
Messages
3
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
Code:
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:
Code:
If Not wb1.Path = "" Then
 

Excel Facts

What is the fastest way to copy a formula?
If A2:A50000 contain data. Enter a formula in B2. Select B2. Double-click the Fill Handle and Excel will shoot the formula down to B50000.
silly idea, maybe force a application.save as the first thing
 
Upvote 0
You could try like this

Code:
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.
 
Upvote 0
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

Code:
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?
 
Upvote 0
Something like this

Code:
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
 
Upvote 0
Something like this

Code:
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.
 
Upvote 0

Forum statistics

Threads
1,213,506
Messages
6,114,025
Members
448,543
Latest member
MartinLarkin

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