Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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!! |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro to open files using cell reference
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!! |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro to open files using cell reference
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!! |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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!! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
open files run macro | Excel Discussion (Misc queries) | |||
macro open files | Excel Programming | |||
Macro Open Files | Excel Programming | |||
Macro to open *.dat files and save as .txt (comma delimited text files) | Excel Programming | |||
Files won't open after running a macro | Excel Programming |