Code to prompt for save as 2

[jazminecat23]

Hi all -

I have a spreadsheet that has a command button with the following code. This spreadsheet is used to create monthly reports for various departments in our organization. I have a master report that I used to do this. Currently, I enter the specific department info, calculate the values, file-->save as for the specific dept, then convert all formulae to values and hide the rows and columns used to generate the data. I created a command button that has code in it to convert all the values, hide the necessary rows and columns, and hide the command button itself...but...I need it to FIRST prompt to Save As - because otherwise, the person I'm creating this tool for will end up saving over the master file. She needs to use this over and over again, to generate a whole set of reports, so I'm trying to make it as easy as possible, and avoid losing the master file by having it saved over.

Here's the code for the button now:

    Cells.Select
    Range("A38").Activate
    Selection.Copy
    Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
        False, Transpose:=False
    ActiveWindow.ScrollRow = 1
    Rows("1:8").Select
    Range("A8").Activate
    Selection.EntireRow.Hidden = True
    Columns("A:E").Select
    Range("A9").Activate
    Selection.EntireColumn.Hidden = True
    CommandButton1.Visible = False

Is there code I can put in the beginning of this that will bring up the file-->save as window, and allow the file to be saved, and then continue running the macro?

Thanks in advance!

 

[PHV]

Why not simply use a template (.xlt) ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[jazminecat23]

hm. I hadn't really thought of that. I'll have to see if that's possible - this spreadsheet uses an add-in called Spreadsheet Server, which in turn uses formulae to access data on the AS400 and present it on the spreadsheet. Those formulae live in the first 9 rows, and the first 8 columns. We enter the items specific to each department and calculate, and it gets the data and populates the spreadsheet. Then we save it with a filename specific to each department, and email them out when we're done. (We have to convert the values, because the data won't show up correctly if the users don't have Spreadsheet Server installed.) It's labor intensive, so I'm trying to automate it with some command buttons. I'll try saving the sheet as a template and see if it will still work with Spreadsheet Server. Thanks for the idea!

 

[SkipVought]

Hi,

Does each dept get the same data?

Is there a range of departments and is there a naming convention for the workbooks to be saved as? If there is, there is no need for a SaveAs dialog. Just SaveAs in the code loop.

Skip,

_[red][/red]

 

[jazminecat23]

Hi Skip - thanks for your reply.

Each department gets the same range of data, but the data itself is specific to their department. Basically, the only cells that get changed for each department are G6 and G7. Then we hit calculate, and Spreadsheet Server grabs the data from the AS400. However, the data goes into cells G18 through I36, and each of these cells has a complex formula that includes data from G1-7 and A through D of that cell's row. These formulae are what I need to convert to values, and then do save as so that the master worksheet isn't overwritten. I'm running it now as a template to see if that will work - if so that will deal with the save as issue, and then I will just use this button to hide the necessary rows and columns on the first three sheets of the workbook, and hide the 4th worksheet entirely.

 

[SkipVought]

What you want is a loop that populates G6 and G7. Do you have a range of cells containing these values?

Skip,

_[red][/red]

 

[jazminecat23]

Hi Skip -

I don't have a range of cells containing those values. We haven't quite settled on which divisions will be the test departments for this process.

If I were to have a range of cells with these values, it would be something like this:

Department A

Value 1 Value 2
00001 3300
00001 3400
00001 3500

Department B
Value 1 Value 2
501 2600
501 2710
501 2900

And so on, for at least 50 different values for value 2.

The process is as follows, for each individual pair of value 1 and value 2:

1. value 1 and value2 to gets plugged in,
2. then the sheet calculated (F9),
3. then a button on the Spreadsheet Server toolbar pressed to generate the detail reports,
4. then the rows hidden on the first three worksheets
5. the one worksheet in the middle hidden,
6. the formulae on the first three worksheets converted to values
7. and the file saved with a name indicating the month and value2.

I'm not sure how to this with a loop, since all of these steps must happen for each set of values, and then a separate workbook must be saved for *each set* of values 1 and 2. So basically, we are creating over 50 individual workbooks that get sent out to different people each month.

Can I do this with a loop, or with a separate spreadsheet that has all of the value 1 and 2 on it, and a button to generate all of these workbooks?

 

[SkipVought]

So do dept A & B each get THREE separate reports (workbooks or worksheets) because there are three pairs of values?

Skip,

_[red][/red]

 

[jazminecat23]

Hi Again Skip!

In the example I used, yep. Dept A and B each get three separate workbooks, because each workbook can only have one set of these values, and which are then used to calculate the other 5 worksheets in that workbook. Value 2 actually indicates which group of financial data they are getting. For example, 3300 might be computer equipment, while 3400 could be elections, and 3500 could be a Research Grant. So, for naming, to continue with that same set of sample data, the workbooks would be named as follows for February reports (MMMYYMERVALUE2 - MER for MonthEndReport):

Department A

Value 1 Value 2 Name
00001 3300 FEB07MER3300
00001 3400 FEB07MER3400
00001 3500 FEB07MER3500

Department B
Value 1 Value 2
501 2600 FEB07MER2600
501 2710 FEB07MER2710
501 2900 FEB07MER2900

Some departments have 15 reports, some only have one.
All Value 2 are unique.

I'm going home for the evening, but I will check back later from my home computer and see what ideas you have. Here's the code I have now, commented out for clarity:

Private Sub CommandButton1_Click()
[green]
'
' Converts all formulae to values, hides Spreadsheet Server rows and columns. Hide button after running.

'1. Make sure detail reports are expanded
[/green]

If MsgBox("Have you expanded all detail reports?", vbYesNo, "Expand Detail Reports!") = vbNo Then
Exit Sub
[red]
'if using a template, this isn't needed:
'If MsgBox("Have you saved this with a new name yet?", vbYesNo, "YOU MUST SAVE FIRST!") = vbNo Then
'Exit Sub
[/red]

Else
[green]
'2. Hide all extraneous rows on all worksheets
[/green]

    Rows("1:8").Select
    Range("A8").Activate
    Selection.EntireRow.Hidden = True
    Columns("A:E").Select
    Range("A9").Activate
    Selection.EntireColumn.Hidden = True
 [green]   
    '3. confirm ready to overwrite values
[/green]
        If MsgBox("Do you want to convert all formulas?", vbYesNo, "Remove Formulas?") = vbNo Then
        Exit Sub
        Else
[green]
    '4. overwrite formulae with values
[/green]
        Cells.Select
        Range("A38").Activate
        Selection.Copy
        Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
            False, Transpose:=False
        ActiveWindow.ScrollRow = 1
[green]
    'hide button
[/green]
        CommandButton1.Visible = False
        End If
End If

End Sub

My next step is to either write the code to prompt to save as, so that it can all happen in one button click, or to work on this loop that you're talking about, which would be pretty cool if we could actually generate all of these workbooks and save them, using code.

Thanks for all your help! Have a great weekend if I don't talk to you before Monday.

 

[SkipVought]

Then your list should look like this...
[tt]
Dept Value1 Value2 Name
A 1 3300 FEB07MER3300
A 1 3400 FEB07MER3400
A 1 3500 FEB07MER3500
B 501 2600 FEB07MER2600
B 501 2710 FEB07MER2710
B 501 2900 FEB07MER2900
[/tt]
is the saveas workbook name in this data?

you macro would loop down the list, populating the two values and running the data access, saveas & copy/paste code.

Skip,

_[red][/red]

 

[SkipVought]

Is the Name value...

sName = UCase(Format(SomeDate,"mmmyy")) & "MER" & Value2

Skip,

_[red][/red]

 

[jazminecat23]

Hi Skip -

The only problem I see with this is that, step 3, in my list above, involves the need to click a button on the toolbar and I don't think I can access that using code because it's a proprietary piece of software. So I still think I'm stuck doing each one individually. Although, the shortcut used to click that button is Shift + ctrl + E. I can use that, right?

Oh, and yes, the file name is in the Name column, and yes, you did break it down correctly - so that to could be generated programatically.

Oh, I'm getting very excited about this! I was out yesterday, and have an early meeting this a.m., but I'll be working on this today.

So for the loop, do I need to start with a For Each statement in the code?

Thank you so much for your help!

 

[SkipVought]

You can call the click event that is tied to the button.

Yes. For instance, if your list is in Sheet1, starting in A1, row 1 headings...

dim r as range
for each r in Sheets("Sheet1").Range(Sheets("Sheet1").[A1], Sheets("Sheet1").[A1].End(xldown))
  sDept = r.value
  nVal1 = r.offset(0.1).value
  nVal2 = r.offset(0.2).value
  sName = r.offset(0.3).value
'Sheet2 is where the processing occurs
  with Sheets("Sheet2")
     .Cells(10, "A").value = sDept
     .Cells(10, "B").value = nVal1
     .Cells(10, "C").value = nVal2
  end with
  
  Call CommandButton1_Click

  SaveAs SomePath & "\" sName & ".xls"

next

Skip,

_[red][/red]

 

[jazminecat23]

Hi Skip - ok, I'll have time this afternoon to look at this a little closer. Do I put this loop code in the button as well then? Such that the entire thing is triggered in the onclick event of the button? Not sure how else to trigger the code to run?

Thanks for your help!

 

[jazminecat23]

Oh, and to complicate matters....

There is one sheet in this workbook which is needed by Spreadsheet Server to generate the last two sheets, which are detail reports. I'd like to hide that sheet in each final workbook. So I'm assuming that what I need to do is put a hide, and and unhide, in the code. I could probably just put all of this in the same function, right - just put the whole thing in the on click event of my button, and have that run through the loops and everything? That way when it restarts at each cycle of the loop, I can just have it unhide that sheet, and after generating the detail reports, hide it before the save command?

 

[SkipVought]

Turn on the macro recorder and do it. The observe the recorded code and modify as needed. Post back if necessary.

Skip,

_[red][/red]

 

[jazminecat23]

I just posted a new question about this, as I wasn't sure if I should keep expanding the scope of this thread. But, the short of it is, that while I can hide and unhide the sheet just fine now, I can't generate the detail reports with VBA. That was step 3 in my list above. It involves clicking a button on the Spreadsheet Server toobar, or selecting a menu item from their menu in Excel. Recording a macro of either of these actions comes up empty. I have tried recording a macro of clicking the button on the Spreadsheet Server toolbar, as well as recording a macro of navigating to the menu item on the Spreadsheet Server menu across the top of Excel. Both come up empty macros. I did note that the same thing happens if I try to record a macro of selecting an item from the AdobePDF menu too. Is there a way to access proprietary menus in excel/vba? Am I asking too much? Thanks for all your help. If I can figure out this bit, I think I'm almost there.

Thanks for all your help. the rest is coming along great, but this one piece will really make the difference between doing it all with one button or having to click this toolbar button, and then continue with the rest of the process listed in my 4th post above, for all 50 sheets that we're going to be creating each month.