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.
this is the section I need to tidy up:
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
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