Switching between workbooks

[Fan924]

Excel 97. Trying to follow an example posted to this BBS and I am
getting errors. I am trying to switch easily between workbooks for
copy and paste operations. I need some help.

Sub test()
Dim wb1 As Workbook
Dim wb2 As Workbook
If Workbooks.Count = 2 Then
Set wb1 = ActiveWorkbook
Set wb2 = "c:\library.xls"
End If

Error message reads "Compile error type mismatch" for wb2

 

[Chip Pearson]

You have declared wb1 and wb2 as Workbook objects, but in the line of code

Set wb2 = "c:\library.xls"

you are attempting to put a string of text in the wb2 variable. (VBA doesn't
see this as a workbook name or file name -- it is simply a series of text
characters) and thus you are getting the error. Change that line to

Set wb2 = Workbooks("Library.xls")

This assumes that the file Library.xls is already open in Excel. If it is
not, use the following instead:

Set wb2 = Workbooks.Open("c:\library.xls")

In the first line of code, you do not (and indeed cannot) use the drive and
folder path, since Excel allows only one workbook having the same file name
to be open at the same time. For example, you cannot have both
C:\Test\Book1.xls and C:\Test2\Book1.xls open at the same time, since both
have the same file name (file name only, not drive or folder name). In
second example, you should provide the full file name, including the drive
and folder names.

If you don't know whether C:\Library.xls is open or not, you can use code
like

On Error Resume Next
Set wb2 = Workbooks("Library.xls")
If wb2 Is Nothing Then
' file was not open. open it now:
Set wb2 = Workbooks.Open("C:\Library.xls")
End If

This code attempts to access an open workbook named "Library.xls". If there
is no such workbook open, it opens it (assuming the file exists).

--
Cordially,
Chip Pearson
Microsoft MVP - Excel, 10 Years
Pearson Software Consulting
www.cpearson.com
(email on the web site)

 

[Fan924]

Sub Create()
Dim wb1 As Workbook
Dim wb2 As Workbook
Set wb1 = ActiveWorkbook
On Error Resume Next
Set wb2 = Workbooks("C:\Library.xls")
If wb2 Is Nothing Then
Set wb2 = Workbooks.Open("C:\Library.xls")
End If

Thanks Chip for all the time you saved me. It all worked fine until I
added the last section. Now it works if Library.xls is closed. If it
is already open, I get an error message. What am I doing wrong now?

 

[Fan924]

OK. Fixed it.Had to pu it aside and reread your post again. The
section on a file already open. Works great now. Thanks.

Sub Create()
Dim wb1 As Workbook
Dim wb2 As Workbook
Dim MapType As Variant
Dim MapLineNo As Variant
'Dim HexNumberHigh As Variant
Set wb1 = ActiveWorkbook
On Error Resume Next
Set wb2 = Workbooks("Library.xls")
If wb2 Is Nothing Then
Set wb2 = Workbooks.Open("C:\Library.xls")
End If

Is there a way to close workbook WB2 at the end of a macro?