File name as stored variable via dialog box (1 Viewer)

[sphynx]

I Posted yesterday in tables with regarding to txt. file import into access and a #NUM! error.

Having sorted this issue, I am now pursuing automating the import of my .txt log files into access

The code I have so far is below, this is working well with the exception of the file reference which is currently set by the code

''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
Private Sub Command1_Click()

DoCmd.RunSQL "DELETE * FROM LogTemp_Tbl;"

DoCmd.TransferText acImportDelim, ("LOG"), ("LogTemp_Tbl"), ("EVENT LOG.txt")

End Sub
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''

What I would like to do is be able to set the file name in the above code as a variable that is inserted via the file open dialog box, i.e if I am not around the task is as simple as possible for someone else to perform who does not have knowledge of VB.

Any help would be greatley appreciated, could I ask that any code help is explained in terms of what the separate lines of code do, I am as interested in understanding in learning how a solution works as I am in sorting the issue

Thanks

 

[jal]

If you are sure that users have MS office installed you can use this (this code depends on a DLL installed with Office).

Dim path As String
Application.FileDialog(1).Show
If Application.FileDialog(1).SelectedItems.Count = 1 Then
path = Application.FileDialog(1).SelectedItems(1)
MsgBox path
Else
MsgBox ("No file selected.")
End If


The numberr 1 means "FileDialog" since it can also be used as a folder dialog. A fuller version is this (but I discourage the full version since it requires adding a reference to the DLL which is a bad idea since the users might have a different version of the DLL).

Dim dlg As Office.FileDialog, path As String
Set dlg = Application.FileDialog(msoFileDialogFilePicker)
dlg.Show
If dlg.SelectedItems.Count = 1 Then
path = dlg.SelectedItems(1)
MsgBox path
Else
MsgBox ("No file selected.")
End If

If you want to avoid the DLL issue altogether, use a Windows API call (pass in empty strings for any values you don't want to set)


Public Declare Function aht_apiGetOpenFileName Lib "comdlg32.dll" _
Alias "GetOpenFileNameA" (OFN As tagOPENFILENAME) As Boolean
Public Declare Function aht_apiGetSaveFileName Lib "comdlg32.dll" _
Alias "GetSaveFileNameA" (OFN As tagOPENFILENAME) As Boolean
Public Declare Function CommDlgExtendedError Lib "comdlg32.dll" () As Long

Public Type tagOPENFILENAME
lStructSize As Long
hwndOwner As Long
hInstance As Long
strFilter As String
strCustomFilter As String
nMaxCustFilter As Long
nFilterIndex As Long
strFile As String
nMaxFile As Long
strFileTitle As String
nMaxFileTitle As Long
strInitialDir As String
strTitle As String
Flags As Long
nFileOffset As Integer
nFileExtension As Integer
strDefExt As String
lCustData As Long
lpfnHook As Long
lpTemplateName As String
End Type

'Pass in a boolean indicating whether this is a SaveFileDialog. If false, it will be an open fileEDialog.
Private Function ShowFileDialog(ByVal SaveFileDialog As Boolean, strFilter As String, strTitle As String, ByVal strInitialDirec As String)
Dim OFN As tagOPENFILENAME
Dim strFileName As String, strFileTitle As String
strFileName = VBA.Left(strFileName & String(256, 0), 256)
strFileTitle = String(256, 0)
With OFN
.lStructSize = Len(OFN)
'.hwndOwner = Application.hWndAccessApp
.strFilter = strFilter
.nFilterIndex = 0
.strFile = VBA.Left(strFileName & String(256, 0), 256)
.nMaxFile = VBA.Len(strFileName)
.strFileTitle = String(256, 0)
.nMaxFileTitle = VBA.Len(strFileTitle)
.strTitle = strTitle
.Flags = 0
.strDefExt = ""
.strInitialDir = strInitialDirec
.strCustomFilter = ""
.nMaxCustFilter = 0
.lpfnHook = 0
.strCustomFilter = String(255, 0)
.nMaxCustFilter = 255
End With
If SaveFileDialog Then aht_apiGetSaveFileName OFN Else aht_apiGetOpenFileName OFN
'The string returned is 256 chars long, ending in nulls. Remove the nulls.
ShowFileDialog = OFN.strFile
If VBA.Len(OFN.strFile & "") = 0 Then Exit Function
Dim i As Long
For i = VBA.Len(OFN.strFile) To 1 Step -1
If VBA.Mid(OFN.strFile, i, 1) <> Constants.vbNullChar Then Exit For
Next i
ShowFileDialog = VBA.Mid(OFN.strFile, 1, i)
End Function

Sample filter string - this one for excel files (the Chr(0) functions like the vertical bar in VB.Net filters).

Dim filter As String
filter = "Excel files(*.xls)" & Chr(0) & "*.xls" & Chr(0) & "All Files (*.*)" & Chr(0) & "*.*"

 

[sphynx]

Thank you very much for you quick and concise response. I continued to work on this after my original post and came up with this

''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
Private Sub Command2_Click()

Dim filename As String

Set ObjFSO = CreateObject("UserAccounts.CommonDialog")
ObjFSO.Filter = "VBScripts|*.vbs|Text Documents|*.txt|All Files|*.*"
ObjFSO.FilterIndex = 3
ObjFSO.InitialDir = "C:\LOGS"
InitFSO = ObjFSO.ShowOpen

filename = ObjFSO.filename

DoCmd.RunSQL "DELETE * FROM LogTemp_Tbl;"

On Error GoTo ErrHandler

DoCmd.TransferText acImportDelim, ("EVENT LOG"), ("LogTemp_Tbl"), filename

ExitHandle:
Exit Sub

ErrHandler:
Select Case Err.Number
Case 2501

Resume ExitHandle
Case Else
MsgBox Err.Number & vbCrLf & Err.Description
Resume ExitHandle

End Select

End Sub
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''

This seems to do what I wanted, I will however also implement the code you posted 1, so i can increase my own understanding & 2, as you were kind enough to take the time to respond

 

[jal]

Thank you so much !

I really didn't know that Windows XP has a scripted file dialog. I hope it carries over into Vista and later. I've added your sample code to my notebook. I like the fact that I won't have to worry about the DLL issue, using your code. Very nice!

 

[boblarson]

It would be good if, in the future, you all would use the code tags to show code as it keeps it cleaner.

See here: