Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Switching between workbooks
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Switching between workbooks
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" wrote in message ups.com... 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Switching between workbooks
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? |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Switching between workbooks
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? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Switching between Workbooks | Excel Discussion (Misc queries) | |||
Switching between workbooks | Excel Discussion (Misc queries) | |||
Switching Between 2 workbooks using VBA | Excel Programming | |||
switching between workbooks... | Excel Programming | |||
Switching Workbooks in vbscript | Excel Programming |