Solved

working with excel .saveas filename

Posted on 2016-09-21
5
40 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
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 32

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 32

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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Introduction This Article briefly covers methods of calculating the NPV and IRR variants in Excel as well as the limitations in calculating and interpreting IRR results. Paraphrasing Richard Shockley, author of my favourite finance reference tex…
Improved? Move/Copy Add-in Replacement - How to avoid the annoying, “A formula or sheet you want to move or copy contains the name XXX, which already exists on the destination worksheet.” David Miller (dlmille)  It was one of those days… I wa…
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

863 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

25 Experts available now in Live!

Get 1:1 Help Now