pdvsa
asked on
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\Ban ks\Outstan ding reports\Export_db_FacAvail Mnth.xlsx" , True, shtName
Dim stAppName As String
stAppName = "Excel.exe " & Chr(34) & "c:\Documents and Settings\User\My Documents\Work\Company\Ban ks\Outstan ding reports\Export_db_FacAvail Mnth.xlsx"
Call Shell(stAppName, 1)
End Sub
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_
Dim stAppName As String
stAppName = "Excel.exe " & Chr(34) & "c:\Documents and Settings\User\My Documents\Work\Company\Ban
Call Shell(stAppName, 1)
End Sub
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
You can do this through Excel Automation (you need a reference to MS Excel)
This is a fairly generic routine I use:
Called like this:
Excel automation can be pretty verbose, but it offers you a lot more flexibility.
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
Called like this:
GenericExcelReport "SELECT * FROM YourTableOrQuery", "Report Title"
Excel automation can be pretty verbose, but it offers you a lot more flexibility.
ASKER
thank you Mbizup....i will tinker with that....
ASKER