Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Getting user to enter file name
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Getting user to enter file name
Lookup GetOpenFilename in the help.
RBS "kuansheng" wrote in message ps.com... 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Getting user to enter file name
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): 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Getting user to enter file name
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? |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Getting user to enter file name
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" wrote in message oups.com... 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? |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Getting user to enter file name
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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Getting user to enter file name
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") "kuansheng" wrote: 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 |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Getting user to enter file name
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 '<<============= --- Regards, Norman "kuansheng" wrote in message oups.com... 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 |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Getting user to enter file name
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" wrote in message oups.com... 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 |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
Getting user to enter file name
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Enter a User Name When Changes are Made | Excel Worksheet Functions | |||
enter user in cell | Excel Worksheet Functions | |||
User must enter something in a range of cells | Excel Discussion (Misc queries) | |||
Macro to have user enter new worksheet name | Excel Programming | |||
Do combobox allow user to enter text? | Excel Programming |