macro or vb script to do the impossible.

[WERNER SLABBERT]

Would it be possible to write a macro that will allow you to print the same document on two different printers and save the workbook to a specific folder according to a name in a cell to a specific path.

eg: in cell A1 the Text = U15420
in cell A2 the test = Goedehoop

now on the network is a PC named "Shop" in there is a folder called "Quotes"
in "Quotes" there are 30+ folders with client's names. like the name in Cell A2 "Goedehoop"
so it looks like this "\\SHOP\Quotes\****** "

would i be possible to have a macro that will take the name in cell A2 lookup the name in the "Quotes" folder and save the workbook with the name in cell "A1" as well as print the workbook on two different printers on the network at the same time?

 

[Andrew Poulsom]

Try:

ActiveWorkbook.SaveAs FileName:=\\SHOP\Quotes\ & Range("A2").Value & "\" & Range("A1").Value & ".xls"

For your printing macro you can use the macro recorder to get the code.

 

[WERNER SLABBERT]

Doesn't seem to work when i combine the two. it gives me an syntax error on your script.
this is what i have. ( it's only a test book thus far )

Sub Macro1()
'
' Macro1 Macro
'

'
ChDir "\\CAELEX-SRV\Desktop\INSTALLS"
ActiveWorkbook.SaveAs Filename:="\\CAELEX-SRV\Desktop\& Range($A$2).Value & " \ " & Range($A$1).Value ,_"
ActiveCell.FormulaR1C1 = "1"
Range("A2").Select
ActiveCell.FormulaR1C1 = "4"
Range("A4").Select
ExecuteExcel4Macro "PRINT(1,,,1,,,,,,,,2,,,TRUE,,FALSE)"
Application.ActivePrinter = "TOSHIBA eS282/283Series PCL6 on Ne02:"
ExecuteExcel4Macro _
"PRINT(1,,,1,,,,,,,,2,""TOSHIBA eS282/283Series PCL6 on Ne02:"",,TRUE,,FALSE)"
End Sub

 

[Andrew Poulsom]

You have:

ActiveWorkbook.SaveAs Filename:="\\CAELEX-SRV\Desktop\& Range($A$2).Value & " \ " & Range($A$1).Value ,_"

I gave you:

ActiveWorkbook.SaveAs FileName:=\\SHOP\Quotes\ & Range("A2").Value & "\" & Range("A1").Value & ".xls"

which is not the same in several respects.

 

[WERNER SLABBERT]

and when i use your code exactly it returms a compile error( expected : expression.)

 

[Sandeep Warrier]

Hi Werner

I think you are missing quotes...

ActiveWorkbook.SaveAs FileName:=[B]"[/B]\\SHOP\Quotes\[B]"[/B] & Range("A2").Value & "\" & Range("A1").Value & ".xls"

ActiveWorkbook.SaveAs Filename:=[B]"[/B]\\CAELEX-SRV\Desktop\[B]"[/B] & Range([COLOR=Red][B]$A$2[/B][/COLOR]).Value & "\" & Range([B][COLOR=Red]$A$1[/COLOR][/B]).Value" & ".xls"

Also in VBA you don't need to use the absolute referencing styles. Simply Range("A1") or Range("A2") will work.

 

[WERNER SLABBERT]

Hi Sandeep.
Thanx for the info, it works great. what a difference a quote can make....lol