Tab Order, export to Excel

Experts,

I am exporting data to excel.
The tab being exported seem to not be the FIRST tab.
How can I make the export always the first tab?

Private Sub cmdExportExcel_Click()

    Dim shtName As String
    shtName = Format(Date, "yyyy-mmm-dd") & "Export"
    DoCmd.TransferSpreadsheet acExport, , "qryLCIssuedFAC_Available_FUTURE", "C:\Documents and Settings\User\My Documents\Work\Company\Banks\Outstanding reports\Export_db_FacAvailMnth.xlsx", True, shtName
   
    Dim stAppName As String

    stAppName = "Excel.exe " & Chr(34) & "c:\Documents and Settings\User\My Documents\Work\Company\Banks\Outstanding reports\Export_db_FacAvailMnth.xlsx"
    Call Shell(stAppName, 1)

End Sub
pdvsaProject financeAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Rey Obrero (Capricorn1)Commented:
you cannot do that from the export command line.
* when the excel file opened, just select the sheet (tab) and move it to the first position.
* if you need to do this automatically, you will need additional vba codes.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
pdvsaProject financeAuthor Commented:
Ok. Thanks...
0
mbizupCommented:
You can do this through Excel Automation (you need a reference to MS Excel)

This is a fairly generic routine I use:

Function GenericExcelReport(sSelect As String, sTitle As String) As Boolean
'On Error GoTo ErrGenericExcelReport

    GenericExcelReport = False

    Dim db As Database
    Dim rsGeneric As DAO.Recordset
    
    Set db = CurrentDb
    Set rsGeneric = db.OpenRecordset(sSelect, dbOpenDynaset, dbSeeChanges)
    
    Dim ColCount As Integer
    Dim col As Integer
    Dim row As Integer
        
    Dim oExcel As Excel.Application
    Dim oWB As Excel.Workbook
    Dim oWS As Excel.Worksheet
    
    'open the spreadsheet for editing
'On Error GoTo Excel_EH
    If oExcel Is Nothing Then Set oExcel = New Excel.Application
    oExcel.Visible = True
    Set oWB = oExcel.Workbooks.Add
    Set oWS = oExcel.ActiveSheet
    
'On Error GoTo ErrGenericExcelReport


    DoEvents
    
    ColCount = rsGeneric.Fields.Count
    row = 1
    col = 0
    With oWS
                     
        If (sTitle & "" <> "") Then row = row + 2       'set up for the title if there is one
        
        .Rows(row).Font.Bold = True
        
        'set up the Column Headings and
        Do While (col < ColCount)
            .Cells(row, col + 1).Value = rsGeneric.Fields(col).Name

            'check if this field type is Date/Time
            If rsGeneric.Fields(col).Type = 8 Then
                'next line requires more checking, the property may not exist for each date field
                'If (rsGeneric.Fields(col).Properties("Format") = "Short Date") then .Columns(col + 1).NumberFormat = "m/d/yyyy;@"
                .Columns(col + 1).NumberFormat = "[$-409]m/d/yy h:mm AM/PM;@"
            End If
            
            'check if this field type is Currency
            If rsGeneric.Fields(col).Type = 5 Then
               .Columns(col + 1).NumberFormat = "$#,##0.00"
            End If
                
            col = col + 1
        Loop
        
        'output the data
        If rsGeneric.EOF Then
            row = row + 1
            col = 0
            .Cells(row, col + 1).Value = "There are no records to display."
            .Range(.Cells(row, col + 1), .Cells(row, ColCount)).Merge
        End If
        
        Do While Not rsGeneric.EOF
            row = row + 1
            col = 0
            Do While (col < ColCount)
                .Cells(row, col + 1).Value = rsGeneric.Fields(col)
                col = col + 1
            Loop
                        
            rsGeneric.MoveNext
        Loop
        
        .Cells.EntireColumn.AutoFit
        
        If (sTitle & "" <> "") Then
            row = 1
            col = 0
            .Rows(row).Font.Bold = True
            .Cells(row, col + 1).Value = sTitle
            .Cells(row, col + 1).WrapText = False
            .Cells(row, col + 1).Font.Size = 14
        End If
        
    End With

    GenericExcelReport = True

Exit Function

Excel_EH:
    DoEvents
    DoEvents
    MsgBox "An error occurred. Please close excel and try running the process again.", vbExclamation, "No Page Break Inserted"
Exit Function

ErrGenericExcelReport:
    MsgBox "An error occured while attempting to generate the report." & vbCrLf & Err.Number & ": " & Err.Description
Exit Function
    
End Function 

Open in new window


Called like this:

GenericExcelReport "SELECT * FROM YourTableOrQuery", "Report Title"

Open in new window



Excel automation can be pretty verbose, but it offers you a lot more flexibility.
0
pdvsaProject financeAuthor Commented:
thank you Mbizup....i will tinker with that....
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.