Getting user to enter file name

[kuansheng]

Hi Guys,

I happen to come across this codes from www.exceltip.com. I am
wondering whether is there a way to create a user form that can prompt
user to enter or select the name of the file of th e closed workbook
and worksheet that we they want to get the data from. The following is
the code that will get value from a closed workbook. But the problem is
that the file name of the closed workbook and worksheet is hard coded.
What i need is for the user to enter those information. I hope someone
can hel me out in this.

Sub test()
GetValuesFromAClosedWorkbook "C:", "Book1.xls", _
"Sheet1", "A1:K30"
End Sub

Sub GetValuesFromAClosedWorkbook(fPath As String, _
fName As String, sName, cellRange As String)
With ActiveSheet.Range(cellRange)
.FormulaArray = "='" & fPath & "\[" & fName & "]" _
& sName & "'!" & cellRange
.Value = .Value
End With
End Sub

 

[RB Smissaert]

Lookup GetOpenFilename in the help.

RBS

 

[Henrich]

Hi, I personaly would use this method:

Application.Dialogs(xlDialogOpen).Show
file_path = ActiveWorkbook.Path
file_name = ActiveWorkbook.Name

user can easy find a file to open and you will also get the file path and
the file name.

Henrich

„kuansheng" napísal (napísala):

 

[kuansheng]

What i am trying to do is to allow the user to enter the filename via a
userform or input box. So the code above could make use of this file to
extract value from. Is there anyway of doing this?

 

[RB Smissaert]

That is exactly what the 2 replies you have will do.
Unless you want the user to type the path in, in which
case you can use an Inputbox.

RBS

 

[kuansheng]

How can i go about doing it. I am new to this and i dont quite get the
code. Could you guide me along in this .Thanks

 

[Henrich]

Hi again, so if you want to do this by harder way then OK. Try this:

file_path = textbox1.text
or
file_path =inputbox("Enter the file path")

 

[Norman Jones]

Hi Kuansheng,

Try:

'=============>>
Public Sub Tester()
Dim WB As Workbook
Dim FName As Variant

FName = Application.GetOpenFilename()

If FName <> False Then
Set WB = Workbooks.Open(FName)
End If
End Sub
'<<=============

 

[RB Smissaert]

You will need a few helper functions.
This code will do it all, just put the whole lot in a normal Module and
run the Sub test.

Sub test()

Dim fileToOpen
Dim strFileToOpen As String

fileToOpen = _
Application.GetOpenFilename("Excel Files (*.xls), *.xls", , _
"Pick a file to get the value from")

If fileToOpen = False Then
Exit Sub
Else
strFileToOpen = CStr(fileToOpen)
Cells(1) = GetValueFromWB(FolderFromPath(strFileToOpen), _
FileFromPath(strFileToOpen), _
"Sheet1", _
"A1")
End If

End Sub

Function GetValueFromWB(path, file, sheet, ref)

'Retrieves a value from a closed workbook
'----------------------------------------

Dim strSep As String
Dim arg As String

strSep = "\"

'Make sure the file exists
'-------------------------
If Right$(path, 1) <> strSep Then path = path & strSep
If bFileExistsVBA(path & file) = False Then
GetValueFromWB = "File Not Found"
Exit Function
End If

'Create the argument
'-------------------
arg = "'" & path & "[" & file & "]" & sheet & "'!" & _
Range(ref).Range("A1").Address(, , xlR1C1)

'Execute an XLM macro
'--------------------
GetValueFromWB = ExecuteExcel4Macro(arg)

End Function

Public Function bFileExistsVBA(ByVal sFile As String) As Boolean

Dim lAttr As Long

On Error Resume Next
lAttr = GetAttr(sFile)
bFileExistsVBA = (Err.Number = 0) And _
((lAttr And vbDirectory) = 0)
On Error GoTo 0

End Function

Public Function FileFromPath(ByVal strFullPath As String, _
Optional bExtensionOff As Boolean = False) As
String

Dim FPL As Long 'len of full path
Dim PLS As Long 'position of last slash
Dim pd As Long 'position of dot before exension
Dim strFile As String

On Error GoTo ERROROUT

FPL = Len(strFullPath)
PLS = InStrRev(strFullPath, "\", , vbBinaryCompare)
strFile = Right$(strFullPath, FPL - PLS)

If bExtensionOff = False Then
FileFromPath = strFile
Else
pd = InStr(1, strFile, ".", vbBinaryCompare)
FileFromPath = Left$(strFile, pd - 1)
End If

Exit Function
ERROROUT:

On Error GoTo 0
FileFromPath = ""

End Function

Public Function FolderFromPath(strFullPath As String) As String

Dim PLS As Byte 'position of last slash

On Error GoTo ERROROUT

PLS = InStrRev(strFullPath, "\", , vbBinaryCompare)

If PLS = 3 Then
FolderFromPath = Left$(strFullPath, PLS)
Else
FolderFromPath = Left$(strFullPath, PLS - 1)
End If

Exit Function
ERROROUT:

On Error GoTo 0
FolderFromPath = ""

End Function


RBS

 

[kuansheng]

I want to thank both of you Norman and RBS. RBS i copied your code to
the module as instructed. After running the macro, it seems like it
only got value in a single cell. Is it possible to get value in a
range. The code i had above can get value from a range, i dont know why
it doest work here. maybe is there anything that i have done wrong.
Could you help me out? Thanks a million.