VBA For Save As Code Query

[ForumMember]

Hi everyone,

I found some code on this site which should enable me to close my workbook - with my workbook automatically saving the file as a combination of 2 cell values (.xls)

Of course, it does nothing or the sort - but I don't know why?

Any ideas anyone?

Dim strSaveLocation As String
Dim strSaveName As String
strSaveLocation = "E:\"
strSaveName = Range("TestFile") & "H5" & "H3" & ".xls"
ActiveWorkbook.SaveAs strSaveLocation & strSaveName

 

[ForumMember]

Any takers?

 

[xenou]

The code you posted doesn't *close* anything ...

Assuming the string you created with your concatenation is a valid file name and the save operation is successful, it will save activeworkbook with a (new?) name.

 

[ForumMember]

The code you posted doesn't *close* anything ...

Assuming the string you created with your concatenation is a valid file name and the save operation is successful, it will save activeworkbook with a (new?) name.

Hi there,

Thanks for replying! No the code is supposed to automatically save the file as format:


"TestFile" & "H5" & "H3" & ".xls"
</pre>
This should happen when the workbook is closed by the user. Basically the user completes the data in the sheet and simply closes. Once the user has clicked the "x" to close, a new file is created with the above file format and added to an existing folder.

I knoiw the code is incorrect, but I can't figure out how to correct?

 

[xenou]

Is that meant to be a copy of the file the user is using? What happens to the file that the user is using (you'll end up with two files). Should both be saved? Just one? Is the first a template? Should it be deleted?

I can't really tell looking at the non-working code what your intention is!

ξ

 

[ForumMember]

I appreciate it must be frustrating working with imperfect knowledge - and for that I apologise.

Let me explain in detail what I would ideally like:

• User opens template.
• User completed fields in template.
• User simply clicks the close "x" in top right corner.
• A copy of the template is saved to a specific folder "e:\Correction Notes\"
• Each copy is allocated a save name dependent on three cell values.
• Ideally I would like the copy to be saved as PDF (purely to print and archive) - although I'm not sure if this is possible.

I have been playing around some more and have got as far as the following:

Private Sub Workbook_BeforeClose(Cancel As Boolean)
ActiveWorkbook.SaveAs "E:\Correction Notes\" & Range("I6").Value & " - " & Range("I3").Value & " - " & Range("I4").Value & ".xlsm"
End Sub

The above does work to an extent, but:

• If I open a copy to view it and then close that copy, it attempts to replicate itself as the copy is an "Active" workbook with the same SaveAs code.
• The 3 cell reference is "Customer", "Date", "Time" which is messy when it comes to filenames - but I can't think of another way to generate a unique ID for each copy.

Ideally, each copy would have the file format "Customer - dd.mm.yy -1" - and where the suffix "1" had already been used, the next number up would be used.

So in the unlikely event of three logs inone day on one account, we would have:

Account - 15.09.2011 - 1.pdf
Account - 15.09.2011 - 2.pdf
Account - 15.09.2011 - 3.pdf

So yes, anything close to the above would be fantastic! I hope that has clarified and apologies once again for previous ambiguity!

 

[ForumMember]

Friendly Nudge

 

[xenou]

This is a tough one for me.

I don't really have a great liking for the way we are copying workbooks automatically. What if the user makes a mistake and just closes the workbook? Willy-nilly they'll be saving a workbook with bad information in it. And then you've already pointed out that you can never open the workbook again without creating more copies. I'm not a big fan of saving numerous workbooks anyway. It would be better to centralize the data more tightly - in a database, or on one sheet in one workbook.

If it were me, I'd put a command button on the sheet. The user clicks it to save the data. After confirmation of the action, it is saved in a centralized data store (whatever that is ... if you want to stick with this layout, then it saves a *copy* of the workbook with the proper data in it where you want it). That code should not be in a close event but only triggered by clicking the command button.