Can find file

[Lou_Reed]

I changed the path in the VBA code for the export of an Access table to Excel. It has the correct path. I also changed the prepared a file to the name
testbook-rec.xlsm.



But when I export it to Excel it says that it cannot find the file testbook-rec.xlsm. It is in the Desktop directory where it should be.

Also since I am now selecting the file from a list how do I integrate that into the VBA code so I can d away with the path?

Any help appreciated. Thanks in advance.

Respectfully,

Lou Reed

[ssanfu]

I modified your browse code. You have "AllowMultiSelect" set to FALSE, so there is no need to loop to get the path/file name selected. I also added the WITH...END WITH construct:

Private Sub btnBrowse_Click()
    Dim diag As Office.FileDialog
    Dim item As Variant

    Set diag = Application.FileDialog(msoFileDialogFilePicker)
    With diag
        .AllowMultiSelect = False
        .Title = "Please select an Excel Spreadsheet"
        .Filters.Clear
        .Filters.Add "Excel Spreadsheets", "*.xls, *.xlsx, *.xlsm"

        If .Show Then
            Me.txtFileName = .SelectedItems(1)
        End If
    End With

End Sub

Then I modified your export code. I added a check to ensure an export path/file name was selected. Since the export path/file name is in the text box control, I changed the workbooks open line to use the control Me.txtFileName.
Last, I closed the record set and destroyed the objects created in the code.

Private Sub ExportTabletoExcel_Click()
    On Error GoTo ExportError

    Dim db As DAO.Database
    Dim rs As DAO.Recordset
    Dim xl As Excel.Application
    Dim ws As Worksheet
    Dim fld As Field, FieldCount As Integer, J As Integer
    Dim rownum As Long

    If Len(Me.txtFileName & vbNullString) > 0 Then

        Set xl = New Excel.Application
        xl.Visible = False

        xl.Workbooks.Open (Me.txtFileName)
        Set ws = xl.Worksheets("Expanded Tracker")
        ws.Select

        Set db = CurrentDb

        Set rs = db.OpenRecordset(Me!TableList)
        rownum = 5

        rs.MoveFirst

        ' FieldCount contains the number of fields in the recordset
        FieldCount = rs.Fields.Count

        '
        ' first row of the spreadsheet contains the fieldnames, making it a header row
        For J = 1 To FieldCount
            ws.Cells(rownum, J).Value = rs.Fields(J - 1).Name   'Use j-1 because recordset field index is zero-based
        Next J

        '
        ' Now copy the data from the recordset to the Excel spreadshheet rows...
        Do While Not rs.EOF
            rownum = rownum + 1
            For J = 1 To FieldCount
                ws.Cells(rownum, J).Value = rs.Fields(J - 1).Value  ' Use j-1 because recordset field index is zero-based
            Next J

            rs.MoveNext
        Loop
        MsgBox "Finished"

    Else
        MsgBox "Error ..... No Export Excel path/file selected."
    End If

ExportError_Exit:
    'clean up before exiting
    On Error Resume Next

    xl.ActiveWorkbook.Close (True)
    xl.Quit
    Set xl = Nothing

    rs.Close
    Set rs = Nothing
    Set db = Nothing

    Exit Sub

ExportError:
    If Err.Number = 3078 Then
        MsgBox "The selected table " & Me!TableList & " is not valid"
    Else
        MsgBox "Error " & Err.Number & ":   " & vbCrLf & Err.Description & vbCrLf & "occurred on export of table " & Me!TableList
    End If

    Resume ExportError_Exit

End Sub

[Lou_Reed]

Thanks, I will give it a try. Thanks you very much.

Respectfully,

Lou Reed

PS I am always amazed at my ignorance of basic programing. I have programmed for years and such things
as what you pointed have little to do with VBA, and db programming. They are basic programming concepts.