Celebrate National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Access VB FileDialog Close Issue

Posted on 2004-08-18
3
Medium Priority
?
496 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 1000 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

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

If you need a simple but flexible process for maintaining an audit trail of who created, edited, or deleted data from a table, or multiple tables, and you can do all of your work from within a form, this simple Audit Log will work for you.
We live in a world of interfaces like the one in the title picture. VBA also allows to use interfaces which offers a lot of possibilities. This article describes how to use interfaces in VBA and how to work around their bugs.
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …

730 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