macro or vb script to do the impossible.

WERNER SLABBERT

Board Regular
Joined
Mar 3, 2009
Messages
103
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?
 

Excel Facts

Show numbers in thousands?
Use a custom number format of #,##0,K. Each comma after the final 0 will divide the displayed number by another thousand

Andrew Poulsom

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092
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

Board Regular
Joined
Mar 3, 2009
Messages
103
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

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092
You have:

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

I gave you:

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

which is not the same in several respects.
 

WERNER SLABBERT

Board Regular
Joined
Mar 3, 2009
Messages
103
and when i use your code exactly it returms a compile error( expected : expression.)
 

Sandeep Warrier

Well-known Member
Joined
Oct 31, 2008
Messages
2,677
Hi Werner

I think you are missing quotes...
Code:
ActiveWorkbook.SaveAs FileName:=[B]"[/B]\\SHOP\Quotes\[B]"[/B] & Range("A2").Value & "\" & Range("A1").Value & ".xls"

Code:
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

Board Regular
Joined
Mar 3, 2009
Messages
103
Hi Sandeep.
Thanx for the info, it works great. what a difference a quote can make....lol
 
Last edited:

Forum statistics

Threads
1,176,402
Messages
5,902,861
Members
435,001
Latest member
ahsanali32

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top