exporting a table to excel using a doCmd

[brow1726]

I have a module but I am unsure if it is completly correct.

    Dim rst As DAO.Recordset
    Dim ApXL As Object
    Dim xlWBk As Object
    Dim xlWSh As Object
    Dim fld As DAO.Field
    Dim strPath As String
    Const xlCenter As Long = -4108
    Const xlBottom As Long = -4107
    On Error GoTo err_handler
 

    strPath = strFilePath
 

    Set rst = CurrentDb.OpenRecordset(strTQName)
 
    Set ApXL = CreateObject("Excel.Application")
 

    Set xlWBk = ApXL.Workbooks.Open(strPath)

    ApXL.Visible = True
 
    Set xlWSh = xlWBk.Worksheets(strSheetName)
    xlWSh.Activate
 
    xlWSh.Range("A1").Select
 
    For Each fld In rst.Fields
        ApXL.ActiveCell = fld.Name
        ApXL.ActiveCell.Offset(0, 1).Select
    Next

    rst.MoveFirst
    xlWSh.Range("A2").CopyFromRecordset rst

    xlWSh.Range("1:1").Select

    With ApXL.Selection.Font
        .Name = "Arial"
        .Size = 12
        .Strikethrough = False
        .Superscript = False
        .Subscript = False
        .OutlineFont = False
        .Shadow = False
    End With

    ApXL.Selection.Font.Bold = True

    With ApXL.Selection
        .HorizontalAlignment = xlCenter
        .VerticalAlignment = xlBottom
        .WrapText = False
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .MergeCells = False
    End With

       ApXL.ActiveSheet.Cells.Select

       ApXL.ActiveSheet.Cells.EntireColumn.AutoFit

       xlWSh.Range("A1").Select
 
    rst.Close

    Set rst = Nothing
 
Exit_SendTQ2XLWbSheet:
    Exit Function

err_handler:
    DoCmd.SetWarnings True
    MsgBox Err.Description, vbExclamation, Err.Number
    Resume Exit_SendTQ2XLWbSheet
End Function

The problem that I seem to have is I'm on a network som when I try to put it on a drive there's no specific C:\ etc. so it dosent want to take it.:banghead:. If there is an issue with the code please let me know, if not please let me know what I am doing wrong. THANKS!!

 

[khodr]

Maybe you can refer to this link they have easier code:
http://www.accessibledatasolutions.com/articles11/AccessexportExcel.htm

 

[Trevor G]

In your code you are showing strPath and strFilePath nothing in your code is using this and you haven't set it. What are you trying to do, the DoCmd Transfer is one good idea. Do you want it to over writer data, add it to a workbook, create a workbook and show you the result then save it somewhere?

 

[brow1726]

I am trying to export the data to a specific tab in a spreadsheet, that I configured to create a report on the next tab.

 

[ajvol01]

Try this - I think this may help do what you want and is much simpler.

'     "Table" is the name of the table or query that has the data to export
'     "File" is the path & name of the Excel workbook being exported to
'     "Range" can be either a worksheet (tab) or a named range in a worksheet of the Excel workbook. 

    DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "Table", "File", True, "Range"

 

[brow1726]

If im on a network and it would be transefering to a share drive should this still work? For some reason I keep getting an error and Im unsure why.

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "TimeOffTracking", "[URL="file://\\Share-MyName\C_Baseball_Groups\Daily\All_Person_Folder\MJB"]\\Share-MyName\C_Baseball_Groups\Daily\All_Person_Folder\MJB[/URL] TimeOffTracking", True, "ReportingInfo"

Am I doing it right?

 

[ajvol01]

If im on a network and it would be transefering to a share drive should this still work? For some reason I keep getting an error and Im unsure why.

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "TimeOffTracking", "[URL="file://\\Share-MyName\C_Baseball_Groups\Daily\All_Person_Folder\MJB"]\\Share-MyName\C_Baseball_Groups\Daily\All_Person_Folder\MJB[/URL] TimeOffTracking", True, "ReportingInfo"

Am I doing it right?

I think it should still work ok regardless of network or local drive.

you need to add the .xls file extension

 

[brow1726]

I keep getting a runtime 3044 error. Invalid path. any thoughts?

 

[brow1726]

FYI Problem SOLVED!!!!!!! Thank you so much for your help!!!!

 

[ajvol01]

FYI Problem SOLVED!!!!!!! Thank you so much for your help!!!!

Glad I could help.