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?
 

Some videos you may like

Excel Facts

Format cells as currency
Select range and press Ctrl+Shift+4 to format cells as currency. (Shift 4 is the $ sign).

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

Board Regularmmmm Pizza
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:

Subscribe on YouTube

Watch MrExcel Video

Forum statistics

Threads
1,106,228
Messages
5,509,926
Members
408,765
Latest member
johncox3

This Week's Hot Topics

  • Turn fraction around
    Hello I need to turn a fraction around, for example I have 1/3 but I need to present as 3/1
  • TIme Clock record reformatting to ???
    Hello All, I'd like some help formatting this (Tbl-A)(Loaded via Power Query) [ATTACH type="full" width="511px" alt="PQdata.png"]22252[/ATTACH]...
  • TextBox Match
    hi, I am having a few issues with my code below, what I need it to do is when they enter a value in textbox8 (QTY) either 1,2 or 3 the 3 textboxes...
  • Using Large function based on Multiple Criteria
    Hello, I can't seem to get a Large formula to work based on two criteria's. I can easily get a oldest value based one value, but I'm struggling...
  • Can you check my code please
    Hi, Im going round in circles with a Compil Error End With Without With Here is the code [CODE=rich] Private Sub...
  • Combining 2 pivot tables into 1 chart
    Hello everyone, My question sounds simple but I do not know the answer. I have 2 pivot tables and 2 charts that go with this. However I want to...
Top