Open an Excel File from Selected Cell
 

Nice to hear from you again. I wonder if I can ask you a question regarding
VBA. I have a program that open an excel file from select cell (see below).
However, this program opens file just from the same directories. What happen
if Ithe last directory changes all the time. For example instead 100 could be
200 or 300 or 400 and so on. Can the program be adjusted to get this new
directory and find the file?



Thanks in advance.

Maperalia.







'***PROGRAM STARTS*****

Sub OpenSelectFilename1()

directory = "C:\Test\Pants\Blue\100 "\"
filetext = Selection.Value & ".xls"
Workbooks.Open directory & filetext

'If filetext = ".xls" Then
'MsgBox "Please a FILE NAME to Open the file"
'End If

End Sub

"***PROGRAM ENDS*******




'***OTHER PROGRAM STARTS*****

'Sub OpenExcelFile()
' directory = ThisWorkbook.Path & "\"
' filetext = Selection.Value & ".xls"
' If filetext = ".xls" Then
' MsgBox "Please a FILE NAME to Open the file"
' Exit Sub
' End If


"***PROGRAM ENDS*******

Open an Excel File from Selected Cell
 

Hi Maperalia

One way to do it is to have a box on the form with the directory or to have
it set in a look up sheet on your excel worksheet.

For example if you have it in a worksheet code data at cell B2 in the
workbook were the macro is the code would be:

Sub OpenSelectFilename1()
on error goto file_error

directory = thisworkbook.worksheets("data").range("B2")

if directory = "" then
directory = "C:\Test\Pants\Blue\100\"

filetext = Selection.Value & ".xls"
If filetext = ".xls" Then
MsgBox "Please a FILE NAME to Open the file", vbokonly, "Error"
else
Workbooks.Open directory & filetext
end if
exit sub
file_error:
MsgBox "Cannot open file, either folder or file name error.",
vbokonly, "Error"

end sub

--
HTHs Martin


"maperalia" wrote:

Nice to hear from you again. I wonder if I can ask you a question regarding
VBA. I have a program that open an excel file from select cell (see below).
However, this program opens file just from the same directories. What happen
if Ithe last directory changes all the time. For example instead 100 could be
200 or 300 or 400 and so on. Can the program be adjusted to get this new
directory and find the file?



Thanks in advance.

Maperalia.







'***PROGRAM STARTS*****

Sub OpenSelectFilename1()

directory = "C:\Test\Pants\Blue\100 "\"
filetext = Selection.Value & ".xls"
Workbooks.Open directory & filetext

'If filetext = ".xls" Then
'MsgBox "Please a FILE NAME to Open the file"
'End If

End Sub

"***PROGRAM ENDS*******




'***OTHER PROGRAM STARTS*****

'Sub OpenExcelFile()
' directory = ThisWorkbook.Path & "\"
' filetext = Selection.Value & ".xls"
' If filetext = ".xls" Then
' MsgBox "Please a FILE NAME to Open the file"
' Exit Sub
' End If


"***PROGRAM ENDS*******

Open an Excel File from Selected Cell
 

Martin;

Thanks for your quick response and for your recommendation. However, I have
tried to apply your program in my sample but unfortunately I was
unsuccessful. My knowledge in vba is very poor.
Basically, what I am looking for is to open a €œFilename€� from a €œSelect
Cell€� in excel database (see table below). I will place a €œpush button€� on
the top of the filename cell; so I can select a filename and then click this
button to open it.

This excel database has the work order(WO) and the filename that is the path
of the file. For example:
1.- In the row 2, I have WO €œTom€� and filename €œBlue @ 40€� so the path of
this file is C:\Jeans\Heavy\Double Mark\Tom\Blue @ 40.xls.
2.- In the row 3, I have the WO €œPaula€� and the filename €œRed @30€� so the
path of this file is C:\Jeans\Heavy\Double Mark\Paula\Red @30.xls.
3.- In the row 4, I have WO €œManuel€� and the filename €œYellow @ 34€� so the
path of this file is C:\Jeans\Heavy\Double Mark\Manuel\ Yellow @ 34.xls.

I wonder if I can have my program I sent you adjust it to read the full path
plus the last directory which is the one that changes all the time along the
filename. This last directory as you notice (is the WO) is located 3 rows on
the left side of the filename.

Could you please help me with this matter?.

Thanks in advance.

Maperalia.

A B C D E
1 Client WO Color Size Filename
2 Tom Tom Blue 40 Blue @ 40
3 Paula Paula Red 30 Red @30
4 Manuel Manuel Yellow 34 Yellow @ 34
5 Peter Peter Marrow 28 Marrow @ 28
6 Tom Tom Black 40 Black @ 40
7 Manuel Manuel Green 36 Green @ 36
8 Peter Peter White 26 White @ 26
9 Paula Paula Blue 38 Blue @ 38











"Martin Fishlock" wrote:

Hi Maperalia

One way to do it is to have a box on the form with the directory or to have
it set in a look up sheet on your excel worksheet.

For example if you have it in a worksheet code data at cell B2 in the
workbook were the macro is the code would be:

Sub OpenSelectFilename1()
on error goto file_error

directory = thisworkbook.worksheets("data").range("B2")

if directory = "" then
directory = "C:\Test\Pants\Blue\100\"

filetext = Selection.Value & ".xls"
If filetext = ".xls" Then
MsgBox "Please a FILE NAME to Open the file", vbokonly, "Error"
else
Workbooks.Open directory & filetext
end if
exit sub
file_error:
MsgBox "Cannot open file, either folder or file name error.",
vbokonly, "Error"

end sub

--
HTHs Martin


"maperalia" wrote:

Nice to hear from you again. I wonder if I can ask you a question regarding
VBA. I have a program that open an excel file from select cell (see below).
However, this program opens file just from the same directories. What happen
if Ithe last directory changes all the time. For example instead 100 could be
200 or 300 or 400 and so on. Can the program be adjusted to get this new
directory and find the file?



Thanks in advance.

Maperalia.







'***PROGRAM STARTS*****

Sub OpenSelectFilename1()

directory = "C:\Test\Pants\Blue\100 "\"
filetext = Selection.Value & ".xls"
Workbooks.Open directory & filetext

'If filetext = ".xls" Then
'MsgBox "Please a FILE NAME to Open the file"
'End If

End Sub

"***PROGRAM ENDS*******




'***OTHER PROGRAM STARTS*****

'Sub OpenExcelFile()
' directory = ThisWorkbook.Path & "\"
' filetext = Selection.Value & ".xls"
' If filetext = ".xls" Then
' MsgBox "Please a FILE NAME to Open the file"
' Exit Sub
' End If


"***PROGRAM ENDS*******