Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Activate file without path location? Subscript out of range.
Hi I have the following code that SHOULD open a file and copy data from range a12:c100 of that opened file into the calling worksheet at sheet1 A38. I get a subscript out of range error trying to activate the newly opened file. I passed the file name to the msgbox and found it has the directory path in it. Since I am on a network is there a way to remove that path so any user at different directories can open the file? Thanks Pal Private Sub CommandButton1_Click() Filename = Application.GetOpenFilename Windows(Filename).Activate Range("A12:C100").Select Selection.Copy Windows("results sheet").Activate Range("A38").Select Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _ False, Transpose:=False End Sub |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Activate file without path location? Subscript out of range.
Hi Pal
GetOpenFilename don't open the file (see the Excel help) You need to open the file Workbooks.Open(FName) Sub test() Dim FName As Variant Dim wb As Workbook FName = Application.GetOpenFilename(filefilter:="Excel Files (*.xls), *.xls") If FName < False Then Set wb = Workbooks.Open(FName) MsgBox "your code" wb.Close End If End Sub -- Regards Ron de Bruin (Win XP Pro SP-1 XL2000-2003) www.rondebruin.nl "Pal" wrote in message news:Cmo0c.423198$I06.4711213@attbi_s01... Hi I have the following code that SHOULD open a file and copy data from range a12:c100 of that opened file into the calling worksheet at sheet1 A38. I get a subscript out of range error trying to activate the newly opened file. I passed the file name to the msgbox and found it has the directory path in it. Since I am on a network is there a way to remove that path so any user at different directories can open the file? Thanks Pal Private Sub CommandButton1_Click() Filename = Application.GetOpenFilename Windows(Filename).Activate Range("A12:C100").Select Selection.Copy Windows("results sheet").Activate Range("A38").Select Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _ False, Transpose:=False End Sub |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Activate file without path location? Subscript out of range.
Thanks Ron
But for some reason my copy command is not looking in the new file. It copes the range of the calling worksheet. Pal Private Sub CommandButton1_Click() Dim FName As Variant Dim wb As Workbook FName = Application.GetOpenFilename(filefilter:="Excel Files (*.wks), *.wks") If FName < False Then Set wb = Workbooks.Open(FName) Range("A12:C100").Copy wb.Close End If End Sub "Ron de Bruin" wrote in message ... Hi Pal GetOpenFilename don't open the file (see the Excel help) You need to open the file Workbooks.Open(FName) Sub test() Dim FName As Variant Dim wb As Workbook FName = Application.GetOpenFilename(filefilter:="Excel Files (*.xls), *.xls") If FName < False Then Set wb = Workbooks.Open(FName) MsgBox "your code" wb.Close End If End Sub -- Regards Ron de Bruin (Win XP Pro SP-1 XL2000-2003) www.rondebruin.nl "Pal" wrote in message news:Cmo0c.423198$I06.4711213@attbi_s01... Hi I have the following code that SHOULD open a file and copy data from range a12:c100 of that opened file into the calling worksheet at sheet1 A38. I get a subscript out of range error trying to activate the newly opened file. I passed the file name to the msgbox and found it has the directory path in it. Since I am on a network is there a way to remove that path so any user at different directories can open the file? Thanks Pal Private Sub CommandButton1_Click() Filename = Application.GetOpenFilename Windows(Filename).Activate Range("A12:C100").Select Selection.Copy Windows("results sheet").Activate Range("A38").Select Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _ False, Transpose:=False End Sub |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Activate file without path location? Subscript out of range.
Try this then
See the following lines Set wb1 = ThisWorkbook Set wb2 = Workbooks.Open(FName) Sub test() Dim FName As Variant Dim wb1 As Workbook Dim wb2 As Workbook Set wb1 = ThisWorkbook FName = Application.GetOpenFilename(filefilter:="Excel Files (*.xls), *.xls") If FName < False Then Application.ScreenUpdating = False Set wb2 = Workbooks.Open(FName) wb2.Sheets("Sheet1").Range("A12:C100").Copy wb1.Sheets("Sheet1").Range("A1") wb2.Close False Application.ScreenUpdating = True End If End Sub -- Regards Ron de Bruin (Win XP Pro SP-1 XL2000-2003) www.rondebruin.nl "Pal" wrote in message news:FZo0c.423468$I06.4713193@attbi_s01... Thanks Ron But for some reason my copy command is not looking in the new file. It copes the range of the calling worksheet. Pal Private Sub CommandButton1_Click() Dim FName As Variant Dim wb As Workbook FName = Application.GetOpenFilename(filefilter:="Excel Files (*.wks), *.wks") If FName < False Then Set wb = Workbooks.Open(FName) Range("A12:C100").Copy wb.Close End If End Sub "Ron de Bruin" wrote in message ... Hi Pal GetOpenFilename don't open the file (see the Excel help) You need to open the file Workbooks.Open(FName) Sub test() Dim FName As Variant Dim wb As Workbook FName = Application.GetOpenFilename(filefilter:="Excel Files (*.xls), *.xls") If FName < False Then Set wb = Workbooks.Open(FName) MsgBox "your code" wb.Close End If End Sub -- Regards Ron de Bruin (Win XP Pro SP-1 XL2000-2003) www.rondebruin.nl "Pal" wrote in message news:Cmo0c.423198$I06.4711213@attbi_s01... Hi I have the following code that SHOULD open a file and copy data from range a12:c100 of that opened file into the calling worksheet at sheet1 A38. I get a subscript out of range error trying to activate the newly opened file. I passed the file name to the msgbox and found it has the directory path in it. Since I am on a network is there a way to remove that path so any user at different directories can open the file? Thanks Pal Private Sub CommandButton1_Click() Filename = Application.GetOpenFilename Windows(Filename).Activate Range("A12:C100").Select Selection.Copy Windows("results sheet").Activate Range("A38").Select Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _ False, Transpose:=False End Sub |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Formula Bar show the location path file plus the formula | Excel Discussion (Misc queries) | |||
Excel 2007 - File Path location | Excel Discussion (Misc queries) | |||
Formula too long - new file path is shorter than old file path - Excel 2003 | Excel Worksheet Functions | |||
Run Time Error 9 (Subscript out of Range) for XLA file | Excel Discussion (Misc queries) | |||
print file path and location in a footer | New Users to Excel |