Replies

Andrew & Legare

I have quickly tested both methods and they both worked for my needs.

As a side note, for my purposes, the range of data will not have blank cells. I will only have one range in colums A:E and rows 1:variable. Thanks for the quick response to my problem.

Edited by gwhitfield on 19-Jul-01 06:26.

I inherited an “.xla” file form the person that was previously in my position. This add-in contained the following code which added a menu-bar called ‘Attendance’. This works good for our current applications. I would like to expand the functionality of this menu-bar. Can anyone explain or show me how to add a subgroup to the ‘Attendance’ menu-bar?

Sub Auto_Open()
    On Error Resume Next
    windowindex = CommandBars(1).Controls("Window").Index
    
    'Set up new menu
    Set newmenu = CommandBars(1).Controls.Add(Type:=msoControlPopup, _
      Before:=windowindex, temporary:=True)
    newmenu.Caption = "&Attendance"
    
    'set up new menu items
    Set Item1 = CommandBars(1).Controls("Attendance").Controls.Add
    Item1.Caption = "&Add New Employee"
    Item1.OnAction = "StartAdd"
    Item1.BeginGroup = True
    Set Item2 = CommandBars(1).Controls("Attendance").Controls.Add
    Item2.Caption = "&Load Sick Time"
    Item2.OnAction = "SickStart"
    Item2.BeginGroup = True
    Set Item3 = CommandBars(1).Controls("Attendance").Controls.Add
    Item3.Caption = "&Corrective Action"
    Item3.OnAction = "StartCA"
    Item3.BeginGroup = True
End Sub

For example the code above will create a menu-bar called ‘Attendance’ with three entries underneath (Add New Employee, Load Sick Time, and Corrective Action). How can I add a subgroup to the ‘Load Sick Time’ choice. Within the subgroup I need to have 4 entries.

And it did work. Thanks so much for the timely replies.

Legare, I guess I just don’t have the semantics down yet. I am looking to have a new menu added to the menu bar at the top of excel (just like File, Edit, View, etc…). The new menu bar will be called ‘Attendance’. On the Attendance menu bar will be several items to choose from (Add New Employee, Load Sick Time, and Corrective Action). When the user clicks on Attendance|Load Sick Time, I want another menu to ‘pop-up’ (correct term?) to the right of the opened Attendance menu bar. The new menu would then have several items to choose from (item1, item2, and item3). Hopefully this makes better sense.
[indent]

---

WindowIndex = CommandBars(1).Controls(“Venster”).Index

---

[/indent]
Hans, I understand the concept of what you have listed but what is “Venster”? I tried the code that you listed and nothing happened. When I step through (F8) the code and look at the variables, the above line of code shows “invalid procedure call or argument”.

ideas?

John and Hans, great job. I will save for future reference.

Just as an FYI, in this case, I “know” that the averages worksheet already exists, but I wanted to delete it and start over from scratch. With everyone’s help I have been able to accomplish what I needed very simply.

Thanks.

Thanks to all for you responses.

I got Preston’s solution to work. But, could not get Linda’s to work. the txtnumber textbox had a 1 in every detail record.

Thanks for the response Brooke. After sleeping on this, I woke up and remembered the exact thing that you posted. Probably should be careful when it is past my bedtime.

Thanks Hans – worked like a charm.

Bob, Sammy & John,

for your solutions, I tried several of them and they worked great.

BUT, now I have another question.

If the worksheet already has a worksheet named ‘Average’ I know I can do an if statement and then Sheets(“Average”).Delete. Any idea how to get rid of the dialog box that warns about deleting a worksheet is a permanent action. Any way to automatically pass to this dialog box?

OOPS! I meant to attach this sample for anyone to look at.

Musta been

This question is related to a previous thread.

I am looking for process to open multiple files in mulitple directories and print one worksheet from each of the files. I have written the following module to open the excel files, update a date field, then print the appropriate worksheet. The module then closes the file and moves on to the next file. I have attached the code I am using, but am having trouble updating range (e3:h3). This is a merged cell that should contain a date in the mm/dd/yy format. The code below will open the workbook, navigate to the correct worksheet, then printout the worksheet, but NOT update the range (e3:h3). What am I doing wrong?

BTW, don’t know if it makes any difference, but this code is being ran from within Access 97. If this belongs in the Access forum, let me know and I will repost.

Sub P22()
    Dim XLApp As New Excel.Application, strMsg As String
    'strMsg = "What date do you want to use for the Updates page?" & vbCrLf & _
    '    vbCrLf & "(Must be a Sunday," & vbCrLf & "in the following format 'MM/DD/YY')"
    'mstrDate = InputBox(strMsg, "Input Date")

    'Start printing the Update sheet for the Production Assistants
      XLApp.Workbooks.Open _
        "Srvnt01SprodmgrShendersFilesProd_AstAttendance2001SUP#022.XLS"
      XLApp.Range("E3:H3").Select
      XLApp.ActiveCell.FormulaR1C1 = "07/01/01" 'mstrDate
      XLApp.Range("E4").Select
      'XLApp.Workbooks.Application.ActivePrinter = "SRVNT01PRT_SCLR on Ne01:"
      XLApp.Sheets("Update").PrintOut
      XLApp.ActiveWorkbook.Saved = True
      XLApp.ActiveWorkbook.Close
    XLApp.Quit
    Set XLApp = Nothing
End Sub

At first I thought I could use an input box to choose the new value for range (e3:h3), but that didn’t work, so I dimmed those statements out and went with a hardcode date. That didn’t work either

Edited by Charlotte to eliminate horizontal scrolling

Charlotte, that didn’t work. However, it triggered an obscure thought (very rare that this happens). What if I try to refresh the ‘PopUp’ form from in the middle of my other code. This did work.

I listed below what I was trying to do, and highlited in red the line that I added.

Sub P22()
    Dim XLApp As New Excel.Application
    'Start printing the Update sheet for the Production Assistants
    DoCmd.RepaintObject acForm, "frmWait"
    XLApp.Workbooks.Open  _
      "Srvnt01SprodmgrShendersFilesProd_AstAttendance2001SUP#022.XLS"
    XLApp.Sheets("Update").Range("E3").Value = "07/01/01"
    XLApp.Sheets("Update").PrintOut
    XLApp.ActiveWorkbook.Saved = True
    XLApp.ActiveWorkbook.Close
    XLApp.Quit
    Set XLApp = Nothing
End Sub
Function p22a()
    DoCmd.OpenForm "frmWait", acNormal
    Call P22
    DoCmd.Close acForm, "frmWait", acSaveNo
End Function

Thanks again for the quick response and the triggering of my gray matter.

Thanks for your help.
I changed my code as per your recommendations and tested the macro. Same results as before. Then like a bolt of lightening, I was struck with an inspiration. The workbook that I am opening contains several worksheets. When opening the workbook, the “Updates” sheet is not always the active sheet. Therefore, the value that I was trying to place into cell E3 was getting placed on someother worksheet. (Where is the ‘ID 10 T’ smiley when you need it).

I replaced this line of code:

     XLApp.Range("E3").Value = "07/01/01"

with this one:

      XLApp.Sheets("Update").Range("E3").Value = "07/01/01"

and everything works great now. It even worked with putting the input box statement back in.

I am using Access. Have not had a chance to try Mark’s suggestion. Will get to it tomorrow.

I inserted mark’s code and tried the macro. The form comes up and the goes away like it should, however, all you see is an outline of the form. The middle of the form is all white. I have attached what the form looks like when running (bad_pic) and what the form should look like (good_pic) (I don’t know if there is a way to upload two images, so I edited the previous post and attached the good_pic). Any ideas on what I am doing wrong.