Beeftimer Posted February 18, 2015 Share Posted February 18, 2015 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 Quote Link to comment Share on other sites More sharing options...
Beeftimer Posted February 20, 2015 Author Share Posted February 20, 2015 Have I thoroughly stumped everyone with this one? Quote Link to comment Share on other sites More sharing options...
lrm Posted February 20, 2015 Share Posted February 20, 2015 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. Quote Link to comment Share on other sites More sharing options...
Beeftimer Posted February 24, 2015 Author Share Posted February 24, 2015 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. Quote Link to comment Share on other sites More sharing options...
Jeffrey_B Posted February 24, 2015 Share Posted February 24, 2015 I'll try to reply later on tonight as I should be able to help. Quote Link to comment Share on other sites More sharing options...
Lee Mac Posted February 24, 2015 Share Posted February 24, 2015 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. Quote Link to comment Share on other sites More sharing options...
Jeffrey_B Posted February 25, 2015 Share Posted February 25, 2015 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. Quote Link to comment Share on other sites More sharing options...
Beeftimer Posted February 25, 2015 Author Share Posted February 25, 2015 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 Quote Link to comment Share on other sites More sharing options...
cTran Posted March 12, 2015 Share Posted March 12, 2015 Thanks a billions Jeffrey_B I love this forum Quote Link to comment Share on other sites More sharing options...
Jeffrey_B Posted March 12, 2015 Share Posted March 12, 2015 You're welcome cTran. Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.
Note: Your post will require moderator approval before it will be visible.