Solved Modify macro to use values in two Excel cells as file name

Microsoft Excel 2010
June 7, 2012 at 09:38:15
Specs: Windows 7, Intel(R) Xeon(R) 2.53 GHz / 48.0 GB
I have a macro from that parses each row from an Excel spreadsheet into a separate word document and saves each document to My Documents as "File-Row #.doc". For example, row 2 is saved as: File2.doc, row 3 as File3.doc, etc. to the last row with data. The macro starts on row 2 to skip the column headers.

I would like to modify the macro to have the file name equal the concatenated values from the first two cells in each row (Columns A and B from row 2 to the last row with data).

An example of a simplified version of the spreadsheet:
1 Date as Text Record # Record Status
2 2012-05-28 APF-2012-1940 Closed
3 2012-05-29 ICM-2012-1987 In Process
4 2012-06-02 PAT-2012-2317 Awaiting Closure

Once the macro is run, the results from the above would be 3 word documents with the file names as below:

Row 2 file name is: 2012-05-28 APF-2012-1940.doc
Row 3 file name is: 2012-05-29 ICM-2012-1987.doc
Row 4 file name is: 2012-06-02 PAT-2012-2317.doc

The macro as it currently exists:

Sub ControlWord()

Dim appWD As Word.Application
Set appWD = CreateObject("Word.Application.8")
appWD.Visible = True

FinalRow = Range("A9999").End(xlUp).Row
For i = 2 To FinalRow
Range("A" & i & ":N" & i).Copy
Range("A" & i & ":N" & i).PasteSpecial Transpose:=False
Range("A" & i & ":N" & i).Copy
appWD.ActiveDocument.SaveAs Filename:="File" & i
Next i
End Sub

I have tried to modify the end of the SaveAs Filename: line to :=Range ("A" & "B" & i), but the macro stops with a Run-time error '13' : Type mismatch.

The debug highlights the appWD.ActiveDocument.SaveAs Filename:=Range "A" & "B" & i line and if I "hover" over the =Range, a popup box says "Filename = <Object variable or With block variable not set>".

As I am a novice in VBA, I can't seem to get the syntax correct.
Any help in modifying this macro to obtain the desired results will be greatly appreciated.

See More: Modify macro to use values in two Excel cells as file name

Report •

June 7, 2012 at 10:19:59
✔ Best Answer
In the future, if you are going to post data or code in this forum, please click on the blue line at the end of this post and read the instructions on how to post example data and VBA code in this forum.

As far as your current issue, this syntax is probably not converting to what you want it to be:

Range ("A" & "B" & i)

For i = 1, this would seen to VBA as:


I would try something like this within the loop:

tmpFilename = Range("A" & i) & " " & Range("B" & i)...
appWD.ActiveDocument.SaveAs Filename:=tmpFilename

This will build the Filename from the values in A1 and B1 with a space in between the 2 values.

You should also be aware that you might run into problems with the dates in Column A.

If VBA picks up the dates as 05/28/2012, then your code will fail because slashes can not be used in a filename.

It will depend on how the default dates are set up on your system.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

Report •

June 7, 2012 at 13:10:20
MANY THANKS! To DerbyDad03

Your suggested code fix worked very well!
I had already addressed the potential date format problem by converting the dates to text. Also, thank you for explaining how to get the data and code to appear correctly. I tried cutting and pasting which did not work. I will use the "blue line" instructions if I need to post future data and code. Thanks again.

Report •

Related Solutions

Ask Question