Solved

Save excel file with VBA code

Posted on 2014-04-28
20
764 Views
Last Modified: 2014-05-14
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
0
Comment
Question by:shaunwingin
  • 10
  • 8
  • 2
20 Comments
 
LVL 48

Assisted Solution

by:Rgonzo1971
Rgonzo1971 earned 100 total points
Comment Utility
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
 
LVL 68

Assisted Solution

by:Qlemo
Qlemo earned 400 total points
Comment Utility
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
 

Author Comment

by:shaunwingin
Comment Utility
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
 
LVL 48

Expert Comment

by:Rgonzo1971
Comment Utility
HI,

to save as xlsx pls try

ActiveWorkbook.SaveAs strFilename, xlOpenXMLWorkbook

Open in new window

Regards
0
 

Author Comment

by:shaunwingin
Comment Utility
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
 

Author Comment

by:shaunwingin
Comment Utility
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
 
LVL 68

Expert Comment

by:Qlemo
Comment Utility
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
 

Author Comment

by:shaunwingin
Comment Utility
Accepted. pls provide code to do as you suggest.
0
 
LVL 68

Accepted Solution

by:
Qlemo earned 400 total points
Comment Utility
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
 

Author Closing Comment

by:shaunwingin
Comment Utility
tx to all of you.
0
Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 

Author Comment

by:shaunwingin
Comment Utility
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
 
LVL 68

Expert Comment

by:Qlemo
Comment Utility
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
 

Author Comment

by:shaunwingin
Comment Utility
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
 
LVL 68

Expert Comment

by:Qlemo
Comment Utility
Works for my test cases. Both the scale option in printer/page setup and the zoom factor are copied over.
0
 

Author Comment

by:shaunwingin
Comment Utility
Strange not in mine - how can I set this scale with code?
0
 
LVL 68

Expert Comment

by:Qlemo
Comment Utility
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
 

Author Comment

by:shaunwingin
Comment Utility
tx - need to set a custom zoom -
Tried this but think "60" an issue... any ideas pls?
0
 
LVL 68

Expert Comment

by:Qlemo
Comment Utility
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
 

Author Comment

by:shaunwingin
Comment Utility
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
 
LVL 68

Expert Comment

by:Qlemo
Comment Utility
Didn't try whether it's the problem, but just assign a number, no string.
0

Featured Post

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

A little background as to how I came to I design this code: Around 5 years ago I designed an add-in that formatted Excel files to a corporate standard, applying different cell colours and font type depending on whether the cells contained inputs,…
This article is the result of a quest to better understand Task Scheduler 2.0 and all the newer objects available in vbscript in this version over  the limited options we had scripting in Task Scheduler 1.0.  As I started my journey of knowledge I f…
The viewer will learn how to simulate a series of sales calls dependent on a single skill level and learn how to simulate a series of sales calls dependent on two skill levels. Simulating Independent Sales Calls: Enter .75 into cell C2 – “skill leve…
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.

743 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now