Macro to open files using cell reference

[Guest]

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!!

 

[Guest]

Try the following:

Sub OpenExcelFile()
directory = ThisWorkbook.path & "\"
filetext = Selection.Value & ".xls"
If filetext = ".xls" Then
MsgBox "Please Select Cell with Filename to Open the file"
Exit Sub
End If
Workbooks.Open directory & filetext
End Sub

Hope this help!!!!

Maperalia

 

[Gary Keramidas]

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

 

[Guest]

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********