Tek-Tips is the largest IT community on the Internet today!
Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!
-
Congratulations Mike Lewis on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!
Printing Excel File From Command Button 1
- Thread starter metalboy
- Start date
MetalBoy,
you can print an excel sheet from a command button in access what you have to do is add the Excel object library to your references and open excel as a new object and set a reference to the spreadsheet that you wish to print.
If you add the object library you will be able to use the object browser to view the classes and their members.
Hope this helps......i think i have an example if you need it.
Xuereveds.
you can print an excel sheet from a command button in access what you have to do is add the Excel object library to your references and open excel as a new object and set a reference to the spreadsheet that you wish to print.
If you add the object library you will be able to use the object browser to view the classes and their members.
Hope this helps......i think i have an example if you need it.
Xuereveds.
- Thread starter
- #3
I couldnt find my example however this should work
Dim exc As New Excel.Application
Dim objexc As Excel.Workbook
Dim objsheet As Excel.Worksheet
Set exc = CreateObject("Excel.Application")
Set objexc = exc.Workbooks.Open("C:\Book1.xls")
Set objsheet = objexc.Sheets("Sheet1")
objsheet.PrintOut
objexc.Close
Give it a try let me know how you get on.
Dim exc As New Excel.Application
Dim objexc As Excel.Workbook
Dim objsheet As Excel.Worksheet
Set exc = CreateObject("Excel.Application")
Set objexc = exc.Workbooks.Open("C:\Book1.xls")
Set objsheet = objexc.Sheets("Sheet1")
objsheet.PrintOut
objexc.Close
Give it a try let me know how you get on.
- Thread starter
- #5
This is what the code for my command button looks like but it falls over on the second line! any ideas???
Private Sub Command24_Click()
Dim exc As New Excel.Application
Dim objexc As Excel.Workbook
Dim objsheet As Excel.Worksheet
Set exc = CreateObject("Excel.Application")
Set objexc = exc.Workbooks.Open("N:\CUSTENQ\DATA\Confirmation\Alex Testing\Sales By Month Charts.xls")
Set objsheet = objexc.Sheets("Adam")
objsheet.PrintOut
objexc.Close
End Sub
Private Sub Command24_Click()
Dim exc As New Excel.Application
Dim objexc As Excel.Workbook
Dim objsheet As Excel.Worksheet
Set exc = CreateObject("Excel.Application")
Set objexc = exc.Workbooks.Open("N:\CUSTENQ\DATA\Confirmation\Alex Testing\Sales By Month Charts.xls")
Set objsheet = objexc.Sheets("Adam")
objsheet.PrintOut
objexc.Close
End Sub
-
1
- #6
What is the second line? Second line of code, second executable line, what is the errormsg "falls over" doesn't give much to work on.
One guess though, have you set a reference to Excel? (in VBE Tools | References - set a checkmark on Microsoft Excel N Object Library - N being number according to the version)
Roy-Vidar
One guess though, have you set a reference to Excel? (in VBE Tools | References - set a checkmark on Microsoft Excel N Object Library - N being number according to the version)
Roy-Vidar
- Thread starter
- #7
Hi!
Pardon me, but what do you mean by "can you help at all??"
Way I read it, I'm not really tempted to answer, but still...
This runs fine on my setup, unless the sheet isn't a worksheet, but for instance a chart sheet (13, Type mismatch). If this is the case, replace:
[tt]Dim objsheet As Excel.Worksheet[/tt]
with
[tt]Dim objsheet As Excel.Chart[/tt]
Please also take a look at this faq faq181-2886 on how to get the most out of the membership.
Roy-Vidar
Pardon me, but what do you mean by "can you help at all??"
Way I read it, I'm not really tempted to answer, but still...
This runs fine on my setup, unless the sheet isn't a worksheet, but for instance a chart sheet (13, Type mismatch). If this is the case, replace:
[tt]Dim objsheet As Excel.Worksheet[/tt]
with
[tt]Dim objsheet As Excel.Chart[/tt]
Please also take a look at this faq faq181-2886 on how to get the most out of the membership.
Roy-Vidar
- Thread starter
- #10
- Thread starter
- #12
Hi again Just another quick question if possible.
The sheet that i am printing out is as you say a pivot chart. (Sorry i should have mentioned that before) When this command button is pressed will the chart update itself with up to date data?
Regards
Alex
The sheet that i am printing out is as you say a pivot chart. (Sorry i should have mentioned that before) When this command button is pressed will the chart update itself with up to date data?
Regards
Alex
That's a bit out of my league, I'm afraid. Did a quick test and found that if the information is linked, it doesn't seem to update.
Did some tweaking and testing, and found something that worked on my setup. Not very elegant, but...
[tt]objexc.worksheets("piv").range("a1").pivottable.refrestable
Set objsheet = objexc.Sheets("Adam")
objsheet.PrintOut
objexc.Close asSaveNo[/tt]
Use a reference to the sheet where the pivottable is stored (and range), and refresh it first. Hmm - found it all thru F1;-)
Roy-Vidar
Did some tweaking and testing, and found something that worked on my setup. Not very elegant, but...
[tt]objexc.worksheets("piv").range("a1").pivottable.refrestable
Set objsheet = objexc.Sheets("Adam")
objsheet.PrintOut
objexc.Close asSaveNo[/tt]
Use a reference to the sheet where the pivottable is stored (and range), and refresh it first. Hmm - found it all thru F1;-)
Roy-Vidar
- Thread starter
- #14
- Status
Similar threads
- Question
- Locked
- Question