Solved Excel to word

Microsoft Office 2003 basic w/ sp2
January 25, 2010 at 02:24:30
Specs: win server 2003
I produce an excel sheet for vehicles entering an area with date, time arrival,veh reg,name etc and then manually write out a gate pass with all the information that I have just typed in to the excel sheet. Can you tell me of a way to auto transfer this info on to the gate pass so that it auto prints off and I can hand it to the driver? PS I have never use macro before and a bit of a newbe in this area.

See More: Excel to word

Report •


✔ Best Answer
January 25, 2010 at 07:27:49
Another option would be to "recreate" the Gate Pass in Excel by formatting cells, using Text Boxes, etc.

Assuming you are saving the inputted data for archiving/security purposes, we could help you create a macro that simply copies data from the input sheet to another sheet which is formatted as a parking pass.

One reason I suggest this is so that future upgrades/modifications could be done within a single workbook instead of having to maintain a Word document and an Excel document.

With simple code such as:

Sheets("ParkingPass").Range("C5") = Sheets("InputData").Range("D3")

there would be less danger of broken links, communication issues between the 2 apps etc.

Let us know if that is of any interest to you.



#1
January 25, 2010 at 02:36:16
Why not import the Data set into Access? Then you could create a form based on your information and print it.

LIR


Report •

#2
January 25, 2010 at 02:42:04
Could do that but would perfer to use the word document that has already been typed up. Not sure whether anyone here is up to speed with access.

Report •

#3
January 25, 2010 at 06:48:55
Hi,

You would need a very short macro in Excel to pull the last entry and place it in a fixed location on the worksheet, then Word uses links to this fixed data.

If the last record was transferred by a macro to say Cells AA1 to AJ1,
Just copy each cell from AA1 to AJ1 in turn, flip to Word, and Paste Special - and select Paste Link and Unformatted Text at the place required for each data element.

If this sounds like something you want to try I can have a more detailed look for you. Include the full number of data elements stored and the column letters used.

Also is there any semi-fixed data, such as Gate Operator's name (fixed for duration of shift).

Regards


Report •

Related Solutions

#4
January 25, 2010 at 07:27:49
✔ Best Answer
Another option would be to "recreate" the Gate Pass in Excel by formatting cells, using Text Boxes, etc.

Assuming you are saving the inputted data for archiving/security purposes, we could help you create a macro that simply copies data from the input sheet to another sheet which is formatted as a parking pass.

One reason I suggest this is so that future upgrades/modifications could be done within a single workbook instead of having to maintain a Word document and an Excel document.

With simple code such as:

Sheets("ParkingPass").Range("C5") = Sheets("InputData").Range("D3")

there would be less danger of broken links, communication issues between the 2 apps etc.

Let us know if that is of any interest to you.


Report •

#5
January 26, 2010 at 06:21:07
Thank you all for the very positive replies. Most helpful. If I can come back to you both in a couple of days that would be great

Report •

#6
January 29, 2010 at 21:13:43
Humar/Derbydad03
We like your ideas very much

Semi fixed data will be date at Col A, Row 9
Headings on row 11 Col A - M
Data on Row 12 Col A - M
Data elements (Not too sure what you mean) Could you clarify.
Work in hand to see if we can convert the gate pass to excel as well.


Report •

#7
February 2, 2010 at 05:57:05
Hi,

You say that the data is on Row 12 columns A to M.

I thought that the data was added to the spreadsheet, so that the spreadsheet contained all the records.

So if headings are on row 11
Then records start at row 12 and subsequent records are on rows 13, 14 15 etc.

Can you explain how the records are stored?

Regards


Report •

#8
February 2, 2010 at 09:25:26
Sorry. You are correct - Heading on row 11 and records start on row 12 onwards. Records are stored on the excel sheet after after each vehicle has been booked in. i.e once row fully completed it is then saved. Then the next vehicle is booked in.
Hope this explains every thing.

Thanks


Report •

#9
February 2, 2010 at 09:34:55
How goes the conversion to an Excel based parking pass?

Report •

#10
February 2, 2010 at 13:26:19
Hi,

I have a macro which you attach to a command button on the Excel 'Gate Pass' worksheet.

Clicking this button finds the last entry and copies that entry from column A to column M inclusive
It pastes the cell values into cells AB2 to AN2
It then copies the semi-fixed date information in cell A9 to cell AA2
(AB1 to AN1 has the same headings as row 11 columns A to M, and has 'Date' in AA1).

The Pass - a document in MS Word named "Gate Pass.doc" for this example, contains text and links to the data in cells AA2 to AN2

To link the data, copy cell AA2
Goto the Word document and Paste Special - Paste Link in the required position. Various options are available - I think that Unformatted or RTF formatted are OK.
Repeat for each cell in the range AA2 to AN2

Save the Word document - if you have a different name to "Gate Pass.doc", change the name in the macro.

The Macro copies the last record, then it tells Word to update the fields, and then print Page 1.

The Word document must be open - (you could add some code to open it if it was not already open).

After printing the Pass, the cursor in the spreadsheet is moved to the first cell in the next empty row, ready for the next vehicle.

Finally the updated workbook is saved automatically.

Here is how to create a command button and add the code:
On the Gate Pass Worksheet, create a command button from the Control Toolbox toolbar.
(If this isn't visible, right click on an existing toolbar and check the Control Toolbox).
Select the button Icon and draw a button
Right-click the button and select Command Button - Edit and change the name to 'Print Pass'
Right-click the button again and select View Code
In the code window that opens enter this:

Option Explicit
Private Sub CommandButton1_Click()
Dim rngLast As Range
Dim appWord As Word.Application

'create the link to MS Word
Set appWord = Word.Application

On Error GoTo ErrHnd

With ActiveSheet
    'find last entry
    Set rngLast = .Range("A65534").End(xlUp)
    'adjust range to columns A to M and Copy the new range
    rngLast.Resize(1, 13).Copy
    'paste values and formats to fixed location
    .Range("AB2").PasteSpecial Paste:=xlPasteValuesAndNumberFormats
    'now copy the semi-fixed data
    .Range("A9").Copy
    .Range("AA2").PasteSpecial Paste:=xlPasteValuesAndNumberFormats
End With

'control Word document
With appWord.Documents("Gate Pass.doc")
    .Fields().Update
    .PrintOut Range:=wdPrintFromTo, From:="1", To:="1"
End With

'put cursor onto next line
Application.CutCopyMode = False
rngLast.Offset(1, 0).Select

'save the updated worksheet
ActiveWorkbook.Save

Exit Sub

'error handler
ErrHnd:
Err.Clear
End Sub

Note that Private Sub CommandButton1_Click() and End sub will already be present, so don't duplicate them. Option explicit goes before Private Sub CommandButton1_Click().

Click Save from the Visual Basic Menu.

Alt+f11 takes you back to the main Excel window.
Exit design mode (first icon on the Controls Toolbox toolbar).

Click the 'Print Pass' button.

Note that the way that this code finds the last entry, is by searching upwards in column A, so there must not be any data in column A below the last record.
Printing: the default printer is used. Another named printer could be used by adding some more information to the print command.

Regards


Report •

#11
February 3, 2010 at 05:11:03
Hi,

I forgot one step - a Reference to Word.

In the VB window, goto Tools - References ...
In the 'References - VBA Project' dialog box, find 'Microsoft Word 11.0 Object Library' and check the check box, click OK

(If you select Microsoft Word 11.0 this will work for Word 2003 and later. If you are using an older version of Word look for an earlier version number. Also 12.0 will only work with Word 2007).

Regards


Report •

#12
February 6, 2010 at 12:16:23
Thanks for all of that. We are now working on it. Will let you know if we get it all sorted.
Thankyou very much for your expert help.

Report •


Ask Question