Using VBA to change the selected Excel tab (1 Viewer)

dmorgan20

Registered User.
Local time
Today, 06:39
Joined
Apr 4, 2018
Messages
39
I thought it would be easy, I must me missing something simple.

Im using the below to populate data in to excel, once the data is in I want to select the 'ID' tab and put a test comment in cell C5.

Code:
Public Sub ExportQuery(ByVal Query As String, ByVal ShowToUser As Boolean, ByVal TabName As String, ByVal TabCount As Integer, ByRef xlApp As Excel.Application, ByRef xlWB As Object)
'This will export a query to Excel and display it, or save it to a path if requested.
Dim rst As DAO.Recordset
Dim rstA As DAO.Recordset
Dim xlSh As Excel.Worksheet
Dim i As Long
Dim ID As String
Dim sql As String: sql = ""
    
    Set rst = CurrentDb.OpenRecordset(Query, dbOpenDynaset, dbSeeChanges)
    Set rstA = CurrentDb.OpenRecordset("SELECT DISTINCT [FIT ID] FROM CFR")
    With xlApp
      
   .Visible = ShowToUser
    
    If TabCount < 1 Then
            Set xlWB = .Workbooks.Add
            Set xlSh = xlWB.Worksheets(xlWB.Worksheets.Count)
        With xlSh
            .Name = "ID"
            .Select
            .Range("A2") = "FullFITID"
            .Range("A3").CopyFromRecordset rstA
            Set xlSh = xlWB.Worksheets(xlWB.Worksheets.Count)
            xlWB.Worksheets.Add After:=xlSh
            .Select
        End With
    Else
        Set xlSh = xlWB.Worksheets(xlWB.Worksheets.Count)
        xlWB.Worksheets.Add After:=xlSh
    End If
    
    End With
    
    Set xlSh = xlWB.Worksheets(xlWB.Worksheets.Count)
    With xlSh
        .Name = TabName
        .Select
        .Range("A2").CopyFromRecordset rst
    
         For i = 1 To rst.Fields.Count
             .Cells(1, i).Value = rst.Fields(i - 1).Name
         Next i
    
        With xlSh
            xlWB.Worksheets(1).Select
            .Range("C5") = "Test"
        End With


this is the section I need to tidy up:

Code:
        With xlSh
            xlWB.Worksheets(1).Select
            .Range("C5") = "Test"
        End With

I have changed .Select to .Activate, I have also changed the word 'Worksheets to 'sheet' and 'sheets'. Browsing online many offer the solution of which none appear to work.

The test in Range.C5 always appears in the wrong tab
 

Ranman256

Well-known member
Local time
Today, 09:39
Joined
Apr 9, 2015
Messages
4,339
cant you use:

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12, "qsData2Export", vFile, True,"Test"
 

dmorgan20

Registered User.
Local time
Today, 06:39
Joined
Apr 4, 2018
Messages
39
Because of the way the application has been built I don't believe that method is as useable as I would like
 

Gasman

Enthusiastic Amateur
Local time
Today, 13:39
Joined
Sep 21, 2011
Messages
14,041
That will likely be due to the sheets not ordered as you think they are.?
I would use the name of the tab or specify where you are adding the sheet.


Code:
Sheets("RawData").Select
Code:
Sheets.Add After:=Sheets(1)
Sheets.Add Before:=Sheets(1)
I thought it would be easy, I must me missing something simple.

Im using the below to populate data in to excel, once the data is in I want to select the 'ID' tab and put a test comment in cell C5.

Code:
Public Sub ExportQuery(ByVal Query As String, ByVal ShowToUser As Boolean, ByVal TabName As String, ByVal TabCount As Integer, ByRef xlApp As Excel.Application, ByRef xlWB As Object)
'This will export a query to Excel and display it, or save it to a path if requested.
Dim rst As DAO.Recordset
Dim rstA As DAO.Recordset
Dim xlSh As Excel.Worksheet
Dim i As Long
Dim ID As String
Dim sql As String: sql = ""
    
    Set rst = CurrentDb.OpenRecordset(Query, dbOpenDynaset, dbSeeChanges)
    Set rstA = CurrentDb.OpenRecordset("SELECT DISTINCT [FIT ID] FROM CFR")
    With xlApp
      
   .Visible = ShowToUser
    
    If TabCount < 1 Then
            Set xlWB = .Workbooks.Add
            Set xlSh = xlWB.Worksheets(xlWB.Worksheets.Count)
        With xlSh
            .Name = "ID"
            .Select
            .Range("A2") = "FullFITID"
            .Range("A3").CopyFromRecordset rstA
            Set xlSh = xlWB.Worksheets(xlWB.Worksheets.Count)
            xlWB.Worksheets.Add After:=xlSh
            .Select
        End With
    Else
        Set xlSh = xlWB.Worksheets(xlWB.Worksheets.Count)
        xlWB.Worksheets.Add After:=xlSh
    End If
    
    End With
    
    Set xlSh = xlWB.Worksheets(xlWB.Worksheets.Count)
    With xlSh
        .Name = TabName
        .Select
        .Range("A2").CopyFromRecordset rst
    
         For i = 1 To rst.Fields.Count
             .Cells(1, i).Value = rst.Fields(i - 1).Name
         Next i
    
        With xlSh
            xlWB.Worksheets(1).Select
            .Range("C5") = "Test"
        End With
this is the section I need to tidy up:

Code:
        With xlSh
            xlWB.Worksheets(1).Select
            .Range("C5") = "Test"
        End With
I have changed .Select to .Activate, I have also changed the word 'Worksheets to 'sheet' and 'sheets'. Browsing online many offer the solution of which none appear to work.

The test in Range.C5 always appears in the wrong tab
 
Last edited:

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 21:39
Joined
May 7, 2009
Messages
19,169
remove it from inside the with... end with
Code:
with xlWB.Worksheets(1)
            .Range("C5").Value = "Test"
end with
]
 
Last edited:

Users who are viewing this thread

Top Bottom