CODEWITHSUNDEEP
ms-accessms-access-2007vba
Chuck
Chuck

Reputation: 1226

Trying to save a table export with file dialog box

Private Sub Command42_Click()
    Dim f As Object
    Dim strFile As String
    Dim strFolder As String
    Dim varItem As Variant
    Dim P As String
    Set f = Application.FileDialog(msoFileDialogSaveAs)
        f.AllowMultiSelect = False
        f.InitialFileName = "H:\Chuck M\UCPP\"
        If f.Show Then
        For Each varItem In f.SelectedItems
                strFile = Dir(varItem)
                strFolder = Left(varItem, Len(varItem) - Len(strFile))
                P = strFolder & strFile
                DoCmd.TransferText acExportDelim, "UCCP Export Specification", "MOM_Table Export", P
            Next
        End If
        Set f = Nothing
End Sub

I currently have this however I am getting an error "Method 'FileDialog' of object '_Application' failed.

I have done some google searching but couldn't find much that helped me.

i just want this box to allowed me to save my comma delimited table export to a location I want and allow us to name it so we don't get each daily export confused.

Any help in this matter would be greatly appreciated.

Upvotes: 1

Views: 2365

Answers (1)

Gord Thompson
Gord Thompson

Reputation: 123799

You can receive that error if:

  • you don't have Option Explicit at the top of your module,
  • your VBA project does not have the reference to the Office Object Library, and
  • you try to use the msoFileDialogSaveAs constant name.

To fix this, either...

(a) verify that your VBA project has a valid reference for...

Microsoft Office nn.n Object Library

...where nn.n corresponds to the version of Office you have installed (e.g., it is 14.0 for Office 2010).

(b) Failing that, you can use

Dim f As Object
Const msoFileDialogSaveAs = 2
Set f = Application.FileDialog(msoFileDialogSaveAs)

TIP: Always use Option Explicit for every module you create. It will save you many a headache. To make it the default, choose Tools > Options in the VBA editor and select "Require Variable Declaration". That won't add Option Explicit to any existing modules, however; you'll have to do that yourself.

Upvotes: 2

Related Questions