Solved

working with excel .saveas filename

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

Courses: Start Training Online With Pros, Today

Brush up on the basics or master the advanced techniques required to earn essential industry certifications, with Courses. Enroll in a course and start learning today. Training topics range from Android App Dev to the Xen Virtualization Platform.

Question has a verified solution.

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

Workbook link problems after copying tabs to a new workbook? David Miller (dlmille) Intro Have you either copied sheets to a new workbook, and after having saved and opened that workbook, you find that there are links back to the original sou…
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 a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.

805 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