Solved

Access VB FileDialog Close Issue

Posted on 2004-08-18
3
491 Views
Last Modified: 2008-02-01
This is probably pretty easy, but here goes:  I am using the following code to open the file dialog window, select a file, and import it into my access database.  This works fine.  However, when I hit cancel on the file dialog window, I get the following error:

Run Time error 2522:  This action or method requires a filename argument.

Here is the code:

Option Compare Database
Option Explicit
Dim Filename As String
Dim strPath As String

Private Sub A2ExcelImport_Click()
    getExcelFileA2

     DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9 _
     , "A2Purchases", Filename, True
   
End Sub

Sub getExcelFileA2()
        Dim result As Variant
        With Application.FileDialog(msoFileDialogFilePicker)
        .Title = "Select File"
        .Filters.Add "All Files", "*.*"
        .Filters.Add "XLS", "*.xls"
        .FilterIndex = 3
        .AllowMultiSelect = False
        .InitialFileName = CurrentProject.Path
        result = .Show
        If (result <> 0) Then
            Filename = Trim(.SelectedItems.Item(1))
        End If
                     
    End With
0
Comment
Question by:billbucy
3 Comments
 
LVL 14

Accepted Solution

by:
ragoran earned 250 total points
ID: 11836032
Change getExcelFileA2 to a fucntion that will return the filename isntead of using a global variable (very bad coding, but is a typical error for beginers)  You can then test the string before doing the export:


Private Sub A2ExcelImport_Click()
   dim Filename

    filename = getExcelFileA2()
   
    if len(filename) > 0 then
        DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9 _
        , "A2Purchases", Filename, True
    end if
End Sub

function getExcelFileA2() as string
        Dim result As Variant
        With Application.FileDialog(msoFileDialogFilePicker)
        .Title = "Select File"
        .Filters.Add "All Files", "*.*"
        .Filters.Add "XLS", "*.xls"
        .FilterIndex = 3
        .AllowMultiSelect = False
        .InitialFileName = CurrentProject.Path
        result = .Show
        If (result <> 0) Then
            getExcelFileA2 = Trim(.SelectedItems.Item(1))
        else
            getExcelFileA2 = ""
        End If
                     
    End With

end function
0
 

Author Comment

by:billbucy
ID: 11836094
Thanks for the quick respone!
0
 
LVL 34

Expert Comment

by:flavo
ID: 11836096
if the user hits cancel then the variable FileName will return either Null or False i think (trying to remember - excel returns False i know that)

so, we need to test they didnt hit cancel like this

If IsNull(FileName) then
   Msgbox "No File!!!"
   Exit sub
else
'do it!
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9 _
     , "A2Purchases", Filename, True
end if


or it may be..................

If FileName = "False" then
   Msgbox "No File!!!"
   Exit sub
else
'do it!
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9 _
     , "A2Purchases", Filename, True
end if


Dave
0

Featured Post

The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

The first two articles in this short series — Using a Criteria Form to Filter Records (http://www.experts-exchange.com/A_6069.html) and Building a Custom Filter (http://www.experts-exchange.com/A_6070.html) — discuss in some detail how a form can be…
A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …

840 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question