Save excel file with VBA code

Say, I wish to save the excel file with VBA code and use the contents of 2 cell for part of the file name.
I also need to check if the specified directory is available before saving as its a remote location and not always available. If not available msgbox to prompt user to see why not available
filename: "Site Sheet" & Sheet"Raw" Cell A1 & " " & Sheet"Raw" Cell A2

To be saved as .xls file

tx
shaunwinginAsked:
Who is Participating?
 
QlemoBatchelor, Developer and EE Topic AdvisorCommented:
Sub OpenAsCopy()
Dim newWb As Workbook, actWb As Workbook
  
  fname = "Site Sheet" & [Raw!A1] & " " & [Raw!A2]
  
  Set actWb = ActiveWorkbook
  Set newWb = Workbooks.Add
  
  ' Delete all but one worksheet (one needs to remain)
  Application.DisplayAlerts = False
  While newWb.Worksheets.Count > 1
    newWb.Worksheets(1).Delete
  Wend
  ' Copy all Sheets, starting from the last one
  For i = actWb.Worksheets.Count To 1 Step -1
    actWb.Worksheets.Item(i).Copy before:=newWb.Worksheets(1)
  Next
  ' Remove the last (empty) sheet
  newWb.Worksheets.Item(newWb.Worksheets.Count).Delete
  
  ' Save
  newWb.SaveAs fname, xlOpenXMLWorkbook
  
  Application.DisplayAlerts = True
End Sub

Open in new window

Make sure this code is in the ThisWorkbook module, and no VBA code is in sheets - the latter would be copied, and might interfere with later safe operations.
Also you might want to add a path to the file name.
0
 
Rgonzo1971Commented:
Hi,

pls try
Sub Macro()
    strFilename = "Site Sheet" & Sheets("Raw").Range("A1") & " " & Sheets("Raw").Range("A2")

    On Error GoTo NotSaved
        
        ActiveWorkbook.SaveAs strFilename, xlExcel8

    Exit Sub
NotSaved:
    MsgBox Err.Description
End Sub

Open in new window

Regards
0
 
QlemoBatchelor, Developer and EE Topic AdvisorCommented:
For building the filename you can also use the much shorter notation
   strFilename = "Site Sheet" & [Raw!A1] & " " & [Raw!A2]

Open in new window

That is so short you even could use the expression directly in the SaveAs.
If the brute-force approach above for "checking" the remote directory doesn't work well because it causes Excel to "hang" for too long we will need a more sophisticated approach, like first pinging the remote server, then checking the folder, and then Save.
0
Cloud Class® Course: MCSA MCSE Windows Server 2012

This course teaches how to install and configure Windows Server 2012 R2.  It is the first step on your path to becoming a Microsoft Certified Solutions Expert (MCSE).

 
shaunwinginAuthor Commented:
Say, I'm having a problem that the save as closes the existing excel file which is a xlsm file. It owuld be better if I can simply Export the file?
How can I save in xlsx format?
0
 
Rgonzo1971Commented:
HI,

to save as xlsx pls try

ActiveWorkbook.SaveAs strFilename, xlOpenXMLWorkbook

Open in new window

Regards
0
 
shaunwinginAuthor Commented:
This causes the existing xlsm to become corrupt!
It also prompts to as regards saving the macros which would need to get rid of.
0
 
shaunwinginAuthor Commented:
Say, I'm having a problem that the save as closes the existing excel file which is a xlsm file. It would be better if I can simply Export the file to xlsx and then open the exported file for the user to view it. Once this file is closed the xlsm file will be in the foreground again and the user can export again.
0
 
QlemoBatchelor, Developer and EE Topic AdvisorCommented:
Doesn't sound like a good idea to do that, and it isn't easy. A much better option is to create a copy of a specific worksheet, and save that as a new file.
To show how complex your current request is:
* we need to create a new, empty workbook
* copy all sheets into that
* save it as a new file without macros
* open that XLSX file.
0
 
shaunwinginAuthor Commented:
Accepted. pls provide code to do as you suggest.
0
 
shaunwinginAuthor Commented:
tx to all of you.
0
 
shaunwinginAuthor Commented:
Say, the saved file has a different Scale setting from the original. How can I set this as the page breaks come out differently now?
0
 
QlemoBatchelor, Developer and EE Topic AdvisorCommented:
The page setup should be copied over with each sheet. The page breaks are not related to the scale: can you show screenshots of the difference?
0
 
shaunwinginAuthor Commented:
See the line between row W and X. If scale is set to 61 then it falls on the page break after row X
Capture.JPG
0
 
QlemoBatchelor, Developer and EE Topic AdvisorCommented:
Works for my test cases. Both the scale option in printer/page setup and the zoom factor are copied over.
0
 
shaunwinginAuthor Commented:
Strange not in mine - how can I set this scale with code?
0
 
QlemoBatchelor, Developer and EE Topic AdvisorCommented:
To copy e.g. the Zoom:
Sub OpenAsCopy()
Dim newWb As Workbook, actWb As Workbook
  
  fname = "Site Sheet" & [Raw!A1] & " " & [Raw!A2]
  
  Set actWb = ActiveWorkbook
  Set newWb = Workbooks.Add
  
  ' Delete all but one worksheet (one needs to remain)
  Application.DisplayAlerts = False
  While newWb.Worksheets.Count > 1
    newWb.Worksheets(1).Delete
  Wend
  ' Copy all Sheets, starting from the last one
  For i = actWb.Worksheets.Count To 1 Step -1
    actWb.Worksheets.Item(i).Copy before:=newWb.Worksheets(1)
    newWb.Worksheets(1).PageSetup.Zoom = actWb.Worksheets(i).PageSetup.Zoom
  Next
  ' Remove the last (empty) sheet
  newWb.Worksheets.Item(newWb.Worksheets.Count).Delete
  
  ' Save
  newWb.SaveAs fname, xlOpenXMLWorkbook
  
  Application.DisplayAlerts = True
End Sub

Open in new window

(there is only one line added). You might have to set other properties of PageSetup, too - just see the VBA help for PageSetup (Object) for more.
0
 
shaunwinginAuthor Commented:
tx - need to set a custom zoom -
Tried this but think "60" an issue... any ideas pls?
0
 
QlemoBatchelor, Developer and EE Topic AdvisorCommented:
No. it isn't an issue. You can set the scale to 60 or whatever value between 10 and 400, and it should get reflected immediately in the printing preview.
0
 
shaunwinginAuthor Commented:
Oops my paste missing:

tx - need to set a custom zoom -
Tried this but think "60" an issue... any ideas pls? My notation "60" seems wroing ...

 Sheets("SiteSheet-Auto").PageSetup.Zoom = "60"
0
 
QlemoBatchelor, Developer and EE Topic AdvisorCommented:
Didn't try whether it's the problem, but just assign a number, no string.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.