Retrieving a Dynamic File Path

will1128

Board Regular
Joined
Aug 6, 2010
Messages
70
Using several combinations of code I need to retrieve File Path dynamically when a user imports a file.

The code to have a user import a csv file is

Code:
fileName = Application.GetOpenFilename( _
                "Comma Separated Files (*.csv),*.csv," & _
                "All Files (*.*),*.*", _
                Title:="Select a CSV File to Import")

The user can get any File Path. This URL gives a post on doing this, but I don't understand it.

http://www.ozgrid.com/forum/showthread.php?t=20350

If you can help me out so I can get the dynamic FilePath so I can use this, http://www.zerrtech.com/content/excel-vba-open-csv-file-and-import, I would sincerely appreciate it.

Thanks :confused:
 

Excel Facts

Can Excel fill bagel flavors?
You can teach Excel a new custom list. Type the list in cells, File, Options, Advanced, Edit Custom Lists, Import, OK
I figured it out....well, the modifications, here if you need it:

Code:
Public Sub Open_RIR_File()
    'In the event of an error, make sure the application is reset to normal.
    'On Error GoTo ErrorCheck
    'Dimension Variables
    Dim FileName As String
    Dim FilePath As String
    Dim OutSheet As String
    OutSheet = "Sheet1"
 
    FileName = Application.GetOpenFilename( _
                "Comma Separated Files (*.csv),*.csv," & _
                "All Files (*.*),*.*", _
                Title:="Select a CSV File to Import")
 
    'Debug.Print FileName
 
    iPos = InStrRev(FileName, "\", , vbTextCompare)
    'Debug.Print iPos
    If (iPos = 0 Or iPos = Null) Then
    MsgBox ("There is an error in file name specified. (FileName)")
    Else
    'Debug.Print FileName
    FilePath = Left(FileName, iPos - 1)
    FileName = Right(FileName, 17)
    'Debug.Print FilePath
    'Debug.Print FileName
    End If
 
   Call doFileQuery(FileName, OutSheet, FilePath)
End Sub

Code:
Public Function doFileQuery(FileName As String, OutSheet As String, FilePath As String) As Boolean
Debug.Print FileName
Debug.Print FilePath
Debug.Print OutSheet
Dim connectionName As String
connectionName = "TEXT;" + FilePath + "\" + FileName
Debug.Print connectionName
'http://www.zerrtech.com/content/excel-vba-open-csv-file-and-import
 With Worksheets(OutSheet).QueryTables.Add(Connection:=connectionName, _
 Destination:=Worksheets(OutSheet).Range("A1"))
 
    .Name = FileName
    .FieldNames = True
    .RowNumbers = False
    .FillAdjacentFormulas = False
    .PreserveFormatting = True
    .RefreshOnFileOpen = False
    .RefreshStyle = xlOverwriteCells
    .SavePassword = False
    .SaveData = True
    .AdjustColumnWidth = True
    .RefreshPeriod = 0
    .TextFilePromptOnRefresh = False
    .TextFilePlatform = 437
    .TextFileStartRow = 1
    .TextFileParseType = xlDelimited
    .TextFileTextQualifier = xlTextQualifierDoubleQuote
    .TextFileConsecutiveDelimiter = False
    .TextFileTabDelimiter = False
    .TextFileSemicolonDelimiter = False
    .TextFileCommaDelimiter = True
    .TextFileSpaceDelimiter = False
    .Refresh BackgroundQuery:=False
    End With
End Function
 
Upvote 0

Forum statistics

Threads
1,213,484
Messages
6,113,927
Members
448,533
Latest member
thietbibeboiwasaco

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top