PDA

View Full Version : Appending to Workbook Name



jo15765
02-22-2012, 08:39 AM
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?

xld
02-22-2012, 08:47 AM
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
02-22-2012, 08:51 AM
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?

xld
02-22-2012, 09:03 AM
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
02-22-2012, 09:08 AM
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))

xld
02-22-2012, 09:11 AM
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
02-22-2012, 09:14 AM
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
02-22-2012, 09:49 AM
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

xld
02-22-2012, 10:16 AM
Are you referencing the correct sheet? What is in the variable PartName.

jo15765
02-22-2012, 10:25 AM
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
02-22-2012, 10:57 AM
Nope disregard, PartName is String throughout the entire procedure, so that was not the issue.

xld
02-22-2012, 11:15 AM
Without seeing everything, I am at a loss to know what is the problem.

jo15765
02-22-2012, 11:32 AM
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.