Changing Excel Sheets Without Having to Close and Reopen Spreadsheet

I'm working on an AutoLISP code that automatically puts drawing data into an existing spreadsheet. So far, I've gotten tremendous help from this forum, and now I'm looking for more.

I've been using the GetExcel LISP (which I've attached), that I found online, in conjunction with my own code. In my routine, I will be putting data into several sheets within the same Excel document.

Currently, the only option I'm seeing to do this is with the (openExcel...) command in the GetExcel routine (one of the arguments for the command is the sheet name). Hence, as it is right now, the only way for me to switch sheets is to open the spreadsheet on the proper sheet, place the data on that sheet, close the spreadsheet and reopen to the next sheet, put data in that sheet and... you get the idea. This is troublesome for several (perhaps obvious) reasons.

Any ideas on how to switch sheets without having to close and reopen the entire document each time?

GetExcel:

getExcel.txt

Have I thoroughly stumped everyone with this one?

I have no experience with linking AutoLISP and Excel so this may be totally irrelevant but it might be worth a look at combining some Excel/VBA code to control sheet selection. For example, the following Excel/VBA code would successively open sheets2, 3, and 4 for 5 seconds.

Sub MyMacro()
   Sheets("sheet2").Select
   Application.Wait (Now + TimeValue("0:00:05"))
   Sheets("sheet3").Select
   Application.Wait (Now + TimeValue("0:00:05"))
   Sheets("sheet4").Select
End Sub

I would also look into something like the VBA DoEvents command to force intermediate event execution.

From what I've seen here and elsewhere, I guess I just need to learn visual basic. Not familiar with it at all, but then again I wasn't at all familiar with LISP a couple months ago. Thanks for the response.

I'll try to reply later on tonight as I should be able to help.

From what I've seen here and elsewhere, I guess I just need to learn visual basic. Not familiar with it at all, but then again I wasn't at all familiar with LISP a couple months ago. Thanks for the response.

Don't bother with VBA, it is no longer supported.

Your request is certainly possible to achieve using Visual LISP with ActiveX, however, I unfortunately cannot post my Excel code to the public domain - in short, you would need to iterate over the Worksheets collection of the Workbook object.

This is the section of code you need to work with and what Lee Mac was referring to.

I've copied the following code from the text file you attached to the original post.

(if SheetName$
(vlax-for Worksheet (vlax-get-property *ExcelApp% "Sheets")
(if (= (vlax-get-property Worksheet "Name") SheetName$)
(vlax-invoke-method Worksheet "Activate")
);if
);vlax-for
);if

If SheetName$ is anything but nil, the program will iterate the "Sheets" collection of the current active workbook of the Excel object. With each iteration, the "Name" of each sheet is compared to the supplied SheetName$ string. If equal, the worksheet is made active.

Knowing that you can create a small Defun to call whenever you want to switch sheets.

;Notes
;*ExcelApp% must already be defined as the Excel object
;Syntax
;(SwitchSheet SheetName$)
;SheetName$ must be a string of the name to be changed to

(defun SwitchSheet ( SheetName$ / )
(vlax-for Worksheet (vlax-get-property *ExcelApp% "Sheets")
(if (= (vlax-get-property Worksheet "Name") SheetName$)
(vlax-invoke-method Worksheet "Activate")
);if
);vlax-for
);defun

Please let me know if this helps.

I will certainly take a look later on when I have time and I'll let you know if it helps. Thank you for your time.

Beef

Thanks a billions Jeffrey_B

I love this forum

You're welcome cTran.