We help IT Professionals succeed at work.

select file path...

Eddy2010
Eddy2010 asked
on
Hi All,

In the following code, is there a way to change the path below to be an option to choose the location since the location of the file keep changing? So what I would like is that a popup comes up to select the right file. Also, if it is possible,

db1Path = "H:\Finance\Financail.mdb"
DoCmd.TransferDatabase acLink, "Microsoft Access", "H:\Finance\Jun\Financail.mdb", acTable,

The complete codes are below:

Dim tblArr(), JHU As Integer, db1Path As String, strSource As String
db1Path = "H:\Finance\Financail.mdb"
tblArr = Array("077")
For JHU = LBound(tblArr) To UBound(tblArr)

 'create a link
 DoCmd.TransferDatabase acLink, "Microsoft Access", "H:\Finance\Jun\Financail.mdb", acTable, tblArr(JHU), tblArr(JHU)
 Application.RefreshDatabaseWindow
 'append records to bayview table
 CurrentDb.Execute _
    "insert into Fina_tbl " _
    & " select [" & tblArr(JHU) & "].* from [" & tblArr(JHU) & "]"


           Select Case tblArr(JHU)
            Case "077"
                  strSource = "dom"
            Case "88"
                  strSource = "MM"
               

      End Select

'update the columns Department and Year

CurrentDb.Execute "update dom_Tbl set type='" & strSource & "',[Year]=2008" _
                   & " Where type Is Null And [Year] Is Null"


'drop the link
CurrentDb.Execute "drop table [" & tblArr(JHU) & "]"
Next
Comment
Watch Question

Eric FlammOwner

Commented:
Here's some code I use to get the path and filename for a file to import in one of my Applications. Once you have the fully-qualified name in txtFilename (a textbox on the form), you can use its contents in your TransferDatabase command. Of course, you'll want to change the file filter to mdb, accdb.
Private Sub Form_Load()
'Open a file picker dialog and save selected filename in txtfilename, then pass it to the import routine
    
    Dim fdlg As Office.FileDialog
    Dim pth As String
    On Error GoTo LoadDetails
    pth = Details("ImportPath") 'loaded from appdetails table
    On Error GoTo 0
    Me.txtFilename.SetFocus
    Set fdlg = FileDialog(msoFileDialogFilePicker)
    With fdlg
        .AllowMultiSelect = False
        .Title = "Select the file to import"
        .Filters.Clear
        .Filters.Add "csv Files", "*.csv"
        .Filters.Add "Excel Files", "*.xlsx"
        .Filters.Add "All Files", "*.*"
        .InitialFileName = pth
        If .Show = True Then
            txtFilename.Text = .SelectedItems(1)
        Else
            MsgBox "You pressed cancel - no file selected"
            txtFilename.Text = ""
        End If
    End With
cleanUp:
    Set fdlg = Nothing
    Exit Sub
LoadDetails:
'reload application details dictionary
    Call LoadDetails
    Resume Next
End Sub

Open in new window

Author

Commented:
will this code work like mine. The one I used it worked but I don't to get to choose the location. I file path is saved in the code
Owner
Commented:
Well, I just gave you the code to populate a textbox on a form - as you can see, this is the load event handler for the form - so when you open the form, the first thing that happens is the file select dialog (Office.FileDialog) opens up; my default path is stored in a dictionary object (Details("ImportPath")) - you can replace this with whatever path you want to start with; but the user can browse anywhere on the PC (or the network) to get the file to load. Once they select a file, they click the "Open" button in the dialog - that sets fdlg.Show to True, which causes the textbox to get loaded with the selected filename.

Once you have the filename, you can do something like:
DoCmd.TransferDatabase acLink, "Microsoft Access", txtFilename.Text, acTable,tblArr(JHU), tblArr(JHU) 

Open in new window

inside your For/Next loop, which will link to the file that was selected in the earlier method. Of course, if the file changes each time through the loop, then you might want to make my code into a private function that returns a new filename each time, e.g.:
Private Function GetFileToLink() as String
'Open a file picker dialog and save selected filename in txtfilename, then pass it to the import routine
    
    Dim fdlg As Office.FileDialog
    Static pth As String    
    if isnull(pth) then pth="H:\Finance\"  ' this just sets the default path - it's a static variable, so it keeps its value from pass to pass
    On Error GoTo 0
    
    Set fdlg = FileDialog(msoFileDialogFilePicker)
    With fdlg
        .AllowMultiSelect = False
        .Title = "Select the file to import"
        .Filters.Clear
        .Filters.Add "mdb Files", "*.mdb" 
        .Filters.Add "All Files", "*.*"
        .InitialFileName = pth
        If .Show = True Then
            GetFileToLink= .SelectedItems(1)   'This is the end value of the function
        Else
            MsgBox "You pressed cancel - no file selected"
            GetFileToLink = ""
        End If
    End With
cleanUp:
    Set fdlg = Nothing
    Exit Function
End Function

Open in new window


If you do that, then your link statement is
DoCmd.TransferDatabase acLink, "Microsoft Access", GetFileToLink, acTable,tblArr(JHU), tblArr(JHU) 

Open in new window

Author

Commented:
I ma sorry I am new at this , so please forgive me on my silly questions.

So, I created a module and placed the below codes in it and called it Module 1
Private Function GetFileToLink() as String....

I placed the codes Private Sub Form_Load() in the form load event.

When I ran the codes, it gave me error on  fdlg
Eric FlammOwner

Commented:
If you place the function in a module, you'll need to make it public so you can call it from the form code page.

You also need to go to the Tools menu in the VB Editor, select References, and check Microsoft Office 12.0 Object Library (or whatever version number you have). That's where the Office.FileDialog class lives.

Good luck!