importing a text file into a form

[legos]

I am creating an import list function for a form i am working on. I can do this easily by having the user type in the path to the file, but i was wondering if there was a way yo do it so that the user could select the file the way most programs do. ie having a file window pop-up and searching for the file by selecting whichever hard drive or folder the file is under and then hilighting the file and clicking OK. is this possible?

Durible Outer Casing to Prevent Fall-Apart

 

[PaulF]

go here

http://www.mvps.org/access/api/api0001.htm

PaulF

 

[sillysod]

yep.

well unless ou re using access 97, because i think the feature was added in access 2k

i use this function for my file grabbers


Function GetFile() As String


'Declare FileDialog object.
Dim fd As FileDialog

'Create a FileDialog object as a File Picker dialog box.
Set fd = Application.FileDialog(msoFileDialogFilePicker)

'Declare a variable to contain the path
'of each selected item. Even though the path is a String,
'the variable must be a Variant because For Each...Next
'routines only work with Variants and Objects.
Dim vrtSelectedItem As Variant

'Use a With...End With block to reference the FileDialog object.
With fd

'Use the Show method to display the File Picker dialog box and return the user's action.
'The user pressed the action button.
If .Show = -1 Then

'Step through each string in the FileDialogSelectedItems collection.
For Each vrtSelectedItem In .SelectedItems

'vrtSelectedItem is a String that contains the path of each selected item.
'You can use any file I/O functions that you want to work with this path.
'This example simply displays the path in a message box.
GetFile = vrtSelectedItem

Next vrtSelectedItem
'The user pressed Cancel.
Else
End If
End With

'Set the object variable to Nothing.
Set fd = Nothing
'Debug.Print GetFile



End Function



Its s slightly modified version of a demo function on the microsoft KB

see here

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/vbaof10/html/ofobjFileDialog.asp

Take note of the extra bits of code on the MS KB where you can specify filters for file types. and also set default locations to open files from.

 

[randy700]

I've searched but cannot find the exact thread that I got this from so don't know who to give the credit to.

Place the following code in a module.

Option Compare Database
Option Explicit

Private Declare Function ap_GetOpenFileName Lib "comdlg32.dll" Alias _
    "GetOpenFileNameA" (pOpenFileName As OPENFILENAME) As Long
    
Private Type OPENFILENAME
    lStructSize As Long
    hwndOwner As Long
    hInstance As Long
    lpstrFilter As String
    lpstrCustomFilter As String
    nMaxCustFilter As Long
    nFilterIndex As Long
    lpstrFile As String
    nMaxFile As Long
    lpstrFileTitle As String
    nMaxFileTitle As Long
    lpstrInitialDir As String
    lpstrTitle As String
    flags As Long
    nFileOffset As Integer
    nFileExtension As Integer
    lpstrDefExt As String
    lCustData As Long
    lpfnHook As Long
    lpTemplateName As String
End Type

Public Function ap_FileOpen(Optional strTitle As String = "Open File", _
        Optional strFileName As String = "", _
        Optional strFilter As String = "") As String
    Dim OpenFile As OPENFILENAME
    Dim lngReturn As Long
    
    If Len(strFileName) = 0 Then
        strFileName = String(255, 0)
    End If
    If Len(strFilter) = 0 Then
        strFilter = "Access databases(*.mdb)" & Chr(0) & "*.mdb" & Chr(0)
    End If
    
    OpenFile.lStructSize = Len(OpenFile)
    OpenFile.lpstrFilter = strFilter
    OpenFile.nFilterIndex = 1
    OpenFile.lpstrFile = strFileName + Space(255 - Len(strFileName))
    OpenFile.nMaxFile = 255
    OpenFile.lpstrFileTitle = strFileName
    OpenFile.nMaxFileTitle = OpenFile.nMaxFile
    OpenFile.lpstrInitialDir = "C:\"
    OpenFile.lpstrTitle = strTitle
    OpenFile.flags = 0
    ap_GetOpenFileName OpenFile
    ap_FileOpen = Left(OpenFile.lpstrFile, InStr(OpenFile.lpstrFile, Chr$(0)) - 1)
End Function

Call the function this way....

Dim strFileName as String
strFileName = ap_FileOpen("Search for files")

Randy

 

[legos]

thanks everyone, that is really helpful. One last thing, i've been trying to get a save as function to work the same way. How is it done.

Durible Outer Casing to Prevent Fall-Apart

 

[sillysod]

I have never used it but
replace the fourth line of code:
Set fd = Application.FileDialog(msoFileDialogFilePicker)
with
Set fd = Application.FileDialog(msoFileDialogSaveAs)

This wont save the file for you, it will just return a string showing where the user wants the file to go.

You will then have to use other code to save it to the destination that the user selected

 

[legos]

The Saveas just doesn't seem to work, i've tried several examples:
Dim dlgSaveAs As FileDialog

Set dlgSaveAs = Application.FileDialog( _
FileDialogType:=msoFileDialogSaveAs)

dlgSaveAs.Show
and the one listed above.

When i use msoFileDialogSaveAs i get an error that says object does not support this action

I think that this might have something to do with the problem, it is from VB help:
Each host application can only instantiate a single instance of the FileDialog object. Therefore, many of the properties of the FileDialog object persist even when you create multiple FileDialog objects. Therefore, make sure that you've set all of the properties appropriately for your purpose before you display the dialog box.

If i can't get the saveas to work, i'd like to use the folderpick option which i can get to work. Is there a way to steal what anyone types in the file/foldername box so that i can have the selected path and the file name. I can then save my file with that information.

Durible Outer Casing to Prevent Fall-Apart

 

[allyne]

Hi Everyone,

I have a form and would like to create a button that when the user clicks on it they can import a text file. I've tried using a macro with the transfer text action. This works great except the file name will always be different. I there a way that I can tell it to look at all .txt files in a folder? A quick and easy way would be great! As I'm not an Access guru.

Thanks for your help!