macro to print xps then save with name value in (B2)

  • Thread starter Thread starter stephen.ditchfield
  • Start date Start date
S

stephen.ditchfield

Hello,
I have a macro which will print (A1:F20) with XPS, is there a way to save with a filename automatically, with the filename taken from (B2). It will be the same path just a different name each time.

all help much appreciated
regards
Ditchy
Ballarat
Australia
 
Hello,
I have a macro which will print (A1:F20) with XPS, is there a way to
save with a filename automatically, with the filename taken from
(B2). It will be the same path just a different name each time.

all help much appreciated
regards
Ditchy
Ballarat
Australia
Click to expand...

Well.., couldn't you just pass the value in B2 when you specify the
filename in the SaveAs action? ..perhaps!

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion
 
Well.., couldn't you just pass the value in B2 when you specify the

filename in the SaveAs action? ..perhaps!



--

Garry



Free usenet access at http://www.eternal-september.org

Classic VB Users Regroup!

comp.lang.basic.visual.misc

microsoft.public.vb.general.discussion
Click to expand...

Hi Garry,
not sure what you mean, would you be able to explain it to me.
I would like it to be done through a macro when I click a button on the sheet.

regards
Ditchy
 
Hi Garry,
not sure what you mean, would you be able to explain it to me.
I would like it to be done through a macro when I click a button on
the sheet.

regards
Ditchy
Click to expand...

Best if you record a macro to 'grab' whatever code it generates while
doing the task manually. See what it gives you to work with. Post back
the code if you need further assistance...

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion
 
Hi Garry
here is the code

Sub copypastetoXPS()
'
' copypastetoXPS Macro
' Macro recorded 15/05/2014 by Ditchy
'

'
ActiveWindow.SmallScroll Down:=-12
Application.ActivePrinter = "Microsoft XPS Document Writer on Ne01:"
ActiveWindow.SelectedSheets.PrintOut Copies:=1, ActivePrinter:= _
"Microsoft XPS Document Writer on Ne01:", Collate:=True
End Sub

how do I get it to save automatically here
"C:\Ballarat Harriers Data"
with a file name and date stamp?

regards
Ditchy
 
Best way IMO is to use a string builder sequent in a 'process'
procedure, then print to file...

Sub PrintToFile_XPS(Filename$)
' Prints Filename with ".xps" file extension
Const sPrinter$ = "Microsoft XPS Document Writer on NE01:"
ActiveWindow.SelectedSheets.PrintOut _
copies:=1, ActivePrinter:=sPrinter, _
PrintToFile:=True, PrToFileName:=Filename & ".xps"
End Sub

Sub Test_PrintToFile_XPS()
Dim sFile$

'Build sFile in logical steps
sFile = "C:\Ballarat Harriers Data\"'//path 1st

'Append the filename as per your requirements
'Do not include the filename extension here
'when using a document 'type' printer.
sFile = sFile & "MyFilename" '//edit to suit
sFile = sFile & Format(Now(), "_dd-mm-yyyy_hh-mm_AMPM")

PrintToFile_XPS sFile
End Sub

Note that the folder must exist as this won't create one if it doesn't!

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion
 
Note that I've revised the TimeStampFile routine in the other thread to
handle new unsaved files. I've revised your ConvertToValues routine to
work with the new stamper routine...

Sub ConvertToValues()
Dim wkbTarget As Workbook, wks, sFile$
Const sExt$ = ".xls" '//edit to suit

'Copy sheets to new workbook
ActiveWindow.SelectedSheets.Copy
Set wkbTarget = ActiveWorkbook

'Convert to values
For Each wks In wkbTarget.Worksheets
With wks.UsedRange: .Value = .Value: End With
Next 'wks

'At this point wkbTarget has not been saved,
'so timestamp a copy of it then close it.
TimeStampFile SavePath:="C:\Work Related Data", _
Filename:="MyFilename" & sExt
wkbTarget.Close SaveChanges:=False
'Cleanup
Set wkbTarget = Nothing
End Sub

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion
 
Hi Garry
the XPS macro is perfect, just what I need.
I am having trouble getting the Sub ConvertToValues() to function, keeps looking for the timestamp and I am not sure where to go from here. I am not very clever when it comes to macros.But XPS is great and much appreciated.

regards
Ditchy
 
I am having trouble getting the Sub ConvertToValues() to function,
keeps looking for the timestamp and I am not sure where to go from
here.
Click to expand...

There are 3 things you have to make sure are correct...

sExt$, SavePath:=, and Filename:=

...so they hold your prefered values. Then, make sure you have the new
TimeStampFile routine posted at the bottom of your other thread. (It
has a new subject title "New TimeStampFile routine also does new
unsaved files")

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion
 
Back
Top