how do i create a macro that will print and save my file to sharep
 

I am creating a reciept for medical services we rendered. I need to put 3
buttons (I know how to ad the button) to an excel sheet that will cause the
program to save the spreadsheet possibly as a PDF or as an excel file on my
sharepoint server. another button that will print the invoice -- specificly
page one --the other pages are referance data and one button that will do
both at the same time and reset the invoice to defaults so we can ad the next
pt info and charges.

how do i create a macro that will print and save my file to sharep
 

If it were me, I'd map the sharepoint server to a drive and save to that
location. You could record much of this and then tweak as needed. What
I'd like to know is if there is a way to save to a SharePoint server in a way
that versions are retained. I don't think my method would do it.

Barb Reinhardt



"medic" wrote:

I am creating a reciept for medical services we rendered. I need to put 3
buttons (I know how to ad the button) to an excel sheet that will cause the
program to save the spreadsheet possibly as a PDF or as an excel file on my
sharepoint server. another button that will print the invoice -- specificly
page one --the other pages are referance data and one button that will do
both at the same time and reset the invoice to defaults so we can ad the next
pt info and charges.

how do i create a macro that will print and save my file to sh
 

saving to the sharepoint server is not the problem -- the problem is getting
it to save using a nam made up of data from in the spread sheet

"Barb Reinhardt" wrote:

If it were me, I'd map the sharepoint server to a drive and save to that
location. You could record much of this and then tweak as needed. What
I'd like to know is if there is a way to save to a SharePoint server in a way
that versions are retained. I don't think my method would do it.

Barb Reinhardt



"medic" wrote:

I am creating a reciept for medical services we rendered. I need to put 3
buttons (I know how to ad the button) to an excel sheet that will cause the
program to save the spreadsheet possibly as a PDF or as an excel file on my
sharepoint server. another button that will print the invoice -- specificly
page one --the other pages are referance data and one button that will do
both at the same time and reset the invoice to defaults so we can ad the next
pt info and charges.

how do i create a macro that will print and save my file to sh
 

Do you mean you want to use some cell entry as the filename?

X = Range("A1").Value
ActiveWorkbook.SaveAs Filename:=X


HTH,
JP



"medic" wrote in message
...
saving to the sharepoint server is not the problem -- the problem is
getting
it to save using a nam made up of data from in the spread sheet

"Barb Reinhardt" wrote:

If it were me, I'd map the sharepoint server to a drive and save to that
location. You could record much of this and then tweak as needed.
What
I'd like to know is if there is a way to save to a SharePoint server in a
way
that versions are retained. I don't think my method would do it.

Barb Reinhardt



"medic" wrote:

I am creating a reciept for medical services we rendered. I need to put
3
buttons (I know how to ad the button) to an excel sheet that will cause
the
program to save the spreadsheet possibly as a PDF or as an excel file
on my
sharepoint server. another button that will print the invoice --
specificly
page one --the other pages are referance data and one button that will
do
both at the same time and reset the invoice to defaults so we can ad
the next
pt info and charges.

how do i create a macro that will print and save my file to sh
 

still wont work here is what I got the last 2 line are in red:

Sub save()
'
' save Macro
'

'
ChDir "C:\Users\medic\Documents\vaccine form"
x = d1.Value
ActiveWorkbook.SaveAs Filename:=x
"C:\Users\medic\Documents\vaccine form\vaccines form1.xlsm",
FileFormat:= _
xlOpenXMLWorkbookMacroEnabled, CreateBackup:=False
End Sub


thansk for the help

"JP" wrote:

Do you mean you want to use some cell entry as the filename?

X = Range("A1").Value
ActiveWorkbook.SaveAs Filename:=X


HTH,
JP



"medic" wrote in message
...
saving to the sharepoint server is not the problem -- the problem is
getting
it to save using a nam made up of data from in the spread sheet

"Barb Reinhardt" wrote:

If it were me, I'd map the sharepoint server to a drive and save to that
location. You could record much of this and then tweak as needed.
What
I'd like to know is if there is a way to save to a SharePoint server in a
way
that versions are retained. I don't think my method would do it.

Barb Reinhardt



"medic" wrote:

I am creating a reciept for medical services we rendered. I need to put
3
buttons (I know how to ad the button) to an excel sheet that will cause
the
program to save the spreadsheet possibly as a PDF or as an excel file
on my
sharepoint server. another button that will print the invoice --
specificly
page one --the other pages are referance data and one button that will
do
both at the same time and reset the invoice to defaults so we can ad
the next
pt info and charges.

how do i create a macro that will print and save my file to sh
 

I don't understand your code here.

1. By "x = d1.Value" do you mean "x = Range("D1").Value" ??

2. The FileName property only takes one argument, it looks like you
are trying to use two (variable 'x' and a workbook name). You can use

ActiveWorkbook.SaveAs Filename:=x

OR

ActiveWorkbook.SaveAs Filename:="C:\Users\medic\Documents\vaccine form
\vaccines form1.xlsm"

but not both at the same time.


HTH,
JP

On Jan 5, 10:02*am, medic wrote:
still wont work here is what I got the last 2 line are in red:

Sub save()
'
' save Macro
'

'
* * ChDir "C:\Users\medic\Documents\vaccine form"
* * x = d1.Value
* * ActiveWorkbook.SaveAs Filename:=x
* * * * "C:\Users\medic\Documents\vaccine form\vaccines form1.xlsm",
FileFormat:= _
* * * * xlOpenXMLWorkbookMacroEnabled, CreateBackup:=False
End Sub

thansk for the help



"JP" wrote:
Do you mean you want to use some cell entry as the filename?

X = Range("A1").Value
ActiveWorkbook.SaveAs Filename:=X

HTH,
JP

how do i create a macro that will print and save my file to sh
 

here is what i have now --how do i tell it to name the file based on date
created the pt name and their date of birth? I combined all the data into a
name in one cell using "&" but how do i tell the macro to pull the the data
from that cell (the result not the formula)?

Sub saveandprint()
'
' saveandprint Macro
'

'
ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _

"http://companyweb/General%20Documents/Insurance%20stuff/Patient%20OON%20bills/PMC%20OON%20bill.pdf" _
, Quality:=xlQualityStandard, IncludeDocProperties:=True,
IgnorePrintAreas _
:=False, OpenAfterPublish:=False
Application.ActivePrinter = "Xerox Phaser 6180MFP-D PCL 6 on Ne00:"
ExecuteExcel4Macro _
"PRINT(1,,,1,,,,,,,,2,""Xerox Phaser 6180MFP-D PCL 6 on
Ne00:"",,TRUE,,FALSE)"
End Sub

"JP" wrote:

I don't understand your code here.

1. By "x = d1.Value" do you mean "x = Range("D1").Value" ??

2. The FileName property only takes one argument, it looks like you
are trying to use two (variable 'x' and a workbook name). You can use

ActiveWorkbook.SaveAs Filename:=x

OR

ActiveWorkbook.SaveAs Filename:="C:\Users\medic\Documents\vaccine form
\vaccines form1.xlsm"

but not both at the same time.


HTH,
JP

On Jan 5, 10:02 am, medic wrote:
still wont work here is what I got the last 2 line are in red:

Sub save()
'
' save Macro
'

'
ChDir "C:\Users\medic\Documents\vaccine form"
x = d1.Value
ActiveWorkbook.SaveAs Filename:=x
"C:\Users\medic\Documents\vaccine form\vaccines form1.xlsm",
FileFormat:= _
xlOpenXMLWorkbookMacroEnabled, CreateBackup:=False
End Sub

thansk for the help



"JP" wrote:
Do you mean you want to use some cell entry as the filename?

X = Range("A1").Value
ActiveWorkbook.SaveAs Filename:=X

HTH,
JP

how do i create a macro that will print and save my file to sh
 

If the information was in A1 you could add it to the filename argument
like this:

Filename:= "http://companyweb/General%20Documents/Insurance%20stuff/
Patient%20OON..." & Range("A1").Value _
, Quality:=xlQualityStandard, IncludeDocProperties:=True,
IgnorePrintAreas:=False, OpenAfterPublish:=False


HTH,
JP

On Jan 7, 5:17*pm, medic wrote:
here is what i have now --how do i tell it to name the file based on date
created the pt name and their date of birth? *I combined all the data into a
name in one cell using "&" but how do i tell the macro to pull the the data
from that cell (the result not the formula)?

Sub saveandprint()
'
' saveandprint Macro
'

'
* * ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _

"http://companyweb/General%20Documents/Insurance%20stuff/Patient%20OON..." _
* * * * , Quality:=xlQualityStandard, IncludeDocProperties:=True,
IgnorePrintAreas _
* * * * :=False, OpenAfterPublish:=False
* * Application.ActivePrinter = "Xerox Phaser 6180MFP-D PCL 6 on Ne00:"
* * ExecuteExcel4Macro _
* * * * "PRINT(1,,,1,,,,,,,,2,""Xerox Phaser 6180MFP-D PCL 6 on
Ne00:"",,TRUE,,FALSE)"
End Sub



"JP" wrote:
I don't understand your code here.

1. By "x = d1.Value" do you mean "x = Range("D1").Value" ??

2. The FileName property only takes one argument, it looks like you
are trying to use two (variable 'x' and a workbook name). You can use

ActiveWorkbook.SaveAs Filename:=x

OR

ActiveWorkbook.SaveAs Filename:="C:\Users\medic\Documents\vaccine form
\vaccines form1.xlsm"

but not both at the same time.

HTH,
JP

On Jan 5, 10:02 am, medic wrote:
still wont work here is what I got the last 2 line are in red:

Sub save()
'
' save Macro
'

'
* * ChDir "C:\Users\medic\Documents\vaccine form"
* * x = d1.Value
* * ActiveWorkbook.SaveAs Filename:=x
* * * * "C:\Users\medic\Documents\vaccine form\vaccines form1.xlsm",
FileFormat:= _
* * * * xlOpenXMLWorkbookMacroEnabled, CreateBackup:=False
End Sub

thansk for the help

"JP" wrote:
Do you mean you want to use some cell entry as the filename?

X = Range("A1").Value
ActiveWorkbook.SaveAs Filename:=X

HTH,
JP- Hide quoted text -

- Show quoted text -

how do i create a macro that will print and save my file to sh
 

here is what i have -- getting a syntex error:
Sub saveandprint()
'
' saveandprint Macro
'

'
ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _

"http://companyweb/General%20Documents/Insurance%20stuff/Patient%20OON%20bills/& Range("A1").Value _

, Quality:=xlQualityMinimum, IncludeDocProperties:=True,
IgnorePrintAreas _
:=False, From:=1, To:=1, OpenAfterPublish:=False
ExecuteExcel4Macro "PRINT(2,1,1,1,,,,,,,,2,,,TRUE,,FALSE)"
End Sub



"JP" wrote:

If the information was in A1 you could add it to the filename argument
like this:

Filename:= "http://companyweb/General%20Documents/Insurance%20stuff/
Patient%20OON..." & Range("A1").Value _
, Quality:=xlQualityStandard, IncludeDocProperties:=True,
IgnorePrintAreas:=False, OpenAfterPublish:=False


HTH,
JP

On Jan 7, 5:17 pm, medic wrote:
here is what i have now --how do i tell it to name the file based on date
created the pt name and their date of birth? I combined all the data into a
name in one cell using "&" but how do i tell the macro to pull the the data
from that cell (the result not the formula)?

Sub saveandprint()
'
' saveandprint Macro
'

'
ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _

"http://companyweb/General%20Documents/Insurance%20stuff/Patient%20OON..." _
, Quality:=xlQualityStandard, IncludeDocProperties:=True,
IgnorePrintAreas _
:=False, OpenAfterPublish:=False
Application.ActivePrinter = "Xerox Phaser 6180MFP-D PCL 6 on Ne00:"
ExecuteExcel4Macro _
"PRINT(1,,,1,,,,,,,,2,""Xerox Phaser 6180MFP-D PCL 6 on
Ne00:"",,TRUE,,FALSE)"
End Sub



"JP" wrote:
I don't understand your code here.

1. By "x = d1.Value" do you mean "x = Range("D1").Value" ??

2. The FileName property only takes one argument, it looks like you
are trying to use two (variable 'x' and a workbook name). You can use

ActiveWorkbook.SaveAs Filename:=x

OR

ActiveWorkbook.SaveAs Filename:="C:\Users\medic\Documents\vaccine form
\vaccines form1.xlsm"

but not both at the same time.

HTH,
JP

On Jan 5, 10:02 am, medic wrote:
still wont work here is what I got the last 2 line are in red:

Sub save()
'
' save Macro
'

'
ChDir "C:\Users\medic\Documents\vaccine form"
x = d1.Value
ActiveWorkbook.SaveAs Filename:=x
"C:\Users\medic\Documents\vaccine form\vaccines form1.xlsm",
FileFormat:= _
xlOpenXMLWorkbookMacroEnabled, CreateBackup:=False
End Sub

thansk for the help

"JP" wrote:
Do you mean you want to use some cell entry as the filename?

X = Range("A1").Value
ActiveWorkbook.SaveAs Filename:=X

HTH,
JP- Hide quoted text -

- Show quoted text -

how do i create a macro that will print and save my file to sh
 

There has to be a space before the "& Range("A1").Value" part.


HTH,
JP

On Jan 8, 10:45*am, medic wrote:
here is what i have -- getting a syntex error:
Sub saveandprint()
'
' saveandprint Macro
'

'
* * ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _

"http://companyweb/General%20Documents/Insurance%20stuff/Patient%20OON...Range("A1").Value _

* * * * , Quality:=xlQualityMinimum, IncludeDocProperties:=True,
IgnorePrintAreas _
* * * * :=False, From:=1, To:=1, OpenAfterPublish:=False
* * ExecuteExcel4Macro "PRINT(2,1,1,1,,,,,,,,2,,,TRUE,,FALSE)"
End Sub

how do i create a macro that will print and save my file to sh
 

getting a "Compile error: expected end of statement" here is what i now have:

Sub saveandprint()
'
' saveandprint Macro
'

'
ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _

"http://companyweb/General%20Documents/Insurance%20stuff/Patient%20OON%20bills/ & Range("d1").Value _

, Quality:=xlQualityMinimum, IncludeDocProperties:=True,
IgnorePrintAreas _
:=False, From:=1, To:=1, OpenAfterPublish:=False
ExecuteExcel4Macro "PRINT(2,1,1,1,,,,,,,,2,,,TRUE,,FALSE)"
End Sub




"JP" wrote:

There has to be a space before the "& Range("A1").Value" part.


HTH,
JP

On Jan 8, 10:45 am, medic wrote:
here is what i have -- getting a syntex error:
Sub saveandprint()
'
' saveandprint Macro
'

'
ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _

"http://companyweb/General%20Documents/Insurance%20stuff/Patient%20OON...Range("A1").Value _

, Quality:=xlQualityMinimum, IncludeDocProperties:=True,
IgnorePrintAreas _
:=False, From:=1, To:=1, OpenAfterPublish:=False
ExecuteExcel4Macro "PRINT(2,1,1,1,,,,,,,,2,,,TRUE,,FALSE)"
End Sub

how do i create a macro that will print and save my file to sh
 

It looks like you aren't closing the string with a closing quotation
mark.

i.e.

ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
"http://companyweb/General%20Documents/Insurance%20stuff/Patient%20OON
%20bills/" & Range("D1").Value


It was there in your previous posts.

HTH,
JP

On Jan 8, 6:16*pm, medic wrote:
getting a "Compile error: *expected end of statement" here is what i now have:

Sub saveandprint()
'
' saveandprint Macro
'

'
* * ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _

"http://companyweb/General%20Documents/Insurance%20stuff/Patient%20OON...& Range("d1").Value _

* * * * , Quality:=xlQualityMinimum, IncludeDocProperties:=True,
IgnorePrintAreas _
* * * * :=False, From:=1, To:=1, OpenAfterPublish:=False
* * ExecuteExcel4Macro "PRINT(2,1,1,1,,,,,,,,2,,,TRUE,,FALSE)"
End Sub



"JP" wrote:
There has to be a space before the "& Range("A1").Value" part.

HTH,
JP

On Jan 8, 10:45 am, medic wrote:
here is what i have -- getting a syntex error:
Sub saveandprint()
'
' saveandprint Macro
'

'
* * ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _

"http://companyweb/General%20Documents/Insurance%20stuff/Patient%20OON....("A1").Value _

* * * * , Quality:=xlQualityMinimum, IncludeDocProperties:=True,
IgnorePrintAreas _
* * * * :=False, From:=1, To:=1, OpenAfterPublish:=False
* * ExecuteExcel4Macro "PRINT(2,1,1,1,,,,,,,,2,,,TRUE,,FALSE)"
End Sub- Hide quoted text -

- Show quoted text -

how do i create a macro that will print and save my file to sh
 

the macro editor is not immediatly going red anymore but when i click the
button the macro is asigned to I get a run time error --here is the code i
have now.
Sub saveandprint()
'
' saveandprint Macro
'

'
ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _

"http://companyweb/General%20Documents/Insurance%20stuff/Patient%20OON%20bills/" & Range("d1").Value _
, Quality:=xlQualityMinimum, IncludeDocProperties:=True,
IgnorePrintAreas _
:=False, From:=1, To:=1, OpenAfterPublish:=False
ExecuteExcel4Macro "PRINT(2,1,1,1,,,,,,,,2,,,TRUE,,FALSE)"
End Sub

Thanks




"JP" wrote:

It looks like you aren't closing the string with a closing quotation
mark.

i.e.

ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
"http://companyweb/General%20Documents/Insurance%20stuff/Patient%20OON
%20bills/" & Range("D1").Value


It was there in your previous posts.

HTH,
JP

On Jan 8, 6:16 pm, medic wrote:
getting a "Compile error: expected end of statement" here is what i now have:

Sub saveandprint()
'
' saveandprint Macro
'

'
ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _

"http://companyweb/General%20Documents/Insurance%20stuff/Patient%20OON...& Range("d1").Value _

, Quality:=xlQualityMinimum, IncludeDocProperties:=True,
IgnorePrintAreas _
:=False, From:=1, To:=1, OpenAfterPublish:=False
ExecuteExcel4Macro "PRINT(2,1,1,1,,,,,,,,2,,,TRUE,,FALSE)"
End Sub



"JP" wrote:
There has to be a space before the "& Range("A1").Value" part.

HTH,
JP

On Jan 8, 10:45 am, medic wrote:
here is what i have -- getting a syntex error:
Sub saveandprint()
'
' saveandprint Macro
'

'
ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _

"http://companyweb/General%20Documents/Insurance%20stuff/Patient%20OON....("A1").Value _

, Quality:=xlQualityMinimum, IncludeDocProperties:=True,
IgnorePrintAreas _
:=False, From:=1, To:=1, OpenAfterPublish:=False
ExecuteExcel4Macro "PRINT(2,1,1,1,,,,,,,,2,,,TRUE,,FALSE)"
End Sub- Hide quoted text -

- Show quoted text -