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

[medic]

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.

 

[Barb Reinhardt]

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]

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

 

[JP]

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

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


HTH,
JP

 

[medic]

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]

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

 

[medic]

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 Documents/Insurance stuff/Patient OON bills/PMC OON bill.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]

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

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


HTH,
JP

 

[medic]

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

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

"http://companyweb/General Documents/Insurance stuff/Patient OON bills/& 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]

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


HTH,
JP

 

[medic]

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 Documents/Insurance stuff/Patient OON bills/ & 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]

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

i.e.

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


It was there in your previous posts.

HTH,
JP

 

[medic]

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 Documents/Insurance stuff/Patient OON bills/" & 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