Export Question

[Huey462]

I have a saved export called Export Temp that will save a database to "C:\Junk\Safety Self Inspection.xlsx". Is it possible to replace this predefined path with two entries from the following database?

Database Name:
Export File Location

Column Name:
Export Name 1

Ideally I'd like to leave these as two separate entries, but if they need to be combined so be it.

I have tried to use the VBA code to manually export using the following code, but could not figure out how to have it export is as an XLSX file that would keep the formatting.

DoCmd.TransferSpreadsheet acExport, 10, "Master", FPath & FName, True, , False

 

[sxschech]

Are you saying that the code works, but doesn't save with formatting or that the code doesn't work using the FPath & FName or both?

How are you getting the values into FPath and FName?
When you do FPath & FName is there a "\" between them?

For example, if FPath = "C:\Junk"
and FName = "Safety Self Inspection.xlsx"
then FPath & FName = "C:\JunkSafety Self Inspection.xlsx"
which won't work.
You would need to do:

FPath & "\" & FName to get the result to = "C:\Junk\Safety Self Inspection.xlsx"

 

[Huey462]

The code does work, but doesn't export with any formatting. This is fine, but I think the export with formatting looks more professional.

I was trying to see if it was possible to use a similar reference in the saved export so the end user can pick the destination
(just an example...I have no idea how this would auctually be formatted.)
Database![Export File Location]![Export Location] (this would provide the C:\Junk\)Database![Export File Location]![Export Name 1] (this would provide the file name)

 

[sxschech]

I'm not sure about the formatting as I'm still using Access 2003 and looks like you are exporting to a newer version of Excel. Instead of transfer spreadsheet have you tried "outputto" maybe that would work.

DoCmd.OutputTo acOutputTable, "Master" acFormatXLS, FPath & FName, True

If "Master" is a query than you would use acOutputQuery

 

[Huey462]

I've decided to go with function over form on this one. Here is the code incase it can help anyone else.

Private Sub ShopOpBackup_Click()

'Defines variables
    Dim rs As DAO.Recordset
    Dim FPath As String
    Dim FName As String
    Set rs = CurrentDb.OpenRecordset("SELECT t.[File Location], t.[File Name 1] FROM [Export Information] t", dbOpenDynaset)
' Pulls info from table
    FPath = rs.Fields("File Location").Value
    FName = rs.Fields("File Name 1").Value

'Verifies to see if export directory exists.  If it does not already exist, it is created.
    If Dir(FPath & "\") = "" Then
       MkDir (FPath)
    Else
    'do nothing for the "C:\Junk" directory already exists
    End If

' Error handeling routine
    On Error GoTo Errorhand

' Exports the database to an excel file
    DoCmd.TransferSpreadsheet acExport, 10, "Shop Operation", FPath & "\" & FName, True, , False

If ErrorCount > 0 Then GoTo Errorhand2 Else GoTo Errorhand3

Errorhand:
     ErrorCount = ErrorCount + 1
     Resume Next

Errorhand2:
    MsgBox ("Custom export was not complete."), vbCritical, "//// ERROR ////"
    GoTo FinalEnd
    
Errorhand3:
    MsgBox ("Successfully exported to " & FPath & "\" & FName), vbInformation, "!!!! SUCCESS !!!!"
    GoTo FinalEnd

FinalEnd:
End Sub