THANKYOU! THANKYOU! THANKYOU!
eer3 - thanks a heap! Once I ammended the code so that every excel operation was done under an app object, everything worked perfectly. If anyones interested, the code now reads:
Dim objExcel As Object
Set objExcel = New Excel.Application
objExcel.Workbooks.Add
objExcel.Application.Visible = False
'Get the field names
For icount = 0 To Data2.Recordset.Fields.Count - 1
objExcel.Cells(1, icount + 1).Value = Data2.Recordset.Fields(icount).Name
Next
objExcel.Range("A2").CopyFromRecordset Data2.Recordset
objExcel.Cells(1, 1).CurrentRegion.EntireColumn.AutoFit
objExcel.Sheets("Sheet1").Name = strReportName
objExcel.ActiveWorkbook.SaveAs FileName:= _
"\\Nacwks60\Share\zz_TSG Share\TUC\" & _
strReportName & " - " & Format((datMostRecentDate), "ddmmyy")
objExcel.ActiveWorkbook.Close
objExcel.Application.Quit
Set objExcel = Nothing
Thanks again - you have saved me the cost of a new PC and window pane.
A way to troubleshoot closing Excel
This may help you out. What I did was start with the basics.
Make sure Excel is closed.
Open Excel from VB.
Verify Excel is in the running programs list.
Close Excel from VB. Do not run any additional code.
Make sure Excel is no longer in the list of running programs.
If this worked. Then add some more code. Does Excel still open and close when expected? If so then add more code until you find the specific code that causes Excel to not close properly.
I hope this helps.
run-time error due to excel already open
I am trying to run a ADO query and then populate excel. My app works fine the first time but if I select another query I get the following error:
Run-time error '1004';
Method 'Range' of object '_Global' failed
Anyone seen this before?
Here is my code in my command button:
Private Sub cmdQuery_Click()
'=======================================================
'
'=======================================================
Dim adoCon As ADODB.Connection
Dim adoRs As ADODB.Recordset
Dim xl As Excel.Application
Dim xlw As Excel.Workbook
Dim strCon As String
Dim strSQL As String
Dim intCnt As Integer
Dim iCnt As Integer
Dim irCnt As Integer
Dim strDate As String
Dim strTime As String
'Set Global Timer Count to zero
giTimerCnt = 0
strDate = Format(Date, "mm-dd-yy")
strTime = Format(Time, "h-m-s")
If cboQry.Text = "" Then
lblStatusOutput.Caption = "No query selected"
Exit Sub
End If
cmdQuery.Enabled = False
'Fill array with field names
'also populate giCnt indicating number of fields starting at 1
sbFillArray
lblStatusOutput.Caption = "Query selected"
strCon = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & strDBLocation & ";Persist Security Info=False"
Set adoCon = New ADODB.Connection
With adoCon
.ConnectionString = strCon
.Open
End With
Set adoRs = New ADODB.Recordset
strSQL = "SELECT * FROM [" & cboQry.Text & "]"
With adoRs
.CursorLocation = adUseClient
.ActiveConnection = adoCon
.Open strSQL
If .EOF = True And .BOF = True Then
frmQry.lblStatusOutput.Caption = "No records found."
irCnt = 0
Else
.MoveLast
.MoveFirst
frmQry.lblStatusOutput.Caption = "Records found."
irCnt = .RecordCount
End If
End With
'Open excel workbook
Set xl = New Excel.Application
Set xlw = xl.Workbooks.Add
xl.Visible = True
xlw.Sheets("Sheet1").Select
'Enter Field/Column Names on first row in excel file
For iCnt = 1 To giCnt
xlw.Application.Cells(1, iCnt).Value = saFields(iCnt)
Next iCnt
For intCnt = 2 To irCnt + 1
For iCnt = 1 To giCnt
xlw.Application.Cells(intCnt, iCnt).Value = (adoRs.Fields(saFields(iCnt)))
Next iCnt
adoRs.MoveNext
Next intCnt
'Makes top row bold
If giCnt = 1 Then
Range("A1").Select
Selection.Font.Bold = True
Range("A1").Select
Else
'This always errors on the second query run in a row.
'Error is:
'Run-time error '1004';
'Method 'Range' of object '_Global' failed
Range("A1:" & gsaAlphabet(giCnt - 1) & "1").Select
Selection.Font.Bold = True
Range("A1").Select
End If
'Autoresize all entered fields
For iCnt = 1 To giCnt
Columns(gsaAlphabet(iCnt - 1) & ":" & gsaAlphabet(iCnt - 1)).EntireColumn.AutoFit
Next iCnt
ChDir "C:\"
strXLSFile = "C:\QryResults" & strDate & "-" & strTime & ".xls"
xlw.SaveAs strXLSFile, xlNormal
xlw.Close
xl.Quit
Set xlw = Nothing
Set xl = Nothing
adoRs.Close
adoCon.Close
'Send email
'sbSendMail
frmQry.lblStatusOutput.Caption = "Finished processing."
frmQry.cmdQuery.Enabled = True
End Sub
autoresize and bold problem
I believe I found the problem. If I comment out the following code it will close just fine (from the code above). The first part makes the headings bold. Second part is autoresizing the fields.
Has anyone seen an issue with this before?
''Makes top row bold
'If giCnt = 1 Then
' Range("A1").Select
' Selection.Font.Bold = True
' Range("A1").Select
'Else
' 'This always errors on the second query run in a row.
' 'Error is:
' 'Run-time error '1004';
' 'Method 'Range' of object '_Global' failed
' Range("A1:" & gsaAlphabet(giCnt - 1) & "1").Select
' Selection.Font.Bold = True
' Range("A1").Select
'End If
''Autoresize all entered fields
'For iCnt = 1 To giCnt
' Columns(gsaAlphabet(iCnt - 1) & ":" & gsaAlphabet(iCnt - 1)).EntireColumn.AutoFit
'Next iCnt
Wow! This is an old post!
I think you missed a few explicit references to the Excel object. Every time you reference anything in Excel from VB, do it from the top level object down. Notice that this line of code (and many others in your example), do not do this.
For example...
Range("A1").Select
Selection.Font.Bold = True
should be...
Xl.Range("A1").Select
Xl.Selection.Font.Bold = True
See what I mean?
Fix all of those orphaned references first, then let us know how it comes out! Good luck!