I've taken over a workbook and am new to excel and VB. The three images will help tell the story, I started with (1), have managed to get to (2)
Help formatting Excel to Word macro
-
-
-
Re: Help formatting Excel to Word macro
ideally am looking for (3). I need to be able to indent a line with some text and align the rest to the right, insert and align text to a logo and text box.
-
Re: Help formatting Excel to Word macro
Presumably there is existing code and/or workbooks used to generate these pages...? It would be very helpful if they were attached.
-
Re: Help formatting Excel to Word macro
Quote from cytop;616976Presumably there is existing code and/or workbooks used to generate these pages...? It would be very helpful if they were attached.
Yup, sorry about that. This is the non-tweaked by me code that's in the working spreadsheet. My second example is the code I tweaked from a backup.
Code
Display MoreSub MakeRegistrationForms() ' Creates Word document with one sheet for each member ' This assumes the worksheet only contains the members to include in the directory ' ' For some reason, the sort portion of this macro is failing so it is currently ' commented out until I get to try this again on another machine. ' ' In 2010, I separated the about to expire from the rest and printed them on ' yellow to draw attention to them. -MF Application.ScreenUpdating = False Dim WordApp As Object Dim DataOption1 Const wdPageBreak = 7 ' Start Word and create an object Set WordApp = CreateObject("Word.Application") With WordApp .Documents.Add End With ' Determine the file name saveasname = ThisWorkbook.Path & "\" & "RegForms.doc" Dim MemberSheet MemberSheet = "Members" Dim NameCol NameCol = "lastfirst" Dim Address1Col Address1Col = "address1" Dim Address2Col Address2Col = "address2" Dim CityCol CityCol = "city" Dim StateCol StateCol = "state" Dim CodeCol CodeCol = "postalcode" Dim CountryCol CountryCol = "country" Dim RenewCol RenewCol = "notice" Dim Phone1Col Phone1Col = "phone1" Dim Phone2Col Phone2Col = "phone2" Dim Email1Col Email1Col = "email1" Dim Email2Col Email2Col = "email2" Dim TnameCol TnameCol = "trailname" Dim TrailsCol TrailsCol = "trails" Dim GatheringCol GatheringCol = "12Gathering" Dim PDFCol PDFCol = "PDF" ' Select the membership worksheet Sheets(MemberSheet).Select ' Determine the last row and column of the worksheet lr = ActiveSheet.Cells.SpecialCells(xlCellTypeLastCell).Row lc = ActiveSheet.Cells.SpecialCells(xlCellTypeLastCell).Column ' Determine the column numbers for the membership data to be ' included in the Membership Index For i = 1 To lc Select Case Worksheets(MemberSheet).Cells(1, i).Value Case NameCol nc = i Case Address1Col a1c = i Case Address2Col a2c = i Case CityCol cic = i Case StateCol sc = i Case CodeCol zc = i Case CountryCol coc = i Case RenewCol rc = i Case Phone1Col p1c = i Case Phone2Col p2c = i Case Email1Col e1c = i Case Email2Col e2c = i Case TnameCol tnc = i Case TrailsCol tc = i Case GatheringCol gc = i Case PDFCol pdfc = i End Select Next i ' The following sort call worked on Scott's machine but not Benson's. I left it ' in in case it works on another machine. ' Sort the entire spreadsheet by the names column 'Range(Cells(1, 1), Cells(lr, lc)).Sort Key1:=Cells(1, nc), Order1:=xlAscending, _ header:=xlYes, OrderCustom:=1, MatchCase:=False, Orientation:= _ xlTopToBottom, DataOption1:=xlSortNormal ' Loop through all members to generate index information For i = 2 To lr ' trap each piece of worksheet information Name = Cells(i, nc).Value & vbCrLf address1 = Cells(i, a1c).Value address2 = Cells(i, a2c).Value city = Cells(i, cic).Value State = Cells(i, sc).Value zip = Cells(i, zc).Value country = Cells(i, coc).Value renewal = Cells(i, rc).Value phone1 = Cells(i, p1c).Value phone2 = Cells(i, p2c).Value email1 = Cells(i, e1c).Value email2 = Cells(i, e2c).Value tname = Cells(i, tnc).Value trails = Cells(i, tc).Value gathering = Cells(i, gc).Value pdf = Cells(i, pdfc).Value ' Format each line of information for the Word document If address1 <> "" Then Address = address1 If address2 <> "" Then Address = Address & vbCrLf & address2 & vbCrLf Else Address = Address & vbCrLf End If Else Address = vbCrLf & "Address: ______________________________" & vbCrLf End If csz = city & ", " & State & " " & zip If csz = ", " Then csz = vbCrLf & "City, State, Zip: ______________________________" & vbCrLf Else csz = csz & vbCrLf End If If country <> "" Then country = country & vbCrLf Else country = "" & vbCrLf End If renewalnote = "Your membership is " & renewal & "." & vbCrLf & vbCrLf If phone1 <> "" Then phone1 = phone1 & vbCrLf Else phone1 = "Phone: ______________________________" & vbCrLf End If If phone2 <> Empty Then phone2 = phone2 & vbCrLf Else phone2 = "" & vbCrLf End If If email1 <> "" Then email1 = email1 & vbCrLf Else email1 = vbCrLf & "Email: ______________________________" & vbCrLf End If If email2 <> "" Then email2 = email2 & vbCrLf Else email2 = "" & vbCrLf End If dqt = Chr(34) ' double quote symbol If tname <> "" Then tname = dqt & tname & dqt & vbCrLf & vbCrLf Else tname = "_____________________________" & vbCrLf & vbCrLf End If If trails <> "" Then trails = "Trails completed: " & trails & vbCrLf & vbCrLf Else trails = vbCrLf & "Trails completed: _____________________________" & vbCrLf & vbCrLf End If If gathering <> "" Then gathering = " Number of prepaid registrants: " & gathering & vbCrLf Else gathering = " Number of prepaid registrants: 0" & vbCrLf End If If pdf <> "" Then pdf = "Thank you for choosing to receive your newsletters in PDF format. " Else pdf = "_____ Check here to receive your newsletters in PDF format. " End If ' Send commands to Word and write each line of data to file With WordApp With .Selection .TypeParagraph .Font.Name = "Arial" .Font.Size = 14 .Font.Bold = True .ParagraphFormat.Alignment = 1 .typetext Text:="Welcome to the 31st annual ALDHA Gathering!" .TypeParagraph .TypeParagraph .typetext Text:="This is YOUR membership record with ALDHA:" .TypeParagraph .TypeParagraph .Font.Size = 12 .ParagraphFormat.Alignment = 0 .typetext Text:="Is the information below correct? If NOT, please note changes here:" .TypeParagraph .TypeParagraph .Font.Size = 12 .Font.Bold = True .typetext Text:=Name .Font.Bold = False .Font.Size = 11 .typetext Text:=Address .typetext Text:=csz .typetext Text:=country .typetext Text:=phone1 .typetext Text:=phone2 .typetext Text:=email1 .typetext Text:=email2 .typetext Text:=vbCrLf & "Trail name(s): " .Font.Italic = True .typetext Text:=tname .Font.Italic = False .typetext Text:=trails .Font.Bold = True .typetext Text:=renewalnote If renewal <> "PAID THROUGH LIFE" And renewal <> "HONORARY LIFE MEMBER" Then .typetext Text:="Membership renewals " .Font.Bold = False .typetext Text:="are $10 per calendar year per individual or household, or $200 for a lifetime membership." & vbCrLf & vbCrLf .typetext Text:=" Number of years: ________ x $10 per year = $_________" & vbCrLf .typetext Text:=" Lifetime membership is $200 (Does not include yearly Gathering fees.) $_________" & vbCrLf & vbCrLf End If .Font.Bold = True .typetext Text:="Gathering registration " .Font.Bold = False .typetext Text:="is $20.00 per person." & vbCrLf & vbCrLf .typetext Text:=gathering .typetext Text:=" Number of registrants paid for today: ________ x $20 per person = $_________" & vbCrLf & vbCrLf .Font.Bold = True .typetext Text:="Donations " .Font.Bold = False .typetext Text:="to ALDHA, a registered 501(c)3 non-profit organization, are tax deductible." & vbCrLf & vbCrLf .typetext Text:=" Amount of donation $_________" & vbCrLf & vbCrLf .Font.Bold = True .typetext Text:="Total paid to ALDHA today: $_________" & vbCrLf .Font.Bold = False .typetext Text:="Make checks payable to ALDHA." & vbCrLf & vbCrLf .Font.Bold = True .typetext Text:=pdf .Font.Bold = False .typetext Text:="PDF newsletters arrive earlier, in full color, are better for the environment, and help keep ALDHA's expenses down." & vbCrLf & vbCrLf .InsertBreak Type:=wdPageBreak End With End With Next i With WordApp .ActiveDocument.SaveAs Filename:=saveasname .ActiveWindow.Close End With Set WordApp = Nothing Application.ScreenUpdating = True End Sub
-
Re: Help formatting Excel to Word macro
I haven't looked at this yet, will do, but one question first...
Why don't you design a Word document with placeholders or fields and then do a simple replace of the placeholders in the code?
Then you're free to redesign the document with no, or minimal, impact on the code.
-
-
Re: Help formatting Excel to Word macro
Quote from cytop;616985I haven't looked at this yet, will do, but one question first...
Why don't you design a Word document with placeholders or fields and then do a simple replace of the placeholders in the code?
Then you're free to redesign the document with no, or minimal, impact on the code.
Sounds logical, but I have no idea how. I'll Google it to at least get an understanding.
In the third example the one I'm striving for, I basically "reversed engineered" it in Word. It's the 1st page of a 1200 page document the macro produces using the appropriate member information.
-
Re: Help formatting Excel to Word macro
The attachment, for example, is a very simplistic lay-out (some fields only). Fields where data needs to be inserted is marked something like '[FULLNAME]',
You already determine the name so a simple replace of the literal [FULLNAME] with your variable is used to insert data.
This way, you can redesign the document at will. As long as no new fields are added no other changes need to be made. If new fields are added, the code changes are minor.
Also, as this may be output as a PDF you can colour it up a little and 'make it nicer' - no problems with it draining your expensive colour inks. If the document is actually printed the user can select to output as black and white.
My thoughts only. While there's nothing wrong with what you have done, it does seem to be a little limiting, particularly if changes are only being made to the document.
-
Re: Help formatting Excel to Word macro
Ok, I'm beginning to understand but still haven't a clue as how the macro works to fill the place holders. It's almost time to get up and I haven't been to sleep yet. Let me get some rest and I'll be back.
Sincerely thanks for your help and patience.
-
Re: Help formatting Excel to Word macro
I'll sample up a working demo for some fields a little later...
Participate now!
Don’t have an account yet? Register yourself now and be a part of our community!