Appending to Workbook Name

[jo15765]

I am working on a naming convention for orders. When the data is ordered, it is exported to Excel and saved as PartName_OrderDate.xls and is then emailed to the appropriate department. There is a book with Part Codes and the format looks similar to this:
Part Code --- Part Name
123456 Engine
287423 Muffler
983231 Spark Plug

etc etc etc etc

Currently the Part code is manually looked up and keypunched. Is there a way in Excel, if a module was created and the associations were set similar to:

123456 = Engine
287423 = Muffler
983231 = Spark Plug


You could automatically set the Part Number to append to the workbook name as opposed to looking up and keypunching?

[Bob Phillips]

You will need to adapt this to yoyr workbooks



Dim filename As String
Dim partNum As String
Dim PartName As String

filename = "muffler-20120212.xls"
PartName = "muffler"
partNum = Application.Index(Columns(1), Application.Match(PartName, Columns(2), 0))
filename = Left$(filename, InStr(filename, ".") - 1) & "-" & partNum & _
Right$(filename, InStrRev(filename, ".") + 1)

[jo15765]

And judging from this line of code:

partNum = Application.Index(Columns(1), Application.Match(PartName, Columns(2), 0))


I would place the data in the worksheet not in the VB Editor?

[Bob Phillips]

No, I assume you already have the date in the variable filename. If you want to build it all, use,



Dim filename As String
Dim partNum As String
Dim PartName As String

PartName = "muffler"
filename = "muffler-20120212.xls"
partNum = Application.Index(Columns(1), Application.Match(PartName, Columns(2), 0))
filename = PartName & "-" & Format(Date, "yyyymmdd") & "-" & partNum

[jo15765]

Okay then I am not following your code. Where is this piece of code looking for the part_number

partNum = Application.Index(Columns(1), Application.Match(PartName, Columns(2), 0))

[Bob Phillips]

That piece of code assumes that you have the partname, and that you have a worksheet that has part numbers in column 1, part names in column 2, and so it looks the part name up and gets the part number.

As I said, it will need modifying to your situation, another worksheet, another workbook, or whatever you have.

[jo15765]

Okay, that's what I was thinking but I wanted clarification. Thanks for the template, I'll try to tweak to custom suit and post back if any issues :)

[jo15765]

I keep getting a type mismatch on this line

partNum = Application.Index(Columns(1), Application.Match(PartName, Columns(2), 0))


But I set it up with PartNum in Column 1 or A and PartName in Column 2 or B

[Bob Phillips]

Are you referencing the correct sheet? What is in the variable PartName.

[jo15765]

There is only one worksheet in this workbook, however I am not explicitly referencing it. PartName is alpha numeric, and may be declared as a Variant -- I'll have to check the previous coding -- if that's the case a simple conversion to string would correct this, right?

[jo15765]

Nope disregard, PartName is String throughout the entire procedure, so that was not the issue.

[Bob Phillips]

Without seeing everything, I am at a loss to know what is the problem.

[jo15765]

Attached is a sampling of the partnames and partnumbers (I haven't typed em all in yet, but should work for testing purposes) as well as a workbook that shows what I am trying to accomplish.