?
Solved

working with excel .saveas filename

Posted on 2016-09-21
5
Medium Priority
?
54 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 34

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 1000 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 1000 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

New benefit for Premium Members - Upgrade now!

Ready to get started with anonymous questions today? It's easy! Learn more.

Question has a verified solution.

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

This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
This article describes how you can use Custom Document Properties to store settings and other information in your workbook so that they will be available the next time you open the workbook.
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.

752 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