macro or vb script to do the impossible.

WERNER SLABBERT

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

Lock one reference in a formula
Need 1 part of a formula to always point to the same range? use $ signs: $V$2:$Z$99 will always point to V2:Z99, even after copying

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
100
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
100

ADVERTISEMENT

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,672
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
100
Hi Sandeep.
Thanx for the info, it works great. what a difference a quote can make....lol
 
Last edited:

Watch MrExcel Video

Forum statistics

Threads
1,133,325
Messages
5,658,171
Members
418,430
Latest member
Chlwls808

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