Results 1 to 9 of 9
  1. #1
    xopherira is offline Advanced Beginner
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jul 2015
    Location
    Indiana
    Posts
    53

    wrong excel worksheet opening

    I am suing the following code to export to a specific cell on a specific worksheet. When I run the incorrect worksheet is being displayed. Code is exporting to correct workbook and sheet, but not opening the correct worksheet after I run code. Does anyone see anything wrong with code? When I ran this yesterday it worked, I can't see what changed or happened.



    Code:
    Public Sub countRecords1107()
    Dim rsCount As Integer
    Dim db As DAO.Database
    Dim rst As DAO.Recordset
    Dim queryName As String
    Dim objXL As Object
    Dim objWB As Object
    Dim objWS As Object
    Set objXL = CreateObject("Excel.Application")
    Set objWB = objXL.Workbooks.Open("L:\Metrics\Dashboard\Metrics_Dashboard_v1001.xlsx")
    Set objWS = objWB.Worksheets("DashBoard")
    queryName = "NAME"
    Set db = CurrentDb
    Set rst = db.OpenRecordset(queryName)
       
    'Source Data Records
    If Not (rst.EOF And rst.BOF) Then
        Do While Not rst.EOF
        
        rsCount = rst.RecordCount
        Debug.Print rsCount
        'rst.Edit
        'rst.Update
        rst.MoveNext
        Loop
          
        rst.Close
        Set rst = Nothing
        Set db = Nothing
    End If
    MsgBox rsCount, vbOKOnly, "Count"
    With objWS
    .Cells(3, 2).Value = rsCount
    End With
    objXL.Visible = True
    End Sub

  2. #2
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    BE SURE YOU OPEN THE WORKBOOK

    ''NOT SURE IF THIS WORKS:
    Set objWB = objXL.Workbooks.Open("L:\Metrics\Dashboard\Metrics _Dashboard_v1001.xlsx")

    ''so change to:

    objXL.Workbooks.Open("L:\Metrics\Dashboard\Metrics _Dashboard_v1001.xlsx")
    set objWB = objXL.activeworkbook
    Set objWS = objWB.Worksheets("DashBoard")

  3. #3
    xopherira is offline Advanced Beginner
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jul 2015
    Location
    Indiana
    Posts
    53
    Copied suggestion and still get wrong worksheet. Could it be a setting in the Excel Workbook?

  4. #4
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    this is not possible. If you open the exact path given, "L:\Metrics\Dashboard\Metrics _Dashboard_v1001.xlsx
    it CANT open a different sheet.

  5. #5
    ssanfu is offline Master of Nothing
    Windows XP Access 2000
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    You might look at this site:

    Ken's ACCESS Examples and Information for Working With EXCEL Workbook Files
    http://www.accessmvp.com/kdsnell/EXCEL_MainPage.htm

  6. #6
    Micron is offline Virtually Inert Person
    Windows 7 32bit Access 2007
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    If the data goes to the right sheet in the right workbook, then I'm betting the sheet you see when the workbook becomes visible is the one that was active the last time the workbook was saved. Open the file, activate your target sheet, save and close. Try again.

  7. #7
    xopherira is offline Advanced Beginner
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jul 2015
    Location
    Indiana
    Posts
    53
    That is correct and what is happening Micron. Is there any way to overide this?

  8. #8
    Micron is offline Virtually Inert Person
    Windows 7 32bit Access 2007
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    I'm no Excel vba guru - everything I've ever done in Excel came from the internet. Not sure of best or even right way to activate the required sheet. Try this:
    after
    Set objWS = objWB.Worksheets("DashBoard")
    put
    objWB.Worksheets("DashBoard").Activate
    I kind of doubt that objWs.Activate will work as it might start too deep in the object model. I'd be curious to know if it works though.

  9. #9
    ssanfu is offline Master of Nothing
    Windows XP Access 2000
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    You should just use
    Code:
    objWB.Activate
    objWS is already assigned as "objWB.Worksheets("DashBoard")"





    I reworked your code a little.
    Code:
    Public Sub countRecords1107()
        Dim rsCount As Integer
        Dim db As DAO.Database
        Dim rst As DAO.Recordset
        Dim queryName As String
        Dim objXL As Object
        Dim objWB As Object
        Dim objWS As Object
        Dim blnEXCEL As Boolean
    
    
        ' Establish an EXCEL application object
        On Error Resume Next
        'see if Excel is running
        Set objXL = GetObject(, "Excel.Application")
    
        If Err.Number <> 0 Then
            Set objXL = CreateObject("Excel.Application")
            blnEXCEL = True
        End If
        Err.Clear
        On Error GoTo 0
    
        ' this is the location of my test Excel file
        '    Set objWB = objXL.Workbooks.Open("C:\Accmdb\testtest\Metrics_Dashboard_v1001.xlsx")
        Set objWB = objXL.Workbooks.Open("L:\Metrics\Dashboard\Metrics_Dashboard_v1001.xlsx")
    
        Set objWS = objWB.Worksheets("DashBoard")
        'activate sheet "Dashboard"
        objWS.Activate
    
        rsCount = 0
    
        'my query name - change to your query name
        queryName = "qryCGF"   '<<= do not use "Name" as a query name
        Set db = CurrentDb
        Set rst = db.OpenRecordset(queryName)
    
        'Source Data Records
        If Not (rst.EOF And rst.BOF) Then
            rst.MoveLast
            rsCount = rst.RecordCount
            Debug.Print rsCount
        End If
        rst.Close
    
        msgbox rsCount, vbOKOnly, "Count"
    
        With objWS
            .Cells(3, 2).Value = rsCount
        End With
        objXL.Visible = True
    
    
        ' close the EXCEL file and save the new data
        objWB.Close True    '<<= add a breakpoint here if you want to see the spreadsheet before it is saved and closed
        DoEvents      ' for timing issues when saving workbook
    
        Set objWS = Nothing
        Set objWB = Nothing
    
        If blnEXCEL = True Then
            objXL.Quit
        End If
        Set objXL = Nothing
    
    
        Set rst = Nothing
        Set db = Nothing
    End Sub

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Export to excel but clear worksheet first
    By mercapto in forum Import/Export Data
    Replies: 3
    Last Post: 05-17-2015, 11:57 AM
  2. Hyperlink to a specific Excel Worksheet
    By perikillo in forum Access
    Replies: 3
    Last Post: 11-06-2014, 05:07 PM
  3. Replies: 1
    Last Post: 08-09-2012, 12:06 PM
  4. VBA to Import Excel Worksheet
    By bdaniel in forum Programming
    Replies: 2
    Last Post: 11-23-2010, 10:53 AM
  5. Importing the second excel worksheet
    By geoffwbailey in forum Programming
    Replies: 1
    Last Post: 06-25-2010, 12:16 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums