Would someone mind looking at my code and tell me what's wrong?
When I start Access and run this code, it works fine. If I quit
Excel and run the code from Access again, it works fine except now
all I can see are the Excel title bar, tool bar and status bar - no
cells or scrollbars are visible. If I quit and re-open Access, the
same scenario repeats itself. Here's my code:
Private Sub XlAuto()
Dim xlApp As Object
Dim xlWbLink As Excel.Workbook
Dim xlWbSad As Excel.Workbook
Dim xlWsLink As Excel.Worksheet
Dim xlWsSad As Excel.Worksheet
Dim newSheet As Excel.Worksheet
If fIsAppRunning("Excel") = True Then
Set xlApp = GetObject(, "Excel.Application")
Else
Set xlApp = CreateObject("Excel.Application")
End If
xlApp.Visible = True
Set xlWbLink = xlApp.Workbooks.Open(strPath & "\SADLink.xls")
Set xlWsLink = xlWbLink.Worksheets("qry_R_SAD")
With xlWsLink
.Range("A1").Select
.Range(Selection, Selection.End(xlToRight)).Select
.Range(Selection, Selection.End(xlDown)).Select
Selection.Copy
End With
Set xlWbSad = xlApp.Workbooks.Open(strPath
& "\SAD_Access_Test.xls")
Set newSheet = Worksheets.Add
newSheet.Name = "Link"
Range("A1").Select
ActiveSheet.paste
Set xlWsSad = xlWbSad.Worksheets("ALL Deficiencies")
'================================================================
'DO SOME FORMATTING IN EXCEL.
'================================================================
xlApp.CutCopyMode = False
xlApp.DisplayAlerts = False
xlWbSad.Worksheets("Link").Delete
xlApp.DisplayAlerts = True
End Sub