Macro to open files using cell reference

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

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

"nyctransplant" wrote:

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

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" <[email protected]> wrote in message
news:[email protected]...
> 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!!
>
>

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" <[email protected]> wrote in message
> news:[email protected]...
> > 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!!
> >
> >
>
>
>