If I declare it as a variant, I can do a boolean comparison:

If OldFile = False Then

If I declare it as a string, then I have to be careful.
If OldFile = "False" Then

I have to worry about the double quotes, the upper/lower case and I'm not sure
how False would be treated in a non-English language.

So instead of worrying, I'll just take the easy way out.


Horatio J. Bilge, Jr. wrote:

That works great. Thanks for the quick help. A question, though... why
declare OldFile as a variant, instead of a string?

"Dave Peterson" wrote:

I'd use:

Option Explicit
Private Sub cmdImport_Click()

Dim WkBk As Workbook
Dim NewWkbk As Workbook
Dim OldWkbk As Workbook
Dim OldFile As Variant
Dim NewFile As String

OldFile = Application.GetOpenFilename
If OldFile = False Then
Exit Sub
End If

NewFile = ThisWorkbook.Path & "\" & "NewLog.xls"

Set OldWkbk = Workbooks.Open(OldFile)
Set NewWkbk = Workbooks.Open(NewFile)

OldWkBk.Worksheets("Times").Range("Log").Copy _
Destination:=NewWkBk.Worksheets("Times").Range("Lo g").Cells(1)

End Sub

I moved the open's before the .copy. Sometimes a workbook will have a
workbook_open/auto_open procedure in it. And most macros (that do anything)
will destroy that clipboard.

By using .cells(1), I don't have to worry about the size of the Log ranges being
different. I'll just let excel determine the size like it does when you copy a
range and just select the top left corner to paste.




Horatio J. Bilge, Jr. wrote:

I am trying to use vba to copy a range from one file to another. The old file
is a log of dates and times, and the new file is an updated version of the
log. The code is in a third workbook, and I have a command button to run the
code. I get an error on the paste line, "Object doesn't support this property
or method."

Private Sub cmdImport_Click()
Dim WkBk As Workbook
Dim OldFile As String
Dim NewFile As String

OldFile = Application.GetOpenFilename
If OldFile = "False" Then Exit Sub
NewFile = ThisWorkbook.Path & "\" & "NewLog.xls"

Set WkBk = Workbooks.Open(OldFile)
With WkBk.Worksheets("Times")
.Range("Log").Copy
End With

Set WkBk = Workbooks.Open(NewFile)
With WkBk.Worksheets("Times")
.Range("Log").Paste
End With

End Sub

--

Dave Peterson


--

Dave Peterson