Excel workbook and printing

S

SF

Hi,

I have developed the following code below. This code work fine except asking
user to save the opened worksheet when closing. I don't want user to save
any information at this stage, Is there a way to disable the message "Do you
want to save...", Yes, No Cancel...


Public Function PrintTravel(ID As Long)
Dim xlApp As Excel.Application
Dim xlBook As Excel.Workbook
Dim xlSheet As Excel.Worksheet
Dim dbs As DAO.Database
Dim rst As DAO.Recordset
Dim qdf As DAO.QueryDef
Dim Stg As String
'Dim I, J As Long
Dim NoDays As String
Dim FromTo As String


Stg = "SELECT Travels.*"
Stg = Stg & " FROM Travels"
Stg = Stg & " WHERE (((Travels.TravelID)=" & ID & "));"


Set dbs = CurrentDb

Set qdf = dbs.QueryDefs("Travels")
qdf.Parameters("ID") = ID
Set rst = qdf.OpenRecordset(dbOpenDynaset)

Set xlApp = CreateObject("Excel.Application")


xlApp.Visible = False
xlApp.Workbooks.Open "S:\General\LAAR\Database\Templates\AOT.XLS"
xlApp.Sheets(1).Cells(6, 5).Value = rst![Names]
xlApp.Sheets(1).Cells(7, 5).Value = rst![Purpose]
xlApp.Sheets(1).Cells(10, 5).Value = rst![Destination] & FromTo

xlApp.ActiveWorkbook.PrintOut
xlApp.ActiveWorkbook.Saved = False
xlApp.Quit
End Function

SF
 
G

Guest

Hi SF,

Try this version. It seems to work. I'm not sure that you really need the
querydef stuff. I added error handling to your code, and I'm closing objects
and setting them to Nothing to prevent memory leaks and database bloat (not
closing DAO recordsets is a good way to help bloat an application).

Although this line doesn't seem to be required (?), I added it anyways:
xlApp.UserControl = False

since it is shown in the XL Automation Help file:

"An instance of Microsoft Excel closes automatically when its object
variable loses scope or is set to the keyword Nothing if there are no
workbooks open, and the Application object's UserControl property is set to
False."

You might want to download a copy of this Help file. You can get it here:
http://support.microsoft.com/?kbid=302460


Option Compare Database
Option Explicit

Public Function PrintTravel(ID As Long)
On Error GoTo ProcError

Dim xlApp As Excel.Application
Dim xlBook As Excel.Workbook
Dim xlSheet As Excel.Worksheet
Dim dbs As DAO.Database
Dim rst As DAO.Recordset
Dim strSQL As String
Dim FromTo As String

strSQL = "SELECT Travels.*" _
& " FROM Travels" _
& " WHERE TravelID=" & ID

Set dbs = CurrentDb
Set rst = dbs.OpenRecordset(strSQL, dbOpenDynaset)

Set xlApp = CreateObject("Excel.Application")

xlApp.Visible = False
xlApp.Workbooks.Open "S:\General\LAAR\Database\Templates\AOT.XLS"

xlApp.Sheets(1).Cells(6, 5).Value = rst![Names]
xlApp.Sheets(1).Cells(7, 5).Value = rst![Purpose]
xlApp.Sheets(1).Cells(10, 5).Value = rst![Destination] & FromTo

xlApp.ActiveWorkbook.PrintOut
xlApp.ActiveWorkbook.Close SaveChanges:=False
xlApp.UserControl = False
xlApp.Quit

ExitProc:
'Cleanup
On Error Resume Next
Set xlSheet = Nothing
Set xlBook = Nothing
Set xlApp = Nothing
rst.Close: Set rst = Nothing
dbs.Close: Set dbs = Nothing
Exit Function
ProcError:
MsgBox "Error " & Err.Number & ": " & Err.Description, _
vbCritical, "Error in procedure PrintTravel..."
Resume ExitProc
Resume
End Function



Tom Wickerath
Microsoft Access MVP

http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________

SF said:
Hi,

I have developed the following code below. This code work fine except asking
user to save the opened worksheet when closing. I don't want user to save
any information at this stage, Is there a way to disable the message "Do you
want to save...", Yes, No Cancel...


Public Function PrintTravel(ID As Long)
Dim xlApp As Excel.Application
Dim xlBook As Excel.Workbook
Dim xlSheet As Excel.Worksheet
Dim dbs As DAO.Database
Dim rst As DAO.Recordset
Dim qdf As DAO.QueryDef
Dim Stg As String
'Dim I, J As Long
Dim NoDays As String
Dim FromTo As String


Stg = "SELECT Travels.*"
Stg = Stg & " FROM Travels"
Stg = Stg & " WHERE (((Travels.TravelID)=" & ID & "));"


Set dbs = CurrentDb

Set qdf = dbs.QueryDefs("Travels")
qdf.Parameters("ID") = ID
Set rst = qdf.OpenRecordset(dbOpenDynaset)

Set xlApp = CreateObject("Excel.Application")


xlApp.Visible = False
xlApp.Workbooks.Open "S:\General\LAAR\Database\Templates\AOT.XLS"
xlApp.Sheets(1).Cells(6, 5).Value = rst![Names]
xlApp.Sheets(1).Cells(7, 5).Value = rst![Purpose]
xlApp.Sheets(1).Cells(10, 5).Value = rst![Destination] & FromTo

xlApp.ActiveWorkbook.PrintOut
xlApp.ActiveWorkbook.Saved = False
xlApp.Quit
End Function

SF
Click to expand...
 
S

SF

Perfect! It works well, thank you

SF

Tom Wickerath said:
Hi SF,

Try this version. It seems to work. I'm not sure that you really need the
querydef stuff. I added error handling to your code, and I'm closing
objects
and setting them to Nothing to prevent memory leaks and database bloat
(not
closing DAO recordsets is a good way to help bloat an application).

Although this line doesn't seem to be required (?), I added it anyways:
xlApp.UserControl = False

since it is shown in the XL Automation Help file:

"An instance of Microsoft Excel closes automatically when its object
variable loses scope or is set to the keyword Nothing if there are no
workbooks open, and the Application object's UserControl property is set
to
False."

You might want to download a copy of this Help file. You can get it here:
http://support.microsoft.com/?kbid=302460


Option Compare Database
Option Explicit

Public Function PrintTravel(ID As Long)
On Error GoTo ProcError

Dim xlApp As Excel.Application
Dim xlBook As Excel.Workbook
Dim xlSheet As Excel.Worksheet
Dim dbs As DAO.Database
Dim rst As DAO.Recordset
Dim strSQL As String
Dim FromTo As String

strSQL = "SELECT Travels.*" _
& " FROM Travels" _
& " WHERE TravelID=" & ID

Set dbs = CurrentDb
Set rst = dbs.OpenRecordset(strSQL, dbOpenDynaset)

Set xlApp = CreateObject("Excel.Application")

xlApp.Visible = False
xlApp.Workbooks.Open "S:\General\LAAR\Database\Templates\AOT.XLS"

xlApp.Sheets(1).Cells(6, 5).Value = rst![Names]
xlApp.Sheets(1).Cells(7, 5).Value = rst![Purpose]
xlApp.Sheets(1).Cells(10, 5).Value = rst![Destination] & FromTo

xlApp.ActiveWorkbook.PrintOut
xlApp.ActiveWorkbook.Close SaveChanges:=False
xlApp.UserControl = False
xlApp.Quit

ExitProc:
'Cleanup
On Error Resume Next
Set xlSheet = Nothing
Set xlBook = Nothing
Set xlApp = Nothing
rst.Close: Set rst = Nothing
dbs.Close: Set dbs = Nothing
Exit Function
ProcError:
MsgBox "Error " & Err.Number & ": " & Err.Description, _
vbCritical, "Error in procedure PrintTravel..."
Resume ExitProc
Resume
End Function



Tom Wickerath
Microsoft Access MVP

http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________

SF said:
Hi,

I have developed the following code below. This code work fine except
asking
user to save the opened worksheet when closing. I don't want user to save
any information at this stage, Is there a way to disable the message "Do
you
want to save...", Yes, No Cancel...


Public Function PrintTravel(ID As Long)
Dim xlApp As Excel.Application
Dim xlBook As Excel.Workbook
Dim xlSheet As Excel.Worksheet
Dim dbs As DAO.Database
Dim rst As DAO.Recordset
Dim qdf As DAO.QueryDef
Dim Stg As String
'Dim I, J As Long
Dim NoDays As String
Dim FromTo As String


Stg = "SELECT Travels.*"
Stg = Stg & " FROM Travels"
Stg = Stg & " WHERE (((Travels.TravelID)=" & ID & "));"


Set dbs = CurrentDb

Set qdf = dbs.QueryDefs("Travels")
qdf.Parameters("ID") = ID
Set rst = qdf.OpenRecordset(dbOpenDynaset)

Set xlApp = CreateObject("Excel.Application")


xlApp.Visible = False
xlApp.Workbooks.Open "S:\General\LAAR\Database\Templates\AOT.XLS"
xlApp.Sheets(1).Cells(6, 5).Value = rst![Names]
xlApp.Sheets(1).Cells(7, 5).Value = rst![Purpose]
xlApp.Sheets(1).Cells(10, 5).Value = rst![Destination] & FromTo

xlApp.ActiveWorkbook.PrintOut
xlApp.ActiveWorkbook.Saved = False
xlApp.Quit
End Function

SF
Click to expand...
Click to expand...
 
G

Guest

Glad I could help. Thanks for reporting back.

I forgot to mention that you have not initialized your string variable
"FromTo". Thus, you are concatenating a zero length string in this step:

xlApp.Sheets(1).Cells(10, 5).Value = rst![Destination] & FromTo


Tom Wickerath
Microsoft Access MVP

http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________


SF said:
Perfect! It works well, thank you

SF

Tom Wickerath said:
Hi SF,

Try this version. It seems to work. I'm not sure that you really need the
querydef stuff. I added error handling to your code, and I'm closing
objects
and setting them to Nothing to prevent memory leaks and database bloat
(not
closing DAO recordsets is a good way to help bloat an application).

Although this line doesn't seem to be required (?), I added it anyways:
xlApp.UserControl = False

since it is shown in the XL Automation Help file:

"An instance of Microsoft Excel closes automatically when its object
variable loses scope or is set to the keyword Nothing if there are no
workbooks open, and the Application object's UserControl property is set
to
False."

You might want to download a copy of this Help file. You can get it here:
http://support.microsoft.com/?kbid=302460


Option Compare Database
Option Explicit

Public Function PrintTravel(ID As Long)
On Error GoTo ProcError

Dim xlApp As Excel.Application
Dim xlBook As Excel.Workbook
Dim xlSheet As Excel.Worksheet
Dim dbs As DAO.Database
Dim rst As DAO.Recordset
Dim strSQL As String
Dim FromTo As String

strSQL = "SELECT Travels.*" _
& " FROM Travels" _
& " WHERE TravelID=" & ID

Set dbs = CurrentDb
Set rst = dbs.OpenRecordset(strSQL, dbOpenDynaset)

Set xlApp = CreateObject("Excel.Application")

xlApp.Visible = False
xlApp.Workbooks.Open "S:\General\LAAR\Database\Templates\AOT.XLS"

xlApp.Sheets(1).Cells(6, 5).Value = rst![Names]
xlApp.Sheets(1).Cells(7, 5).Value = rst![Purpose]
xlApp.Sheets(1).Cells(10, 5).Value = rst![Destination] & FromTo

xlApp.ActiveWorkbook.PrintOut
xlApp.ActiveWorkbook.Close SaveChanges:=False
xlApp.UserControl = False
xlApp.Quit

ExitProc:
'Cleanup
On Error Resume Next
Set xlSheet = Nothing
Set xlBook = Nothing
Set xlApp = Nothing
rst.Close: Set rst = Nothing
dbs.Close: Set dbs = Nothing
Exit Function
ProcError:
MsgBox "Error " & Err.Number & ": " & Err.Description, _
vbCritical, "Error in procedure PrintTravel..."
Resume ExitProc
Resume
End Function



Tom Wickerath
Microsoft Access MVP

http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________

SF said:
Hi,

I have developed the following code below. This code work fine except
asking
user to save the opened worksheet when closing. I don't want user to save
any information at this stage, Is there a way to disable the message "Do
you
want to save...", Yes, No Cancel...


Public Function PrintTravel(ID As Long)
Dim xlApp As Excel.Application
Dim xlBook As Excel.Workbook
Dim xlSheet As Excel.Worksheet
Dim dbs As DAO.Database
Dim rst As DAO.Recordset
Dim qdf As DAO.QueryDef
Dim Stg As String
'Dim I, J As Long
Dim NoDays As String
Dim FromTo As String


Stg = "SELECT Travels.*"
Stg = Stg & " FROM Travels"
Stg = Stg & " WHERE (((Travels.TravelID)=" & ID & "));"


Set dbs = CurrentDb

Set qdf = dbs.QueryDefs("Travels")
qdf.Parameters("ID") = ID
Set rst = qdf.OpenRecordset(dbOpenDynaset)

Set xlApp = CreateObject("Excel.Application")


xlApp.Visible = False
xlApp.Workbooks.Open "S:\General\LAAR\Database\Templates\AOT.XLS"
xlApp.Sheets(1).Cells(6, 5).Value = rst![Names]
xlApp.Sheets(1).Cells(7, 5).Value = rst![Purpose]
xlApp.Sheets(1).Cells(10, 5).Value = rst![Destination] & FromTo

xlApp.ActiveWorkbook.PrintOut
xlApp.ActiveWorkbook.Saved = False
xlApp.Quit
End Function

SF
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Exort set of queries to excel 2
Office automation - Access data to Excel 2
Excel automation 2
Code wont work 2
Instance of Excel running after closing Excel 3
Export data to multiple excel spreadsheets 2
Calling Stored Procedure and have it return records for a report 4
First time Outlook VBA - Prob right-a-way 3

Top