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

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

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

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