Solved

Save excel file with VBA code

Posted on 2014-04-28
20
774 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 49

Assisted Solution

by:Rgonzo1971
Rgonzo1971 earned 100 total points
ID: 40026876
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 69

Assisted Solution

by:Qlemo
Qlemo earned 400 total points
ID: 40026896
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
ID: 40029019
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
ScreenConnect 6.0 Free Trial

Check out the updates in one game-changing release, ScreenConnect 6.0, based on partner feedback. New features include a redesigned UI that improves session organization and overall user experience. See the enhancements for yourself!

 
LVL 49

Expert Comment

by:Rgonzo1971
ID: 40029027
HI,

to save as xlsx pls try

ActiveWorkbook.SaveAs strFilename, xlOpenXMLWorkbook

Open in new window

Regards
0
 

Author Comment

by:shaunwingin
ID: 40029035
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
ID: 40029037
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 69

Expert Comment

by:Qlemo
ID: 40029253
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
ID: 40029287
Accepted. pls provide code to do as you suggest.
0
 
LVL 69

Accepted Solution

by:
Qlemo earned 400 total points
ID: 40029785
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
ID: 40031596
tx to all of you.
0
 

Author Comment

by:shaunwingin
ID: 40031810
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 69

Expert Comment

by:Qlemo
ID: 40031885
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
ID: 40031924
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 69

Expert Comment

by:Qlemo
ID: 40039758
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
ID: 40061159
Strange not in mine - how can I set this scale with code?
0
 
LVL 69

Expert Comment

by:Qlemo
ID: 40062566
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
ID: 40062644
tx - need to set a custom zoom -
Tried this but think "60" an issue... any ideas pls?
0
 
LVL 69

Expert Comment

by:Qlemo
ID: 40063293
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
ID: 40063798
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 69

Expert Comment

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

Featured Post

Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
Freeze panes is an option within all variants of Excel to enable parts of a sheet to remain stationary when the cursor is in another part of the sheet. This is a very useful feature which is overlooked or under used.
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.

821 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