Macro to open files using cell reference
Gary;
Could you please help me out with one statement?...
I have a program that open an excel file from select cell (see below).
What I need is a statement that will allow the program to pick up the value
from the row "B" which is located 3 columns on the left side in the same row
of the cell that is selected and is always the last value in the row because
it been picked up from a excel database that is been created automatically.
I have try working with offset but I was not able to achieve what I am
looking because I do not have to much knowledge in programming.
Thanks in Advance.
Maperalia
€˜****PROGRAM START********
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
€˜****PROGRAM END********
"Gary Keramidas" wrote:
i use something like this. the if allows me to have a path here, and at my
client's site
i have a sheet with the workbook names called emp, starting in a1 thru a?. the
code handles the rest
Option Explicit
Sub openwb()
Dim i As Long
Dim fPath As String
Dim lastEMP As Long
Dim fName As String
lastEMP = Worksheets("Emp").Cells(Rows.Count, "A").End(xlUp).Row
For i = 1 To lastEMP
If UCase(Environ("UserName")) = "GARYK" Then
fPath = "N:\My Documents\Excel\Reccu\Vac\"
Else
fPath = "\\mi01admin\private$\Vacation\"
End If
fName = Worksheets("Emp").Cells(i, "B").Value
Workbooks.Open Filename:=fPath & fName, _
ReadOnly:=True, UpdateLinks:=3
' code here
Next i
End Sub
--
Gary
"nyctransplant" wrote in message
...
I'm trying to write a macro that will open a series of files using the
contents of a cell that has the file name. The Workbook.Open Filename seems
to need to text string typed in.
Example:
Cells C4 and C5 contain the path & filename of the files
Workbook.Open Filename <reference in C4
Activate the macro in file & then close
Go down 1 cell
Is cell blank? If yes, end, if not
Workbook.Open Filename <reference in C5
Activate the macro in file & then close
Go down 1 cell
Is cell blank? If yes, end, if not <should end here
I think this is pretty easily taken care of with a variable, however, given
that it's been a while since I've done programming, I'm a bit stuck on the
correct commands.
Thanks for your help!!
|