HELP: Access VB Code to Activate Specific Excel Spread Sheet

[nagiese]

Good Evening,


I'm at a loss, I need my code to be able to activate the sheet in the excel workbook that I specify to past and format specific data too. The code below works fine if I have that sheet active upon the workbook opening. I need the code to automaticly go to the sheet I tell it to and then paste the data and beging the formatting. Can anyone help me fix this?

Sub GetJournal_Entry_Data_transfer_to_Excel()
'Step 1: Declare your Variables
    Dim MyConnect As String
    Dim MyRecordset As ADODB.Recordset
    Dim MyQueryDef As DAO.QueryDef
    Dim MyDatabase As DAO.Database
    Dim MySQL As String
    Dim MyRange As String
    Dim s As String
    
       
    Dim Db As Database
    Dim xl As Excel.Application
    Set xl = CreateObject("Excel.Application")
    Dim xlwkbk As Excel.Workbook
    Dim xlsheet As Excel.Worksheet
    Dim i As Integer
    Dim Cols_to_Insert As Single
    
'Step 2:Declare your connection string
    MyConnect = "Provider=Microsoft.ACE.OLEDB.12.0; Persist Security Info = False;Data Source= P:\FINANCE\Balance Sheet\Inventory\Project TAN\Project TAN.accdb; User ID = Admin;"
    
'Step 3: Build Your SQL Statement
    MySQL = "Select* From [mtb-TantasticJE's]Where [mtb-TantasticJE's].[Dscrptn_Text]='Culls_Stat34'and [mtb-TantasticJE's].[Co_Code]='1381'"
    
'Step 4: Instantiate and specify your recordset
    Set MyRecordset = New ADODB.Recordset
    MyRecordset.Open MySQL, CurrentProject.Connection
   
'Step 6: Instantiate Excel. If Excel isn't Loaded, Error # 429 occurs.
    Set xl = GetObject(, "Excel.Application")
   
'Step 7: Open TAN_JE_Export.xlsx
    Set xlwkbk = xl.Workbooks.Open("P:\FINANCE\Balance Sheet\Inventory\Project TAN\TAN_JE_Export.xlsm")
    Set xlsheet = xlwkbk.Worksheets("Culls_Stat34_1381")  :confused:This is the sheet that I need to be active, how do I tell the computer to make it active no matter what sheet the workbook opens on?
    xl.Visible = True
    xlwkbk.Windows(1).Visible = True
    xlsheet.Cells.ClearContents
        
'Step 8: Find First empty cell and use that to build a dynamic range
    With xlsheet
    xl.Range("A1").CopyFromRecordset MyRecordset
    .Columns("B:B").Select
    Selection.Insert Shift:=xlToRight
    .Range("C1").Select
    .Range("C3").Select
    Cols_to_Insert = 2
    Worksheets("Culls_Stat34_1381").Range("F1:" & Chr(Asc("F") + Cols_to_Insert) & "1").EntireColumn.Insert
    Cols_to_Insert = 0
    Worksheets("Culls_Stat34_1381").Range("J1:" & Chr(Asc("J") + Cols_to_Insert) & "1").EntireColumn.Insert
    Cols_to_Insert = 1
    Worksheets("Culls_Stat34_1381").Range("M1:" & Chr(Asc("M") + Cols_to_Insert) & "1").EntireColumn.Insert
    Cols_to_Insert = 0
    Worksheets("Culls_Stat34_1381").Range("Q1:" & Chr(Asc("Q") + Cols_to_Insert) & "1").EntireColumn.Insert
    Cols_to_Insert = 0
    Worksheets("Culls_Stat34_1381").Range("T1:" & Chr(Asc("T") + Cols_to_Insert) & "1").EntireColumn.Insert
    End With
    
'Step 9: save as text file & Close active recorset
    xl.Visible = True
    xl.Run "Export_Stat34_1381_culls_TXT_File"
    xlwkbk.Close (True)
    xl.WindowState = xlMinimized
    MsgBox "JE Has Been Exported to Excel & Text File"
'Step 10: Memory Clean up
    Set xlsheet = Nothing
    Set xlwkbk = Nothing
    Set xlApp = Nothing
    Set Db = Nothing
    MyRecordset.Close
    
End Sub

[ssanfu]

Just a guess.... Have you tried


xlsheet.Activate

or

xlwkbk.Worksheets("Culls_Stat34_1381").Activate

[pbaldy]

I have this in a working db:

Set xlSheet = xl.Worksheets("Limo")
xlSheet.Select

You probably need the Select, and I don't know if it's relevant but I'm using the Excel object rather than a workbook object.

[pbaldy]

Oops, too slow finding file and copying lines.

[nagiese]

Can't believe I missed that. Thanks a tone.