Saving with a new filename using a UserForm

XLS Padlock
1

Hi
I am trying to generate a new file which is created by the user using a UserForm where they select which sheets they want copied to the new file. The new file is saved in the same folder as the original file. The code that works in a normal version of Excel is as follow: -
With ThisWorkbook
_ Fnme = Left(.FullName, InStr(.FullName, “.”) - 1) & __
_ "" & Format(Now, “dd mmm yyyy_hh_mm_ss”) & " SUBMISSION" & “.xlsx”
_ End With_
_ _
_ With Wkbk_
_ _
_ .SaveAs __
_ Filename:=Fnme, __
_ FileFormat:=xlOpenXMLWorkbook_
_ .Close_

The code that worked in a XLS Padlock file before the latest update (and which also got over the problem of the virtual drives) was: -

With ThisWorkbook
_ Set xlspadlock = Application.COMAddIns(“GXLSForm.GXLSFormula”).Object_
_ Dim PathtoFile As String_
_ Filename = xlspadlock.PLEvalVAR(“EXEPath”) & Filename_
_ Fnme = Left(.PathtoFile, InStr(.PathtoFile, “.”) - 1) & __
_ "" & Format(Now, “dd mmm yyyy_hh_mm_ss”) & " SUBMISSION" & “.xlsx”
_ End With_
_ _
_ With Wkbk_
_ _
_ .SaveAs __
_ Filename:=Fnme, __
_ FileFormat:=xlOpenXMLWorkbook_
_ .Close_

However, the first code will only save to the AppData\Roaming folder and the second code no longer works at all.

Can anyone help?
Thanks

2

Try to insert a MsgBox to see if the Filename variable is correctly defined in your 2nd code snippet.

3

Hi - I have corrected my code as follows: -

Dim PathtoFile As String
Dim xlspadlock As Object
Dim Fnme As String
Set xlspadlock = Application.COMAddIns(“GXLSForm.GXLSFormula”).Object
PathtoFile = xlspadlock.PLEvalVAR(“EXEPath”)
With ThisWorkbook
Fnme = PathtoFile & Left(ThisWorkbook.Name, InStr(ThisWorkbook.Name, “.”) - 1) & _
"_" & Format(Now, “dd mmm yyyy_hh_mm_ss”) & " SUBMISSION" & ".xlsx"
End With

This all now works with the original workbook name. However, can you tell me if it is possible to use the .xlsc name instead of the original workbook name?

4

Great.
Now if you want to save an XLSC file, it’s only possible if you want to save a copy of your workbook, XLS Padlock won’t crypt additional workbooks that you could have made with VBA for instance.

5

Hi
I am not trying to create a protected workbook using VBA - I just need to reference the .xlsc filename. Basically I have a macro which enables the user to select from the range of open worksheets - these worksheets are then exported to a new file but it is only the formats & values which are copied across. This enables the user to create a ‘smaller’ workbook which can be Emailed but without any of the workings being shown or available to the recipient.
So if there is a way to just recall the .xlsc filename then that would be most helpful.
Thanks

6

We’ll add a way to remember the name of the last XLSC file through VBA. Thanks!

1 Like
7

Oh boy! That will really save me a lot of hassle. Currently, my applications run a lot of procedures in vba. This makes it cranky if you don’t save frequently. I added a Thisworkbook.Save command button, but each time it brings up the XLS padlock SaveAs dialog box. I would rather make my app save invisibly sometimes to clear the memory/cache or at least upon button click for it to continue saving to the current XLSC file.

Please let me know when this version goes live. AMAZING SOFTWARE, @gdgsupport. Can’t say how happy we are!!

Thanks
Dan

8

Hi

Do you have any idea when you are likely to add this feature to your programme? Without it I cannot really use it to protect my spreadsheets so an idea of timing would be very helpful.

I look forward to hearing from you.

Regards

Vince

9

Hi
Have you got any updates on this addition to your programme?
Thanks
Vince

10

New version will be out this week.

11

Dan,

I have code that auto saves when idle every 5 min without bringing up save dialogue box.
let me know if interested.

12

Cool! Sounds great, I’d love to take a look? Are you using OnTime method?

Thanks
Dan

13

Yes.
But anytime the user navigates to a page (worksheet) by clicking on icon - running vba macro, effectively the timer resets for a further 5 minutes

Public RunWhen As Date

Private Sub Save_Required()

    'allows save when idle
Dim TimeSave As Integer
Dim TimeSchedule As Date

TimeSave = Worksheets("Admin").Range("C13")         'This cell records when the last time it was saved
TimeSchedule = Now + TimeSave / 1440

'cancel previous schedule
On Error Resume Next
Application.OnTime EarliestTime:=RunWhen, Procedure:=“Save”, Schedule:=False 'cancels any previous schedule
Application.OnTime EarliestTime:=TimeSchedule, Procedure:=“Save”, Schedule:=True

'Application.OnTime EarliestTime:=Now + TimeSave / 1440, Procedure:="Save", LatestTime:=Now + 10 / 1440, Schedule:=True
'Application.OnTime EarliestTime:=Now + TimeValue("00:TimeSave:00"), Procedure:="Save", Schedule:=True

RunWhen = TimeSchedule

End Sub

Private Sub Go_To_Major_VBA_Process()
Sheets(“Sheetname”).Select
Range(“C4”).Select
Application.Run “Save_Required” 'this will save or reset timer
End Sub

Private Sub Major_VBA_Process()
'Do all your processes first"
Application.Run "Save_Required"
End sub

Cheers

1 Like