Solved

Access VB FileDialog Close Issue

Posted on 2004-08-18
3
490 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

Live: Real-Time Solutions, Start Here

Receive instant 1:1 support from technology experts, using our real-time conversation and whiteboard interface. Your first 5 minutes are always free.

Question has a verified solution.

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

Experts-Exchange is a great place to come for help with solutions for your database issues, and many problems are resolved within minutes of being posted.  Others take a little more time and effort and often providing a sample database is very helpf…
I see at least one EE question a week that pertains to using temporary tables in MS Access.  But surprisingly, I was unable to find a single article devoted solely to this topic. I don’t intend to describe all of the uses of temporary tables in t…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…

786 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