Solved

working with excel .saveas filename

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

Networking for the Cloud Era

Join Microsoft and Riverbed for a discussion and demonstration of enhancements to SteelConnect:
-One-click orchestration and cloud connectivity in Azure environments
-Tight integration of SD-WAN and WAN optimization capabilities
-Scalability and resiliency equal to a data center

Question has a verified solution.

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

Approximate matching with VLOOKUP and MATCH seems to me to be a greatly under-used technique, and one which is vital for getting good performance out of large lookups. Until recently I would always have advised using an exact match for simplicity an…
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…
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

789 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