At my wits end...abt launching document once file is exported to excel.

  • Thread starter Thread starter Cire via AccessMonster.com
  • Start date Start date
C

Cire via AccessMonster.com

Private Sub cmdExpToExcel_Click()

'code behind command button "Export to Excel"
Dim lngMax As Long
Dim lngCount As Long
Dim xlApp As Object
Dim xlBook As Object
Dim xlSheet As Object
Dim strFile As String

Set maindb = CurrentDb()
Set mainqdf = maindb.QueryDefs("qryCOSearch")
Set mainRst = mainqdf.OpenRecordset(dbOpenDynaset, dbEdit)
'all code below explains exporting the query results to excel
'allow user to choose path to save to
strFile = GetSaveFile_CLT("C:\", "Save this file as", "Untitled.
xls")
If strFile = "" Then
'user clicked cancel
Exit Sub
End If
'defining the variables
On Error Resume Next
Set xlApp = GetObject("Excel.Application")
If xlApp Is Nothing Then
Set xlApp = CreateObject("Excel.Application")
End If
On Error GoTo Err_Handler
Set xlBook = xlApp.Workbooks.Add
Set xlSheet = xlBook.Worksheets.Add
'formatting cells in excel
With xlSheet
For Each Cell In xlSheet.Range("A1", "S1")
Cell.Font.Size = 10
Cell.Font.Name = "Arial"
Cell.Font.Bold = True
Cell.Interior.Color = rgb(204, 255, 255)
Cell.HorizontalAlignment = xlHAlignCenter
Cell.WrapText = True
Next
.Cells(1, 2).HorizontalAlignment = xlHAlignLeft
.Columns("A:S").HorizontalAlignment = xlHAlignLeft
.Columns("A").ColumnWidth = 10
.Columns("B").ColumnWidth = 24
.Columns("C:D").ColumnWidth = 12
.Columns("E").ColumnWidth = 40
.Columns("F").ColumnWidth = 30
.Columns("G").ColumnWidth = 8
.Columns("H").ColumnWidth = 32
.Columns("I:J").ColumnWidth = 24
.Rows(1).RowHeight = 16
End With
'deleting all other worksheets except for "Results"
For lngCount = lngMax To 1 Step -1
If xlBook.Worksheets(lngCount).Name <> "Results" Then
xlBook.Worksheets(lngCount).Delete
End If
Next lngCount
'copying the query results from the recordset to the
excel file
With xlSheet
.Name = "Results"
.UsedRange.ClearContents
lngMax = mainRst.Fields.Count
For lngCount = lngMax To 1 Step -1
.Cells(1, lngCount).Value = mainRst.Fields(lngCount -
1).Name
Next lngCount
.Range("A2").CopyFromRecordset mainRst
End With
lngMax = xlBook.Worksheets.Count
'deleting all other worksheets except for "Results"
For lngCount = lngMax To 1 Step -1
If xlBook.Worksheets(lngCount).Name <> "Results" Then
xlBook.Worksheets(lngCount).Delete
End If
Next lngCount

xlBook.SaveAs strFile
MsgBox "Export Completed", vbInformation
MsgBox "Do you want to open your file?",
vbYesNoCancel
If vbYes Then
xlApp.Worksheets("Results") = Visible
xlApp.Visible = True
Else
'stay in window, do nothing
End If
GoTo Exit_Handler
Exit_Handler:

If Not xlSheet Is Nothing Then
Set xlSheet = Nothing
End If

If Not xlBook Is Nothing Then
Set xlBook = Nothing
End If

If Not xlApp Is Nothing Then
xlApp.Quit
Set xlApp = Nothing
End If

If Not mainRst Is Nothing Then
mainRst.Close
Set mainRst = Nothing
End If

If Not mainqdf Is Nothing Then
Set mainqdf = Nothing
End If

If Not maindb Is Nothing Then
Set maindb = Nothing
End If

Exit Sub

Err_Handler:
On Error Resume Next
MsgBox Err.Description, vbExclamation, "Error No: " & Err.Number
Resume Exit_Handler
Resume
End Sub


this line of codes doesnt work...:
------CODE-------
MsgBox "Do you want to open your file?", vbYesNoCancel
If vbYes Then
xlApp.Worksheets("Results") = Visible
xlApp.Visible = True
Else
'stay in window, do nothing
End If
-----End CODE----
i've tried
xlApp.visible = True
xlApp.usercontrol = True

and
xlApp.open "strFile", false

sigh..been at it whole morning...almost... half of it was solving the
"multiple instances of excel appearing"
 
Cire said:
Private Sub cmdExpToExcel_Click()

'code behind command button "Export to Excel"
Dim lngMax As Long
Dim lngCount As Long
Dim xlApp As Object
Dim xlBook As Object
Dim xlSheet As Object
Dim strFile As String

Set maindb = CurrentDb()
Set mainqdf = maindb.QueryDefs("qryCOSearch")
Set mainRst = mainqdf.OpenRecordset(dbOpenDynaset, dbEdit)
'all code below explains exporting the query results to excel
'allow user to choose path to save to
strFile = GetSaveFile_CLT("C:\", "Save this file as", "Untitled.
xls")
If strFile = "" Then
'user clicked cancel
Exit Sub
End If
'defining the variables
On Error Resume Next
Set xlApp = GetObject("Excel.Application")
If xlApp Is Nothing Then
Set xlApp = CreateObject("Excel.Application")
End If
On Error GoTo Err_Handler
Set xlBook = xlApp.Workbooks.Add
Set xlSheet = xlBook.Worksheets.Add
'formatting cells in excel
With xlSheet
For Each Cell In xlSheet.Range("A1", "S1")
Cell.Font.Size = 10
Cell.Font.Name = "Arial"
Cell.Font.Bold = True
Cell.Interior.Color = rgb(204, 255, 255)
Cell.HorizontalAlignment = xlHAlignCenter
Cell.WrapText = True
Next
.Cells(1, 2).HorizontalAlignment = xlHAlignLeft
.Columns("A:S").HorizontalAlignment = xlHAlignLeft
.Columns("A").ColumnWidth = 10
.Columns("B").ColumnWidth = 24
.Columns("C:D").ColumnWidth = 12
.Columns("E").ColumnWidth = 40
.Columns("F").ColumnWidth = 30
.Columns("G").ColumnWidth = 8
.Columns("H").ColumnWidth = 32
.Columns("I:J").ColumnWidth = 24
.Rows(1).RowHeight = 16
End With
'deleting all other worksheets except for "Results"
For lngCount = lngMax To 1 Step -1
If xlBook.Worksheets(lngCount).Name <> "Results" Then
xlBook.Worksheets(lngCount).Delete
End If
Next lngCount
'copying the query results from the recordset to the
excel file
With xlSheet
.Name = "Results"
.UsedRange.ClearContents
lngMax = mainRst.Fields.Count
For lngCount = lngMax To 1 Step -1
.Cells(1, lngCount).Value = mainRst.Fields(lngCount -
1).Name
Next lngCount
.Range("A2").CopyFromRecordset mainRst
End With
lngMax = xlBook.Worksheets.Count
'deleting all other worksheets except for "Results"
For lngCount = lngMax To 1 Step -1
If xlBook.Worksheets(lngCount).Name <> "Results" Then
xlBook.Worksheets(lngCount).Delete
End If
Next lngCount

xlBook.SaveAs strFile
MsgBox "Export Completed", vbInformation
MsgBox "Do you want to open your file?",
vbYesNoCancel
If vbYes Then
xlApp.Worksheets("Results") = Visible
xlApp.Visible = True
Else
'stay in window, do nothing
End If
GoTo Exit_Handler
Exit_Handler:

If Not xlSheet Is Nothing Then
Set xlSheet = Nothing
End If

If Not xlBook Is Nothing Then
Set xlBook = Nothing
End If

If Not xlApp Is Nothing Then
xlApp.Quit
Set xlApp = Nothing
End If

If Not mainRst Is Nothing Then
mainRst.Close
Set mainRst = Nothing
End If

If Not mainqdf Is Nothing Then
Set mainqdf = Nothing
End If

If Not maindb Is Nothing Then
Set maindb = Nothing
End If

Exit Sub

Err_Handler:
On Error Resume Next
MsgBox Err.Description, vbExclamation, "Error No: " & Err.Number
Resume Exit_Handler
Resume
End Sub

this line of codes doesnt work...:
------CODE-------
MsgBox "Do you want to open your file?", vbYesNoCancel
If vbYes Then
xlApp.Worksheets("Results") = Visible
xlApp.Visible = True
Else
'stay in window, do nothing
End If
-----End CODE----
i've tried
xlApp.visible = True
xlApp.usercontrol = True

and
xlApp.open "strFile", false

sigh..been at it whole morning...almost... half of it was solving the
"multiple instances of excel appearing"
Click to expand...

YESSSSS I did it!!!! woot..finally..the prob was in my exit handler and i
just noticed it!! in my exit handler i set my objects to nothing and used
xlApp.quit so no matter what, the document won't open...so i changed the code
to:

If vbYes Then
xlApp.Visible = True
xlApp.UserControl = True
xlBook.Visible = True
xlBook.UserControl = True
Else
'stay in result window, clean up by closing objects
and ending excel process
xlApp.Quit
Set xlApp = Nothing
Set xlBook = Nothing
Set xlSheet = nothng
End If

then in exit handler, juz commented out the statements:
'If Not xlSheet Is Nothing Then
'Set xlSheet = Nothing
'End If

'If Not xlBook Is Nothing Then
'Set xlBook = Nothing
'End If

'If Not xlApp Is Nothing Then
'xlApp.Quit
'Set xlApp = Nothing
'End If

the only thing now is..i get a Error number 0 one the excel file is launched..
anyone knows what is this? can i juz design a trap? i rem reading somewhere
abt error 0...
 
Cire said:
Private Sub cmdExpToExcel_Click()
Click to expand...
[quoted text clipped - 141 lines]
sigh..been at it whole morning...almost... half of it was solving the
"multiple instances of excel appearing"
Click to expand...

YESSSSS I did it!!!! woot..finally..the prob was in my exit handler and i
just noticed it!! in my exit handler i set my objects to nothing and used
xlApp.quit so no matter what, the document won't open...so i changed the code
to:

If vbYes Then
xlApp.Visible = True
xlApp.UserControl = True
xlBook.Visible = True
xlBook.UserControl = True
Else
'stay in result window, clean up by closing objects
and ending excel process
xlApp.Quit
Set xlApp = Nothing
Set xlBook = Nothing
Set xlSheet = nothng
End If

then in exit handler, juz commented out the statements:
'If Not xlSheet Is Nothing Then
'Set xlSheet = Nothing
'End If

'If Not xlBook Is Nothing Then
'Set xlBook = Nothing
'End If

'If Not xlApp Is Nothing Then
'xlApp.Quit
'Set xlApp = Nothing
'End If

the only thing now is..i get a Error number 0 one the excel file is launched..
anyone knows what is this? can i juz design a trap? i rem reading somewhere
abt error 0...
Click to expand...

well changing the err_handler to:
Err_Handler:
On Error Resume Next
Resume Exit_Handler
MsgBox Err.Description, vbExclamation, "Error No: " & Err.Number
Resume
End Sub

fixed it, i think it went to the msgbox before the exit_handler
 
Cire said:
[quoted text clipped - 38 lines]
anyone knows what is this? can i juz design a trap? i rem reading somewhere
abt error 0...
Click to expand...

well changing the err_handler to:
Err_Handler:
On Error Resume Next
Resume Exit_Handler
MsgBox Err.Description, vbExclamation, "Error No: " & Err.Number
Resume
End Sub

fixed it, i think it went to the msgbox before the exit_handler
Click to expand...

sigh..the multiple process EXCEL.exe is rearing its ugly head again....and
when its running, my code doesnt work..no exporting done..sigh any help?
while i'm still searching the forumns for a solution...heres my latest code:

Private Sub cmdExpToExcel_Click()

'code behind command button "Export to Excel"
Dim lngMax As Long
Dim lngCount As Long
Dim xlApp As Object
Dim xlBook As Object
Dim xlSheet As Object
Dim strFile As String
Dim running As Boolean

Set maindb = CurrentDb()
Set mainqdf = maindb.QueryDefs("qryCOSearch")
Set mainRst = mainqdf.OpenRecordset(dbOpenDynaset, dbEdit)
'all code below explains exporting the query results to excel
'allow user to choose path to save to
strFile = GetSaveFile_CLT("C:\", "Save this file as",
"strDefName")
If strFile = "" Then
'user clicked cancel
Exit Sub
End If
'defining the variables
On Error Resume Next
Set xlApp = GetObject("Excel.Application")
If xlApp Is Nothing Then
Set xlApp = CreateObject("Excel.Application")
Err.Clear
End If
On Error GoTo Err_Handler
Set xlBook = xlApp.Workbooks.Add
Set xlSheet = xlBook.Worksheets.Add
'formatting cells in excel
With xlSheet
For Each Cell In xlSheet.Range("A1", "S1")
Cell.Font.Size = 10
Cell.Font.Name = "Arial"
Cell.Font.Bold = True
Cell.Interior.Color = rgb(204, 255, 255)
Cell.HorizontalAlignment = xlHAlignCenter
Cell.WrapText = True
Next
.Cells(1, 2).HorizontalAlignment = xlHAlignLeft
.Columns("A:S").HorizontalAlignment = xlHAlignLeft
.Columns("A").ColumnWidth = 10
.Columns("B").ColumnWidth = 24
.Columns("C:D").ColumnWidth = 12
.Columns("E").ColumnWidth = 40
.Columns("F").ColumnWidth = 30
.Columns("G").ColumnWidth = 8
.Columns("H").ColumnWidth = 32
.Columns("I:J").ColumnWidth = 24
.Rows(1).RowHeight = 16
End With
'deleting all other worksheets except for "Results"
For lngCount = lngMax To 1 Step -1
If xlBook.Worksheets(lngCount).Name <> "Results" Then
xlBook.Worksheets(lngCount).Delete
End If
Next lngCount
'copying the query results from the recordset to the
excel file
With xlSheet
.Name = "Results"
.UsedRange.ClearContents
lngMax = mainRst.Fields.Count
For lngCount = lngMax To 1 Step -1
.Cells(1, lngCount).Value = mainRst.Fields(lngCount -
1).Name
Next lngCount
.Range("A2").CopyFromRecordset mainRst
End With
lngMax = xlBook.Worksheets.Count
'deleting all other worksheets except for "Results"
For lngCount = lngMax To 1 Step -1
If xlBook.Worksheets(lngCount).Name <> "Results" Then
xlBook.Worksheets(lngCount).Delete
End If
Next lngCount

xlBook.SaveAs strFile
MsgBox "Export Completed,Do you want to open your
file?", vbYesNo
If Yes Then
xlApp.Visible = True
xlApp.UserControl = True
xlBook.Visible = True
xlBook.UserControl = True
End If
If No Then
'stay in result window, clean up by closing objects
and ending excel process
xlApp.Visible = False
xlBook.Visible = False
xlSheet.Close True
xlBook.Close True
xlApp.Close True
Set xlSheet = Nothing
Set xlBook = Nothing
Set xlApp = Nothing
xlApp.Quit = True
End If
Exit_Handler:

'clean up
If Not mainRst Is Nothing Then
mainRst.Close
Set mainRst = Nothing
End If

If Not mainqdf Is Nothing Then
Set mainqdf = Nothing
End If

If Not maindb Is Nothing Then
Set maindb = Nothing
End If

Exit Sub

Err_Handler:
On Error Resume Next
Resume Exit_Handler
MsgBox Err.Description, vbExclamation, "Error No: " & Err.Number
Resume
End Sub
 
Private Sub cmdExpToExcel_Click()
Click to expand...
this line of codes doesnt work...:
------CODE-------
MsgBox "Do you want to open your file?", vbYesNoCancel
If vbYes Then
xlApp.Worksheets("Results") = Visible
xlApp.Visible = True
Else
'stay in window, do nothing
End If
-----End CODE----
Click to expand...

Try this (untested):

Dim vRetVal as Variant

vRetVal = MsgBox "Do you want to open your file?", vbYesNoCancel
If vRetVal = vbYes Then
xlApp.Worksheets("Results") = Visible
xlApp.Visible = True
Else
'stay in window, do nothing
End If
 
RD said:
Private Sub cmdExpToExcel_Click()
Click to expand...
this line of codes doesnt work...:
------CODE-------
Click to expand...
[quoted text clipped - 6 lines]
End If
-----End CODE----
Click to expand...

Try this (untested):

Dim vRetVal as Variant

vRetVal = MsgBox "Do you want to open your file?", vbYesNoCancel
If vRetVal = vbYes Then
xlApp.Worksheets("Results") = Visible
xlApp.Visible = True
Else
'stay in window, do nothing
End If
Click to expand...

RD tks but can u answer my post over at access.programmers.uk? i'm not going
to try ur suggestion until i get the multiple instance and exporting fixed
now that i will have to reuse the above code for all the different forms i
have so it doesnt make sense to copy and paste throughout the forms..

Thanks
Eric
 
Back
Top