Posts by Greg

Greg · Jan 5th 2016

Re: Data types when passing Arrays or Ranges

Excellent
many thanks, Mike.

Greg · Jan 5th 2016

hello,

I have the below code that works for some reason. it deletes columns in RngHeaders by treating Rng2Del (another range) as an array and matching against RngHeaders.

Code

Sub doubletest()
Dim Rng2Del, rngHeaders


rngHeaders = Cells(1).CurrentRegion.Rows(1)
Rng2Del = Range("rngCol2Del")


For i = UBound(Rng2Del, 1) To 1 Step -1
    If Not IsError(Application.Match(Rng2Del(i, 1), rngHeaders, 0)) Then
        Columns(Application.Match(Rng2Del(i, 1), rngHeaders, 0)).Delete
    End If
Next


End Sub

Display More

But I'm getting a Type mismatch when passing it as below:

Code

Sub DeleteColsArr(Rng2Del, rngHeaders)


For i = UBound(Rng2Del, 1) To 1 Step -1
    If Not IsError(Application.Match(Rng2Del(i, 1), rngHeaders, 0)) Then
        Columns(Application.Match(Rng2Del(i, 1), rngHeaders, 0)).Delete
    End If
Next


End Sub

Display More

i understand i can declare Rng2Del as an array and prepopulate in the calling procedure but why the original code does not require any of these manipulations?

Thanks,
Greg

Greg · Jan 4th 2016

Re: Hide Column based on Column Heading name from another Excel Sheet

Quote from KjBox;736659
This time the named range is "DeleteCols"

I removed the merged cells from the header row 1 and replaced with the cells formatted as Center Across Selection. Merged cells are a bad idea at the best of times especially when deleting areas that include them.

When the first column of any category (columns with header "A" in your sample) then the Category header is in that column. The code moves the header value one cell to the right before deleting the column. This cannot be done with merged cells unless they are first unmerged. Using Center Across Selection overcomes this.
Code
Sub Button1_Click()
     
    Dim w, x, y, i As Long, ii As Long
     
    x = Cells(1).CurrentRegion.Rows(1)
    w = [DeleteCols]
     
    Application.ScreenUpdating = False
    Columns.Hidden = False
    For i = UBound(w, 1) - 1 To 1 Step -1
        If Not IsError(Application.Match(w(i, 1), x, 0)) Then
            Columns(Application.Match(w(i, 1), x, 0)).Resize(, 4).Delete
        End If
    Next
    For ii = 1 To UBound(w, 1)
        y = Cells(1).CurrentRegion.Rows(2)
        For i = UBound(y, 2) To 2 Step -1
            If w(ii, 1) Like "Cat*" Then Exit For
            If y(1, i) = w(ii, 1) Then
                If Cells(1, i) <> "" Then Cells(1, i + 1) = Cells(1, i)
                Columns(i).Delete
            End If
        Next
    Next
    Cells(1).CurrentRegion.Rows(1).Borders.Weight = 2
    Application.ScreenUpdating = True
     
End Sub
Display More
Display More

This is just a brilliant piece of code

Greg · Jan 11th 2013

Re: 2010 Outlook VBA - Accessing non-default folders

have you viewed all threads?

this appears on the Dave's HowTo sticky (first thread in this forum) -

http://blogs.technet.com/b/hey…-of-my-outlook-inbox.aspx

Greg · Oct 25th 2010

Re: USD $40.00 Creating a memo in Word from Excel

Hi Andrew,

It seems logical to clean up all the bookmarks at the end so i added this code at the bottom of main procedure replacing:

Code

Memo.Save

:

Code

Dim bmarkName As String


With Memo
            
            '.ActiveWindow.View.ShowBookmarks = True  'to see all bookmarks in the document
            
            For I = 1 To .Bookmarks.Count
                bmarkName = .Bookmarks(I).Name
            
                If .Bookmarks.Exists(bmarkName) = True Then
                   .Bookmarks(bmarkName).Delete
                End If
            Next
            .Save
    End With

Display More

It stumbles though after deleting about half of bookmarks. do you have an idea why? (not urgent)

Thanks again,
Greg

Greg · Oct 25th 2010

Re: USD $40.00 Creating a memo in Word from Excel

Hi Andrew,

Thank you for the detailed response. I should have been more precise in my requirements as the users do need more freedom with tables especially table_selection. This is driven by the fact that different products will require various set of headings and number of columns. And the only formatting required for tables are the font (Times New Roman) and gray cell background for headings. This is fine though as i have two ways of doing tables now.

I have not finished my changes and merging two sets together, but I think i have a good handle on it. I consider the work successfully completed and just sent you a payment. I greatly appreciate your very well-thought code, professional approach and timely responses.

Thank you and Best Regards,
Greg

Greg · Oct 24th 2010

Re: USD $40.00 Creating a memo in Word from Excel

Hi Andrew,

That is a lot of coding for tables, so i have a question: Can the following be used to paste a table into word via simple bookmark:

Code

Memo.Bookmarks(BmarkName).Select
 Memo.ActiveWindow.Selection.PasteExcelTable LinkedToExcel:=False, WordFormatting:=False, RTF:=True

The table range in excel can just be controlled using Dynamic Named range as in here
http://www.ozgrid.com/Excel/DynamicRanges.htm

It seemed to work for me, but there might be potential pitfalls that I'm not aware of.

Thanks
Greg

Greg · Oct 23rd 2010

Re: USD $40.00 Creating a memo in Word from Excel

Hi Andrew,

i'm looking at it now, adding fields/bookmarks and changing formulas a bit. I'm also expanding the memo with real data. I think it would be fair and useful for you to see. Is there an email i could send it when completed? i just don't want to publish it here due to sensitivity of data.

In the interim, can you please not do more changes (if you have not yet of course) except tables?

I'm aiming to send it to you by Sunday Morning London time.

Thanks
Greg

Greg · Oct 23rd 2010

Re: USD $40.00 Creating a memo in Word from Excel

Hi Andrew,

looks good. I greatly appreciate the generic solution and all the hard work involved to make it generic.
Unfortunately, I haven't had time to test it thoroughly as I wanted to provide some feedback.

Quote from AndrewMB;527531

The layout of the Fields sheet should not be changed, but the layout of the Inputs sheet can be changed as much as you like as long as the formulas are adjusted appropriately.

Is that still safe to create new fields at the bottom? For instance, as I forgot to add ‘Main Scope’ and ‘Additional Scope’ inputs; can I add two fields Scope1 & Scope2 at the bottom, look them up from Inputs, and create two new bookmarks in memo.docx (they should be called appropriately)? will it will flow through?

Quote from AndrewMB;527531

I have assumed that the memo and questionnaire will be in the same directory, rather than in a specified directory - this makes for a portable solution, but let me know if you don't want this.

Great idea! the path field is redundant then

Quote from AndrewMB;527531

Some specifics about the memo:
• The date doesn't get filled in.
• The ref doesn't get filled in.
• The period ended doesn't get filled in.
• The word "conclusions(s)" shows the letter s yellow, suggesting it should be removed if not applicable. If so, how can I decide whether it should be present?
• I have so far done nothing about [main scope] or [additional scope] as I was not sure where to get these from.
• I have altered the client usage text formula to give correct plurals for the product names. I noticed that the date seems to be built into the formula as a literal, but wondered if this was right. Also the product types and descriptions are only used in constructing the client usage, but the additional product descriptions are not used at all - I wondered if they should be added into the client usage formulas, but they weren't in your formulas.
• The memo header and footer seem to contain some tables which are empty - should these be removed or do they have a purpose?
• At the end of the memo is "more text" in yellow which is not coming from anywhere at present.

Display More

- The date will be today’s date (there is default field for that in Word). The same is true for ref: it will be just file name (no need to use code, I will just insert a field from Quick Parts in Word.
- Can we fill in the measurement date after the period ended? Also, if I ever want to change a date format from say mm/dd/yy to dd-mmm-yyyy, will that format be carried over to Word?
- The word “conclusions” and “more text” at the end should not have any yellow, it was by accident. I was stripping the memo down in a hurry trying at the same time to remove/substitute any company/client sensitive info (here comes Red Fox) as I was about to post in the public domain. My apologies for that.
- Very good handling of the plurals. Thinking about another aspect of it: can we add a check for the words ‘products’ and ‘positions’ in case the client has only one product which is sometimes possible?
It is a good idea to have a separate field (and input) for date when the products were purchased. Again, given that the code is generic do you think I could do it on my own?
- Memo header and footer had our company name, etc., so I removed it. It will be static in the memo.docx.
- “more text” should not be yellow and will be static data.

I’m going to try to test it with real data (no more vacuums) by Sunday morning. That would give much better idea of any gaps.

Thanks again,
Greg

Greg · Oct 22nd 2010

Re: USD $40.00 Creating a memo in Word from Excel

thank you! I should be able to review and write back by the end of the day today.

Greg · Oct 21st 2010

Re: USD $40.00 Creating a memo in Word from Excel

that's perfect. Please take more time if needed. I was hoping to have the entire work done in 7 to 10 days.

Greg · Oct 21st 2010

Re: USD $40 Creating a memo in Word from Excel

Hi Andrew,

Thank you for responding. Your understanding is generally correct. I added a few comments below.

Quote from AndrewMB;527263

Let me check my understanding:
- the data is to be taken from column C of the Inputs sheet and inserted into the fields on the memo where the text highlighted in yellow corresponds to the value in column B of the inputs sheet.

That is correct

Quote from AndrewMB;527263

- the tables on the Tables sheet are to be placed into the corresponding tables in the memo.

the tables should be placed into those spots rather than corresponding tables as the new tables my have different layout.

Quote from AndrewMB;527263

- you would like a 'general' solution which does not rely on the layout of the memo or the names of the fields.

correct. additionally, it would not rely on the layout of the input spreadsheet too much

Quote from AndrewMB;527263

You have the following types of data handling required:
- transfer data as is
- set one of a group of checkboxes
- insert text which can be derived from the inputs using a formula

-checkboxes are already sitting in stripped (default) Word template. just the ability to check one of them based on the input.
-there are also dropdown inputs (via validation) that would trigger different text values to insert

Quote from AndrewMB;527263

I'm not quite clear on the significance of "if left blank omit on the memo". What else might you do?

might not be important, let's touch base on this later.

Quote from AndrewMB;527263

This suggests to me a solution which would involve:
- a memo template where all the placeholders where data is required are indicated by bookmarks (probably easier to use than the yellow text there at present) - the name of the bookmark will correspond directly to the name in Column B.

or custom fields? just a thought - I've never done any VBA with Word so bookmarks might be easier - i don't really know

Quote from AndrewMB;527263

- columns on the Input sheet which codify the type of processing required and the actual text to be inserted (calculated by formula) - similar in information content to what you show in columns D onwards, but more directly transferrable. In some cases some extra rows may also be desirable, e.g. when filling in checkboxes it may be simplest to calculate the value of each checkbox in a separate row.

Column D onwards was more for your purposes. The excel file will go to a group of users who have an unpleasant habit of tempering with everything, so i'd rather have only columns B & C. However, as the word document is a given and excel file is what is being built, obviously there is more flexibility with Excel file. Let's create those if needed, but cannot deviate from memo's formatting, styles, check boxes, etc., even though it does not have the world best design.

Thanks again.
Greg

Greg · Oct 20th 2010

Hello

This task is to automate writing memorandums of work in Word (word memos) based on a questionnaire in Excel. Therefore, it is important that a developer taking on the task would be well versed in Word as well.

I’m attaching both the questionnaire and a sample memo template. No registration is necessary to download these files from the below links – just chose slow (free) download – takes 1 sec

Excel http://rapidshare.com/files/426235611/Questionnaire.xlsx

Word http://rapidshare.com/files/426236066/memo.docx

The user will be filling in the questionnaire, run the code and have a memo ready. The goal is to have it automated as much as possible.

There are the following field types in the questionnaire:

- Static data that just need to be transferred in the respective placeholder(s) in Word
- Boolean or drop down that would call different data or check different checkboxes in Word
- Ranges – two tables in Excel that may have various #rows/cols

The code should be adaptable; that is a user can make own changes to it (e.g., ability to insert another table or section in word)

Also, I would like an ability to use a different word template if needed.

Value: USD $40 payable through paypal. Please let me know if you could do it and approx time frame.

Thanks in advance,
Greg

Greg · Sep 29th 2010

hello,

There is a computer Product ID found by right clicking on computer icon, and going into Properties.

Is there a way to pull via VBA?

Thanks
Greg

Greg · Apr 24th 2006

Re: Date in TextBox: Validation

Yes, thank you very much. It is solved. I used DateValue to convert strings to values and was able to compare.

Greg · Apr 24th 2006

Re: Date in TextBox: Validation

hi Norie,
Thanks for your reply

I'm trying to validate that

1. the actual date is entered in TextBox_expiry
2. the date entered in TextBox_expiry is Not smaller than date which is already in TextBox_today.

I have two if statements for each if you look at my code.
Greg

Greg · Apr 24th 2006

Hi,

I am trying to validate two things for one TextBox and they somehow contradict each other:

Code

Private Sub UserForm_Initialize()
TextBox_today.Value = Date
TextBox_today.Text = Format(TextBox_today.Text, "dd mmm yy")
End Sub


Private Sub TextBox_expiry_BeforeUpdate(ByVal Cancel As MSForms.ReturnBoolean)


    With Me.TextBox_expiry
        If IsDate(.Text) Then
            .Text = Format(.Text, "dd mmm yy")
            Me.Label_expiry.Caption = "expiry as date:"
        Else
            Cancel = True
            Me.Label_expiry.Caption = "date not valid!"
        End If
    
        If .Value < TextBox_today Then
            Cancel = True
            Me.Label_expiry.Caption = "< than today!"
        Else
            Me.Label_expiry.Caption = "expiry as date:"
           
        End If
    
    End With


End Sub

Display More

Could someone help me streamline it a bit?
Also, what other date formats I could use besides "dd mmm yy?"

Thank you,
Greg

Greg · Apr 19th 2006

Re: Incremental progress bar?

Exactly! I had it as Long. Thanks a great deal Tom!

Now, do you know why if I replace the line

Code

Application.StatusBar = Format(PctDone, "#.##%") & " completed"

with

Code

If If PctDone = Round(PctDone / 0.05, 0) * 0.05 Then _
Application.StatusBar = Format(PctDone, "#.##%") & " completed"

it is not working ?

Greg · Apr 19th 2006

Re: Incremental progress bar?

that's what i have

Code

For i = 0 To TextBox_cnt.Value - 1
            Start_cll.Offset(i, 0).Value = lcg.NextNumber
            PctDone = i / (TextBox_cnt.Value - 1)
            Application.StatusBar = Format(PctDone, "#.##%") & " completed"




        Next i

Also, what about the increment?

Greg · Apr 19th 2006

Hi all,

I have a code that has a loop with up to 100,000 possible iterations. The max number of iterations, N, is variable and to be specified by the user via user form.

That's why I like to show only incremental progress , say 5% competed , 10% completed ,... , so on. What is the best way to code it?

Another question: I tried to show it via

Code

Application.StatusBar = Format(PctDone, "#.##%") & " completed"

but for some reason it does not get updated while the code is running. Is that because the user form is visible? For now, I'm trying to add a label progress bar to the user form, but StatusBar would suffice.

Thank you,
Greg