Open an excel file

[maperalia]

I have a program that open an excel file from file selected cell (see below).
However, I have problem to create an offset. What I want is to have the
program to read the new work order number as a third directory and then read
the filename.

Could you please help me with this matter?

Thanks in advance.
Maperalia


A B C D
E
1 WORK ORDER Location Cut FILENAME
2 2563 AA 11 AA @ 11.xls
3 2564 BB 22 BB @ 22.xls
4 2565 CC 33 CC @ 33.xls



Sub OpenSelectFilename()

Dim dcell As Range

Set dcell = ActiveSheet.Range("Bi")
directory = "S:\test\pants\" & dcell.OFFSET(-3, 0).Value & "\"
filetext = Selection.Value & ".xls"
Workbooks.Open directory & filetext
End Sub

 

[Carim]

Hi,

Could Vlookup() solve your problem ...?

HTH

 

[maperalia]

Carim;
Thanks for your quick respond. I wondfer if you can help me to get the
statement done because I replace waht you advised me I did not work..

Thanks.
Maperalia

 

[maperalia]

Basically, I have this program (see below) that I want to pick up the value
from the row "B". Which is located 3 columns on the left side in the same
row. Everytime will be a new value in in the column "B". Therefore the
program have to read this value in order to open the file in the work order.

Thanks in Advance.
Maperalia






Sub OpenSelectFilename1()
Dim WO As String

WO = Worksheets("Sheet1").Range("B3")

directory = "C:\test\Pants\" & WO & "\"
filetext = Selection.Value & ".xls"
Workbooks.Open directory & filetext
End Sub

 

[Kim Greenlee]

I suggest you run the code and set breakpoints to verify that at each point
you are getting what you expect.

Looking at your stuff, assume that the numbers I have added are line numbers:

A B C D
E
1 WORK ORDER Location Cut FILENAME
2 2563 AA 11 AA @ 11.xls
3 2564 BB 22 BB @ 22.xls
4 2565 CC 33 CC @ 33.xls

Sub OpenSelectFilename()

1 Dim dcell As Range
2
3 Set dcell = ActiveSheet.Range("Bi")
4 directory = "S:\test\pants\" & dcell.OFFSET(-3, 0).Value & "\"
5 filetext = Selection.Value & ".xls"
6 Workbooks.Open directory & filetext
End Sub


At line 3 the Range() results are very likely not what you expect because
you are not building the cell id correctly. There is no Range "Bi". So the
first thing to do is find out what the value is in "B" in the selected row.
Don't forget to just return if the selected row is 1. (ie. don't try to open
the file) Once you know what is selected then you can get the correct cell id
and its contents.

Basically step through the code and verify that the results you are
expecting are actually what your program is finding.

It would also be easier to verify the complete file path if you built it
before the call to Workbooks.Open.

Dim myFile As String
myFile = directory & filetext
Workbooks.Open myFile

Good luck,

Kim Greenlee

 

[maperalia]

Kim;
Thanks for your response.
Unfortunately my knowledge in VBA is very poor and was not able to follow
your advice.
However, Iwill appreciate your lead me with the way I have to make a
statemnet for offset or lookup because I and trying to get the information
written 3 rows on the left side of the cell I select and then convert as a
value.

Thanks in advance.
Maperalia

 

[Kim Greenlee]

I just finished putting together a blog post about basic Excel VBA debugging.
This will get you started. Good luck with your project.

http://krgreenlee.blogspot.com/2006/04/programming-excel-vba-debugging-for.html

Kim

 

[maperalia]

Kim;
Thank for the excellent detail that you have posted. Has helped me a lot
understand VBA.
Regarding the sample, I ran the macro:


Sub MyMacro()
Dim strDir As String
strDir = ActiveSheet.Range("B1").Value
Dim strValue As String
strValue = dcell.Offset(-3, 0).Value
directory = "c:\test\" & strValue & "\"
filetext = Selection.Value & ".xls"
Workbooks.Open directory & filetext
End Sub

But I got the following error message:

Runtime -Error '424'
Object Required

Then when I click debug it is highligthing at:
strValue = dcell.Offset(-3, 0).Value


Could you please tell me how to fix it thie error?

Thanks in advance.
Maperalia

 

[Kim Greenlee]

The problem is that dcell is never defined or set. So when you try to use it
the interpretor doesn't know what to do. I don't think you need it. Try the
code below to remove the dcell dependency:

Sub MyMacro()
Dim strDir As String
strDir = ActiveSheet.Range("B1").Value
directory = "c:\test\" & strDir & "\"
filetext = Selection.Value & ".xls"
Workbooks.Open directory & filetext
End Sub

Also put the Selection.Value in a separate line so you can easily verify the
filetext value. So the code becomes:

Sub MyMacro()
Dim strDir As String
strDir = ActiveSheet.Range("B1").Value

Dim strFileText As String
strFileText = Selection.Value

Dim strFullPath As String
strFullPath = "c:\test\" & strDir & "\" & strFileText & ".xls"

Workbooks.Open strFullPath
End Sub

You'll want to add code incase the user doesn't enter the values you expect
AND in case the Open fails.


Kim Greenlee

 

[maperalia]

Kim;
Thanks for your quick response.
I ran the macro, however, takes the directory that was point it only. For
example, when I type:
strDir = ActiveSheet.Range("B2").Value
The macro will open the file under the directory which has the work order
number 2563. It there is another file with the same directory the macro will
work. However, does not work when the work order is different than 2563.
How can I get a generic "B" so will take all the values located under this
column?

For your information. The table I am using is the following:
A B C D
E

1 WORK ORDER Location Cut FILENAME
2 2563 AA 11 AA @ 11.xls
3 2564 BB 22 BB @ 22.xls
4 2565 CC 33 CC @ 33.xls

Thanks again for your time.

Maperalia