Solved

working with excel .saveas filename

Posted on 2016-09-21
5
46 Views
Last Modified: 2016-09-22
Hi,

I have the fully qualified filename stored in a string called fname.  When I execute the VBA code:
    For Each sh In ActiveWorkbook.Worksheets
       If sh.Name = "Claims_Detail" Then
          sh.Copy
          Call formatit
          MsgBox Fname
          ActiveWorksheet.SaveAs Filename:=Fname, FileFormat:=51
          ActiveWorkbook.Close
          End If
    Next sh

a SaveAs box pops up with book1.xlsx as the name of the file to be saved.  My goal was to have no popup and save it in the path and file defined in fname.  

What am I missing?

Thanks!

--Ben
0
Comment
Question by:Ben Conner
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
5 Comments
 
LVL 33

Expert Comment

by:Norie
ID: 41809787
Ben

ActiveWorksheet.SaveAs should be ActiveWorkbook.SaveAs.
  For Each sh In ActiveWorkbook.Worksheets
       If sh.Name = "Claims_Detail" Then
          sh.Copy
          Call formatit
          MsgBox Fname
          With ActiveWorkbook
              .SaveAs Filename:=Fname, FileFormat:=51
              .Close
          End With
       End If
    Next sh

Open in new window

0
 
LVL 8

Accepted Solution

by:
Koen earned 250 total points
ID: 41809792
you try to save the sheet, and then you close the workbook... since the workbook has changed, excel prompts you to save it.
I always copy the sheet to a new workbook (removing all macros) and save that...

Application.displayalerts = false 'no prompts for overwrite or save upon exit
ActiveSheet.copy
ActiveWorkbook.saveas Filename:=Fname, FileFormat:=51
ActiveWorkbook.Close
0
 
LVL 33

Assisted Solution

by:Rob Henson
Rob Henson earned 250 total points
ID: 41810281
A workbook can only have one sheet with a particular name, therefore there will only be one sheet called "Claims_Detail". As such there is no need to go through all sheets in the workbook to find that sheet, just tell the routine to do what is required to that sheet.

Sheets("Claim_Details").Copy
Call formatit
ActiveWorkbook.saveas Filename:=Fname, FileFormat:=51
ActiveWorkbook.Close

Open in new window

0
 

Author Closing Comment

by:Ben Conner
ID: 41810721
Thanks Guys!  And Rob, thanks for answering the follow-up question before I had a chance to ask it! :)

Eventually I'll get a good handle on VBA.  Much appreciated.

--Ben
0
 
LVL 33

Expert Comment

by:Rob Henson
ID: 41810727
Glad to be of help and can obviously see into the future, maybe I ought to buy a Lotto ticket!!
0

Featured Post

Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

Question has a verified solution.

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

Convert between Excel file formats (.XLS, .XLSX, .XLSM) with/without macro option David Miller (dlmille) Intro Over this past Fall, I've had the opportunity to see several similar requests and have developed a couple related solutions associate…
How to get Spreadsheet Compare 2016 working with the 64 bit version of Office 2016
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.

749 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