• WSCPD-CB

    WSCPD-CB

    @wscpd-cb

    Viewing 15 replies - 1 through 15 (of 67 total)
    Author
    Replies
    • in reply to: Summing based on Filters – Excel 2007 #1394769

      what i am after, is that if I filter on a date range, eg February, then I would like to know how many nights were billable, non-billable, and billable special.

    • in reply to: Macro to generate bookmarks for reports #1309587

      Paul,

      WIth my understanding of word that sounds great. I am however having trouble implementing the solution. I now realise that I probably have not given sufficient information how the solution, in my mind, would work. After writing the paragraph, or document, I would go through the document and identify the ‘Recommendation’, highlight the ‘recommendation’ text that I want duplicated in summary section and then hit a ‘whiz bang macro button’ which will produce the magic that will enable the cross referencing in the summary section.

      Below is an example of a macro that was generated to do a similar function for abbreviations in the report …. but I dont have the talent to modify it and ideally i would like this functionality for a report I am writing overnight. I dont mind a SEC field or a bookmarking function….I am easily pleased 🙂 Once again, apologies for my lack of macro karma.

      Code:
      Public Sub MakeREC()
      Debug.Print Now(), "CompanyGlobal.MakeABR"
      On Error GoTo MakeABR_Err:
         
      Dim rngThis As Range, rngE As Range, rngA As Range
      Dim lStart As Long, lEnd As Long, ret As Long
      Dim bmNum As Long
      Dim strText As String, strMsg As String, strTitle As String, strParen As String
      Dim bmAName As String, bmEName As String
      Dim bmA As Bookmark, bmE As Bookmark, bmThis As Bookmark
       
         
      ' Routine to make bookmarks for the abbreviation list
       
      ' takes a selection including the full text followed by the abbreviation in parentheses
      ' extracts the text before the parentheses and marks that with the next available eNN bookmark
      ' then marks the text in the partenthese with the corresponding aNN bookmark.
       
          Set rngThis = Selection.Range
         
          'First check that there are no bookmarks already in the selection
          If rngThis.Bookmarks.Count > 1 Then
              'There are already bookmarks in here - check if they are to be kept, replaced or quit
              strMsg = "There are already bookmark(s) in the selection:" & vbCr & _
                       rngThis.Text & vbCr & _
                       "Do you want to add more?" & vbCr & _
                       "Choose Yes to Keep the bookmarks and add new ones for this Explanation and Abbr." & vbCr & _
                       "Choose No to Replace the bookmarks with new ones for this Explanation and Abbr." & vbCr & _
                       "Choose Cancel to quit."
                      
              strTitle = "Abbreviation Capture"
              ret = MsgBox(strMsg, vbDefaultButton1 + vbYesNoCancel, strTitle)
              Select Case ret
              Case vbYes
                  'Do Nothing
              Case vbNo
                  For Each bmThis In rngThis.Bookmarks
                      bmThis.Delete
                  Next bmThis
              Case vbCancel
                  Exit Sub
              End Select
             
          End If
         
          'check for parentheses
          strText = rngThis.Text
          strParen = "("
          lStart = InStr(strText, strParen)
          strParen = ")"
          lEnd = InStr(strText, strParen)
          Debug.Print strText, lStart, lEnd
         
          If lStart > 0 And lEnd > 0 Then
              'We have found the abbr
             
              'Find the next available bookmark number
              bmNum = 1
             
              'Make the BM names
              bmAName = "a" & Trim(Format(bmNum, "00000"))
              bmEName = "e" & Trim(Format(bmNum, "00000"))
             
              While ActiveDocument.Bookmarks.Exists(bmAName) Or ActiveDocument.Bookmarks.Exists(bmEName)
                  bmNum = bmNum + 1
                  bmAName = "a" & Trim(Format(bmNum, "00000"))
                  bmEName = "e" & Trim(Format(bmNum, "00000"))
              Wend
             
              'make the ranges
              If lStart > 1 Then
                  'The parentheses are not at the start of the line - assume explanation is
                  Set rngE = ActiveDocument.Range(Start:=rngThis.Start, End:=rngThis.Start + lStart - 1)
                  Set rngA = ActiveDocument.Range(Start:=rngThis.Start + lStart, End:=rngThis.Start + lEnd - 1)
              Else
                  'The parentheses are at the start of the line so make this the abbr
                  Set rngA = ActiveDocument.Range(Start:=rngThis.Start + lStart, End:=rngThis.Start + lEnd - 1)
                  Set rngE = ActiveDocument.Range(Start:=rngThis.Start + lEnd, End:=rngThis.End)
                 
              End If
             
              'Add the bookmarks
              Set bmE = ActiveDocument.Bookmarks.Add(bmEName, rngE)
              Set bmA = ActiveDocument.Bookmarks.Add(bmAName, rngA)
             
          Else
              'No opening and closing paren - so message
              strMsg = "There is no set of parentheses () in the the selection." & vbCr & _
                           "Please make a selection including the full text, and the abbr in parentheses()"
              strTitle = "Abbreviation Capture"
              MsgBox strMsg, vbOKOnly, strTitle
              Exit Sub
             
          End If
      Exit Sub
       
      MakeABR_Err:
          Debug.Print "MakeABR_Err:", Err.Number, Err.Description, Err.Source
          Resume Next
         
      End Sub
    • in reply to: Macro to generate bookmarks for reports #1309582

      Hi Paul,

      Thanks for your review. The reports that I generate follow a rather strict format style (called a seven part paragraph) so the conclusion and recommendation are inbedded in the ‘one paragraph’ with the other ‘5 parts’. So I understand your proposed solution, and I would even know how to do that myself :rolleyes:, but it not applicable for my particular problem.

      Cheers

      Claude

    • in reply to: Multiple Category macro #1175288

      You can add a category to the already assigned categories as follows:

      Code:
      If collSelItems(lngC).Categories = "" Then
        collSelItems(lngC).Categories = "my category"
      Else
        collSelItems(lngC).Categories = collSelItems(lngC).Categories & ", my category"
      End If

      Hi Hans,

      See the code below…I hope I have interpreted your instructions correctly? Unfortunately, I get an ‘run time 9 , subscript out of range’ error when I try to run the macro:

      Sub SetCategoryP0429()
      Dim collSelItems As Collection
      Dim lngC As Long
      Set collSelItems = GetSelectedItems
      If collSelItems(lngC).Categories = “” Then
      collSelItems(lngC).Categories = “P0429”
      Else
      collSelItems(lngC).Categories = collSelItems(lngC).Categories & “, P0429”
      End If

      Set collSelItems = Nothing
      End Sub

      Can you advise how to fix this?

      Cheers,

      Claude

    • in reply to: Multiple Category macro #1175219

      You can specify multiple category as a list separated by comma+space:

      collSelItems(lngC).Categories = “category-topic, category-file”

      Hi Hans, Thanks for your answer. I was wondering if there is a method of ‘adding’ to a previously assigned category rather than doing multiple categories as you have presented above. Apologies if I wasn’t clear in my earlier request.

      Thanks – Claude

    • in reply to: Paste and Text Wrap (2003 SP3) #1173790

      Set up a workbook exactly the way you want all new workbooks to look.
      Save it as an Excel template named Book.xlt (this name is obligatory) in your Excel start folder, usually C:Documents and SettingsApplication DataMicrosoftExcelXLSTART. This template will be used for new workbooks if you do not explicitely create it from another template.
      Then delete all sheets except one, and save as an Excel template named Sheet.xlt in the same folder. Again, the name is obligatory.
      This template will be used when you select Insert | Worksheet.

      Thanks Hans – thats great info

    • in reply to: Paste and Text Wrap (2003 SP3) #1173705

      You can select a range of cells or even a whole sheet, and tick the Wrap Text check box in the Alignment tab of Format | Cells…
      Another option is to turn on Wrap Text in the Normal style – the style that is applied to all cells by default until the user selects another style.

      Hi Hans,

      excuse my ignorance, but how can you set your normal style for all new workbooks, ie as you can for normal.dot in word?

    • in reply to: Year View? (2003) #1127570

      Hi I have been trying to solve the same problem – getting a year view that I could plot on a large A1 plotter. Anyway, a couple years back I got a trial third party add-in from Planet Software Pty Ltd (Adelaide Australia) but I couldn’t get it to work after an hour or so, so gave up (I am not an IT superstar…so it could just be me or my computer configuration).

      2005 contact details
      Planet Software Pty Ltd
      Phone: +61 8 8267 6199
      Mobile: 0418 693 284
      mailto:gsmith@planetsoftware.com.au

      Anyway, do a search for planet software in Australia and you may have better luck – let me know how it goes.

      Cheers,

      Claude

    • Hi Alan, thanks for taking the time to respond to me. The rename utility look good, but I did not install it due to the warning (on the same site) that the program cannot be fully removed??? Thanks again.

    • Thanks Batcher, copied the file and it worked well. I will use this at one of my work sites where they dont let me install programs on the system, ie tweak. THanks again

    • Thanks – this worked a treat and I found some other useful tools.

    • in reply to: Macro to Assign Category (2003) #1043946

      Thanks all for helping me out. The code now works, however I am a little confused what the code adds. I tried selecting multiple items in the explorer pane to see if set categories to multiple items (ie what I understood from John’s “…of all selected items in explorer pane..”) but it still only adjusted one email item. To my understanding, this is the same function as the previous code.

      I am interested to understand what the code does/add when you have time, and helped out a few other people with potentially higher priority problems…..

      Thanks again.

    • in reply to: Macro to Assign Category (2003) #1043941

      Edited by HansV to crop huge screenshot to show only the relevant part

      Thanks John, the code sound intriguing (for someone who likes tools but does not understand VBA!). I tried to establish a test case but came up with an error (refer to attached).

      Can you please give me any hints on how I may have incorporated it incorrectly. Thanks

    • in reply to: Macro to Assign Category (2003) #1043843

      Hans – the advice that I have to have the email ‘open’ was the trick – thankyou. Do you know if there is a way to assign categories without having the mail item actually open, ie assigning a category when you have the mail item highlighted and are reading it in the preview pane?

      Once again thanks for the fast response.

    • in reply to: Macro to Assign Category (2003) #1043841

      Hans,

      FOr some reason the Macro’s dont work anymore. I am a bit confiused because I am sure they worked initially. When I first try to use the macro after logging on outlook asks if I want to ‘enable macros’ which I say ‘yes’. Even, the open categories dialogue box does not open anymore?? I have attached a couple of the macros which I am sure replicate those that you recommended. Any ideas on the problem? Thanks.

      Sub SetCategoryIAC()
      If Inspectors.Count > 0 Then
      ActiveInspector.CurrentItem.Categories = “IAC”
      End If
      End Sub

      Sub ShowCategoriesDialog()
      If Inspectors.Count Then
      With ActiveInspector.CurrentItem
      If .Class = olMail Then .ShowCategoriesDialog
      End With
      End If
      End Sub

    Viewing 15 replies - 1 through 15 (of 67 total)