Solved

Access VB FileDialog Close Issue

Posted on 2004-08-18
3
492 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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

Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

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…
Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

735 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