Solved

Access VB FileDialog Close Issue

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

MS Dynamics Made Instantly Simpler

Make Your Microsoft Dynamics Investment Count  & Drastically Decrease Training Time by Providing Intuitive Step-By-Step WalkThru Tutorials.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Access lists formating 8 52
MS Access How To Make Exports Recognize Returns or New Lines As Shown On Form 7 50
MS Access query 16 63
Export individual report to pdf 2 41
It’s been over a month into 2017, and there is already a sophisticated Gmail phishing email making it rounds. New techniques and tactics, have given hackers a way to authentically impersonate your contacts.How it Works The attack works by targeti…
Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
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…
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…

710 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