Why Won't This Code Work????

[TK]

Why can't I get this to work? NewFN always equals the file I selected but
wbExt always equals "nothing" and the routine crashes. I can't figure out
what I'm doing wrong.
Thanks in advance,
Todd

Private Sub CommandButton1_Click()
NewFN = Application.GetOpenFilename(FileFilter:="Excel Files (*.xls),
*.xls", Title:="Select a file")
If NewFN = False Then
MsgBox "No File Was Selected"
Else
Workbooks.Open FileName:=NewFN

Dim wbExt As Workbook
Set wbExt = NewFN
wbExt.Worksheets("Sheet1").Range("C4").Copy

Application.CutCopyMode = False
wbExt.Close savechanges:=False
Set wbExt = Nothing

End If
End Sub

 

[Rowan]

It crashes because NewFN is a string and wbExt is a workbook so you
can't set one equal to the other.

You could try:

Set wbExt = Workbooks(Right(NewFN, InStrRev(NewFN, "\")))

Hope this helps
Rowan

 

[Patrick Molloy]

just a simple change is all, you pretty much had it....

Private Sub CommandButton1_Click()
Dim wbExt As Workbook

NewFN = _
Application.GetOpenFilename("Excel Files (*.xls), _
*.xls","Select a file")
If NewFN = False Then
MsgBox "No File Was Selected"
Else
Set wbExt = Workbooks.Open(NewFN)
wbExt.Worksheets("Sheet1").Range("C4").Copy

Application.CutCopyMode = False
wbExt.Close savechanges:=False
Set wbExt = Nothing
End If
End Sub