VanDyke Software Forums

VanDyke Software Forums (https://forums.vandyke.com/index.php)
-   Scripting (https://forums.vandyke.com/forumdisplay.php?f=14)
-   -   Reading SecureCRT output from Excel VBA (https://forums.vandyke.com/showthread.php?t=14545)

djvj 05-27-2021 03:04 AM

Reading SecureCRT output from Excel VBA
 
I see all the examples provided here

What I haven't been able to find is the ability to read the output from a session created from within Excel, then read the data from SecureCRT back to Excel, parse and write it to specific cells.

For example, I used the example script "ConnectToSecondaryHost_via_JumpHost_ShellDriverExcel.bas" to connect to a device, run a custom script. I want to take the output from that script and put it back into excel. This has to be done from Excel.

Is this possible?

bgagnon 05-27-2021 08:21 AM

Hi djvj,

What you are asking sounds like the Reading and Writing example found at the forum thread you referenced. In what way does that not meet your needs? What parsing needs done to the data you plan to put into Excel?

djvj 05-27-2021 10:43 AM

Hi Brenda,

Reading and Writing example is done from SecureCRT, not from launching SecureCRT within Excel, telling SecureCRT what to do, then bringing the data back to Excel. Basically extending the ConnectToSecondaryHost_via_JumpHost_ShellDriverExcel example to get the output back to the same Excel spreadsheet.

bgagnon 05-27-2021 10:53 AM

Hi djvj,

Sorry, I did not pickup on that distinction. I was thinking session *data* would be stored in Excel and you would use that for the SecureCRT session. Correct, SecureCRT is not externally scriptable, so that's not currently possible.

I have added this thread to a feature request in our product enhancement database to expose CRT object API so external programming editors can reference methods and properties. Should a future release of SecureCRT include this feature, notification will be posted here.

If you prefer direct email notification, send an email to support@vandyke.com and include Feature Request - Forum Thread #14545 in the subject line or use this form from the support page of our website.

djvj 05-27-2021 11:04 AM

Ok Thank you. I was hoping for some ability to do now. I'll play around with the dynamic script option and see if I can figure something out for now. Just not sure an open workbook is writable from outside like that.

bgagnon 05-27-2021 11:05 AM

Hi djvj,

I believe it is because the aforementioned script does it. :)

djvj 05-27-2021 11:17 AM

It does? I thought that was launched from SecureCRT...hmm ok I'll dive into that a little more then.

I thought the end of this meant it was launched from CRT:
' This example Script shows how to read (and write) data to (and from) an
' Excel spreadsheet using VBScript running within SecureCRT.

Thanks.

bgagnon 05-27-2021 11:24 AM

Hi djvj,

Your statement was:
Quote:

Just not sure an open workbook is writable from outside like that.
That is what I responded to. The open workbook is written to by a script that is run from SecureCRT, correct.

djvj 05-27-2021 11:26 AM

Ah right, thanks for the clarification.

bgagnon 05-27-2021 11:44 AM

Hi djvj,

Since your title mentions VBA I see what might be causing confusion.

You can use VBScript in SecureCRT, as was done for those example scripts. SecureCRT supports VBScript (ActiveX scripting languages) and Python. Not VBA.

djvj 05-27-2021 06:49 PM

Right, it can be confusing. But yea since Excel uses VBA, and the syntax is the same as VBS, just thought SecureCRT supported VBA.

Now Python I'm good with if my solution could be had using that language, but Excel doesn't support that. So still back to square one.

UPDATE

I played around with the read/write VBS script example. So it works with writing to excel fine, but its limit is if the workbook is open, it doesn't update it. The workbook has to be closed, which doesn't work for what I'm trying to do. Brings me back to my initial request.

For the time being, I'm going to try and get creative and use a 2nd workbook to control the one I want to update. In my head it should work, but not as intuitive and much more convoluted vs quickly updating a couple of cells.

jdev 06-02-2021 09:36 AM

There's no "SecureCRT" object for Excel VBA to control, manage, or interact with. SecureCRT doesn't expose it's internal scripting interface to any outside elements.

If you want to control everything from within your Excel VBA, then you may want to consider alternative approaches for getting the data back into Excel. For example:
  1. In the script file you're telling SecureCRT to run, add code that writes the output to your "results" file (Not sure how to do this? Refer to the existing example.).
  2. Before launching SecureCRT from within your Excel sheet/code, add code that will delete the "results" file (if it exists, it will already have old information, right?).
  3. Have your Excel macro/VBA code launch SecureCRT with the /Script arg pointing to the script file you want to run. The SecureCRT app will connect as per your instructions and run the script as per your instructions. The script that is running within SecureCRT will take the output/results and write them to the "results" file.
  4. In your Excel VBA code, wait for the SecureCRT process to close or for the "results" file to exist, then read in the data from the "results" file.
  5. Use the data retrieved from the "results" file to update your spreadsheet.

--Jake

djvj 06-05-2021 01:34 AM

Thanks Jake.

I've been writing up an implementation that will do something similar to what you describe in the past week. It's coming along slowly, but getting there.

Hopefully one day SecureCRT can open an API or VBA interface of sorts one day so much of my script can be simplified.

jdev 06-07-2021 08:50 AM

Quote:

Originally Posted by djvj (Post 55148)
Hopefully one day SecureCRT can open an API or VBA interface of sorts one day so much of my script can be simplified.

What kinds of connections are you having SecureCRT make?
  • SSH2
  • SSH1
  • Telnet
  • Other?

What kind(s) of device(s) is SecureCRT connecting to?

What sort of commands are you sending from SecureCRT to these devices once connected?

What kind of output are you hoping to gather?

--Jake

djvj 08-03-2021 08:11 AM

Quote:

Originally Posted by jdev (Post 55155)
What kinds of connections are you having SecureCRT make?
  • SSH2
  • SSH1
  • Telnet
  • Other?

SSH2 and telnet are the 2 I use.

Quote:

Originally Posted by jdev (Post 55155)
What kind(s) of device(s) is SecureCRT connecting to?

My scripts connect to cisco, casa, arris, & nokia devices.

Quote:

Originally Posted by jdev (Post 55155)
What sort of commands are you sending from SecureCRT to these devices once connected?

Getting device counts and status, parsing outputs from show commands like 'show cable modem summary'

Quote:

Originally Posted by jdev (Post 55155)
What kind of output are you hoping to gather?
--Jake

I parse the output using regex, so that's something I can already do relatively easy. But basically run a single command, parse output, then dump into an existing spreadsheet in specified sheet/cell. Move on to next command.

I have this running in VB at the moment, but would be a lot faster if I could talk directly to CRT or possibly even a Python/CRT combo. There are a number of ideas I have in my head.


All times are GMT -6. The time now is 04:20 AM.