Logo 
Search:

MS Office Forum

Ask Question   UnAnswered
Home » Forum » MS Office       RSS Feeds

Problem with Excel Automation from Access

  Asked By: Muaz    Date: Mar 07    Category: MS Office    Views: 749
  

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

Share: 

 

No Answers Found. Be the First, To Post Answer.

 
Didn't find what you were looking for? Find more on Problem with Excel Automation from Access Or get search suggestion and latest updates.




Tagged: