JSON format and VBA

Topic

New Reply

This is brand new territory, so basically I am asking for directions at this stage.

The next phase of my project requires “communication” to log into and send data and numbers to a server.
The data and numbers are from my project, this I can do and have ready, in excel or a txt file.

But, the company that has that provision for their customers to automate a method via their own means,
( Excel etc) uses JSON format.
The entire script is provided publicly for whoever needs to use it.
My question is, does the company’s server script in JSON format, does this go into VBA Module, or is there more to develop in another type of program that is not Excel VBA, but “talks” with JSON format ?
To Illustrate,
As a retired motor mechanic by trade, this would be termed as a “Adaptor Plate” if one wants to use a Ford Gearbox on a GMH motor.

So, do I need a “adaptor plate” program between Excel VBA and JSON format to log into their server ?

Hope this makes sense.

Thanks

Reply | Quote

Replies

JSON is a data format, like CSV. You either write code to export in the format or use add-ins etc.
This page has it in a VBA module: http://www.rwardell.com/rtw/projects/emitxml.htm
This page has an online converter: http://stackoverflow.com/questions/19187085/how-can-i-quickly-and-easily-convert-spreadsheet-data-to-json

cheers, Paul

Reply | Quote

Here is some additional material that may be of use to generate Json from Excel: http://ramblings.mcpher.com/Home/excelquirks/json

Reply | Quote

Thanks Paul T
I checked out the links, it has xml and stuff I am not familiar with, but thank you.

My current project in the Excel Workbook uses VBA, the web query is VBA from that same company’s public web page which is the data I use, mostly numbers.
This does not need to be changed. The Owners of the server are not asking me or their clients to change anything I do.
What has change, or changing is the way the company wants it’s clients/customers to access the server.
They have provided a specific script to do this, basically.
There are fields to add my username/email/member number;, password and then a sessionID, whatever that is and I don’t know where to get one from, except it’s somewhere accessible.

At the end of *my* processing, lets say on Sheet10, I have the required data they ask for, except this sessionID they also require, for now.

So if my required details are on Sheet10, what else is required of me to do to let their provided script in JASON know what is the Ranges within Sheet10 ? How do each of the languages “talk” ?

Reply | Quote

ruirib , thanks.
I’ll just keep reading, might “get it”

Reply | Quote

ruirib , thanks.
I’ll just keep reading, might “get it”

I fear I cannot help much, as I don’t use JSON from Excel, but the site has multiple links and downloadable projects, that include code that seems can be used with Excel, to convert to and from JSON.

http://ramblings.mcpher.com/Home/excelquirks/json/howcjobject

Reply | Quote

The session ID may be one you make up using a UUID.
http://www.vbaexpress.com/kb/getarticle.php?kb_id=1066

Are you able to post the JSON code or description?

cheers, Paul

Reply | Quote

I fear I cannot help much, as I don’t use JSON from Excel

No prob,

It was un-forseen change, but expected. The company was once thriving with VBA coders to access the server, then it went to XML, (I still stuck around with my proto-type development on test mode in VBA, just to sort out the logic and do the stats.), now that my initial tests and tweaking of the entire workbook, works, and was hoping some of the company’s old stand-alone software was the next step in the process, they jumped onto this JASON.
Perfectly annoyingly normal, gotten used to constant changes in this part of the IT neck of the woods.
I’m not the only one who’s going, “ohh shhh…,”

I did read somewhere on those links, they illustrated where Excel/VBA does OK with XML, but not with the JASON language, for now. From what I understand, has to be converted, I think.
Then I tried to log into their forum to ask specificaly the issue, but first have to become a Google member.

I don’t want to become a Google member, I vote “No”, and wait. :rolleyes:

Thanks.

Reply | Quote

Are you able to post the JSON code or description?

Sent some interim info

Thanks

Reply | Quote

The JSON coding is basically CSV surrounded by braces so there should be no big issue picking out the data. If you need some help you could post some sample data here.

cheers, Paul

p.s. I generally don’t respond to private messages, but am happy to write the response here.

Reply | Quote

OK

here it is..

http://media.tatts.com/documentations/ICustomer/Methods/SellWageringToteBets.html

The, WP, MR, 5, 4, 10 and 0.0 with passwords etc in the code below would be ready in a Sheet in whatever Cell Ranges to suit.
In other words, stopping the process to type this data, (WP, MR, 5, 4, 10 and 0.0 with passwords etc) manually won’t do.

“BetType”: “WP”,
“MeetingCode”: “MR”,
“RaceNumber”: 5,
“Runners”: [4]
“WinInvestment”: 10,
“PlaceInvestment”: 0.0

{
“CustomerSession”: {
“SessionId”: “29012788-71e0-4042-8609-d31a02ea32eb”
},
“Bets”: [
{
“BetType”: “WP”,
“MeetingCode”: “MR”,
“IsPresale”: false,
“RaceNumber”: 5,
“IsRover”: false,
“Selections”: [
{
“Field”: false,
“Runners”: [4]
}
],
“WinInvestment”: 10,
“PlaceInvestment”: 0.0
}
]
}

Reply | Quote

You have a single record with arrays and sub-arrays.
Record = Bets and is an array, square brackets.
Fields = BetType, MeetingCode etc.
Selections is a sub-array and Runners a sub-array of that.

To use the data you need to enumerate each record using some of the code samples linked above.

cheers, Paul

Reply | Quote

Where is the JSON code written on ?
In a Excel VBA module ?
I have provided a workbook to try this and it errors all over the module in reds.

Here is a complete code provided publicly, but no information on where it goes.
This I think this is exactly what’s required.

Code:

Sub Tatts_Login()

Dim sURL As String, sHTML As String
Dim oHttp As Object
Dim userName As String
Dim userPass As String
Dim Body As String
Dim sessionID As String ‘Add this variable to the General Declarations section for use outside of this subroutine

‘‘Change the details below to access your account
‘userName = ‘Your user Number / ID / Email here’
‘userPass = “Your Account Password here”

‘‘Add a reference to the MSXML type library
Set oHttp = CreateObject(“MSXML2.XMLHTTP”)
‘or
‘Set objRequest = New MSXML2.XMLHTTP

‘‘The website to login to your account
sURL = “https://api.tatts.com/sales/vmax/web/account/login”

‘‘ The json string to send. It is paramount that the syntax is correct, the result must return: {“Username”:”yourusername”, “Password”:”yourpassword”, “Dob”:””, “DeviceName”:””, “DeviceKey”:””, “Referrer”:””}
Body = “{“”Username””:””” & userName & “””, “”Password””:””” & userPass & “””, “”Dob””:””””, “”DeviceName””:””””, “”DeviceKey””:””””, “”Referrer””:””””}”

oHttp.Open “POST”, sURL, False
oHttp.setRequestHeader “Content – type”, “application / json”
oHttp.setRequestHeader “Accept”, “application / json”

‘‘Send the login string to the server. Adding parentheses around (Body) forces it to be passed By Value.
oHttp.send (Body)

‘‘Server response.
sHTML = oHttp.responseText

‘‘The sessionID is required for a majority of the calls to the server
sessionID = Mid(sHTML, InStr(1, sHTML, “SessionId”) + 12, 36) ‘InStr(1, sHTML, “}”) – 35)

‘‘Verify that the login was successful.
If Mid(sHTML, Len(sHTML) – 5, 6) = “:true}” Then
MsgBox “Logged ON, SessionID: ” & sessionID

Else

Set oHttp = Nothing
MsgBox “An error occured logging on to the Tatts site. Please re-enter your login details.”
Exit Sub
End If

Set oHttp = Nothing

End Sub

Reply | Quote

I can’t say if this is literally solved, but some help from a referrer got it to work using python as the “go between”, between VBA and JSON.

VBA and JSON, don’t “talk” to each other.

VBA and python do “talk” to each other

python “talks” to JSON.

VBA makes txt files from data inside workbook cell references, the data JSON requires.

python “reads” the txt files made by VBA
or, sends that data within, to JSON.

JSON then works.

However, to get JSON to communicate back is another hill to climb, it has to eventually
get back to VBA, via python, again ?
So VBA can log.txt what JSON is doing, done, or if there was an error.

Conclusion from what I gather and understand,
It was suggested we now learn python.
Personally I do not want to learn python, I can hardly do VBA, what I do is rather basic to say the least.

But if this project with Excel/VBA is to continue, some sort of language other than python needs to be learnt to communicate to JSON via Excel VBA .txt files ?
If not python, what other is there ?

Wish list
Can VBA “talk” directly to JSON ?

But it worked, the result was positive as in the, functions of all manner of things using python as the go-between

“dumb” question.

If then JSON, “new kid on the block” becomes the chosen one for what it supersedes, and what it supersedes currently is on “talking terms” with VBA, what then if VBA and JSON don’t communicate ?

Reply | Quote

Where is the JSON code written on ?
In a Excel VBA module ?
I have provided a workbook to try this and it errors all over the module in reds.

Here is a complete code provided publicly, but no information on where it goes.
This I think this is exactly what’s required.

Code:

Sub Tatts_Login()

Dim sURL As String, sHTML As String
Dim oHttp As Object
Dim userName As String
Dim userPass As String
Dim Body As String
Dim sessionID As String ‘Add this variable to the General Declarations section for use outside of this subroutine

‘‘Change the details below to access your account
‘userName = ‘Your user Number / ID / Email here’
‘userPass = “Your Account Password here”

‘‘Add a reference to the MSXML type library
Set oHttp = CreateObject(“MSXML2.XMLHTTP”)
‘or
‘Set objRequest = New MSXML2.XMLHTTP

‘‘The website to login to your account
sURL = “https://api.tatts.com/sales/vmax/web/account/login”

‘‘ The json string to send. It is paramount that the syntax is correct, the result must return: {“Username”:”yourusername”, “Password”:”yourpassword”, “Dob”:””, “DeviceName”:””, “DeviceKey”:””, “Referrer”:””}
Body = “{“”Username””:””” & userName & “””, “”Password””:””” & userPass & “””, “”Dob””:””””, “”DeviceName””:””””, “”DeviceKey””:””””, “”Referrer””:””””}”

oHttp.Open “POST”, sURL, False
oHttp.setRequestHeader “Content – type”, “application / json”
oHttp.setRequestHeader “Accept”, “application / json”

‘‘Send the login string to the server. Adding parentheses around (Body) forces it to be passed By Value.
oHttp.send (Body)

‘‘Server response.
sHTML = oHttp.responseText

‘‘The sessionID is required for a majority of the calls to the server
sessionID = Mid(sHTML, InStr(1, sHTML, “SessionId”) + 12, 36) ‘InStr(1, sHTML, “}”) – 35)

‘‘Verify that the login was successful.
If Mid(sHTML, Len(sHTML) – 5, 6) = “:true}” Then
MsgBox “Logged ON, SessionID: ” & sessionID

Else

Set oHttp = Nothing
MsgBox “An error occured logging on to the Tatts site. Please re-enter your login details.”
Exit Sub
End If

Set oHttp = Nothing

End Sub

The JSON here is this bit:

Code:

{“Username”:”yourusername”, “Password”:”yourpassword”, “Dob”:””, “DeviceName”:””, “DeviceKey”:””, “Referrer”:””}
Body = “{“”Username””:””” & userName & “””, “”Password””:””” & userPass & “””, “”Dob””:””””, “”DeviceName””:””””, “”DeviceKey””:””””, “”Referrer””:””””}”

Is this all you need to send?

The rest of the code is mostly to communicate with the server.

Reply | Quote

ruirib
I don’t know exactly what type of environment you are running on

At the moment my main PC and 2 test PC’s are Win XP, ex corporate office IBM, they get rid of them for a tax write-off and a song.

Major upgrade soon, just waiting for what goes with Win 10, no hurry, but it is inevitable because the server and the company, the public section have already stated that soon anything less than Win7 won’t be allowed and anything less than IE9 also won’t be allowed.
The “best” XP can do is IE8.

I am thinking of simply going to Win 7 wait around, see what goes.
Not really too keen on Win 10 and MS’s subscription policies etc.

FYI, this project has one entire objective; that is in real time gather/scrape information from a web page using VBA dynamic web query, crunch numbers, evaluate, statistics, graphs, strategies and a lot more, too much to add.

It is based on hands free wagering systems, the very first type was before the internet, it’s architecture was based around windows 95/98 environment. Before in it was DOS with GUI, everything had to be typed in.

There was a version of this type of number crunching, semi-auto using Assembly.

My point is, many have or are trying to successfully experiment using similar source of real-time web page data.
Since then, many of these types of “systems” have fallen over, downtime after downtime and IT evolving mush faster than most can keep up with, only IT academics stay in the play,( or those who can afford to pay for one), but as hobby or full on profession.
(In layman’s terms, a mechanical engineer leaves a stable job and decides to offer his/her profession in Formula 1 Racing, unstable because of the risk factor when the engineer has no control when someone else is in the driver’s seat).

For me this is not a hobby, but I am no way capable of learning new things all the time, therefore this VBA project is infinitely “proto-type”.
Meaning, if I can just get some sort of statistical evidence to allow me do what I do better than, get a home run without revealing too much of my own strategy, then I can show someone and say, “here, take a look at this architecture, how does it go to stand-alone, BUT, free open source?; so the constant evolutionary changes in IT on this particular interest is not cost bearing and limiting experiments of probability from a,theory, to b,practice, and then c,fact” ?

Well, I’ve done b and it appeared good enough to move on to c.
That’s why I am at, at the point of communicating with the server.

2 Years ago it was simple, the server’s propriety owners provided their customers with their own free software to communicate via a .txt file place in que inside a directory.
That free public software is soon to be decommissioned, so they provided an alternative, hence JSON which is a new addition as far a I’m aware of, till the next “new thing”, and then we start over all again.

That’s why I insisted on open source, things keep changing in this neck of the woods, hopefully the savvier IT people can pitch in from time to time. Bottom line, it’s sport just like NASCAR or Formula 1, ( probability and risk).
Hope this explained your question about, “environment”.

—,

but if you are considering Python, I would probably think using .Net would be a logical alternative.

I never knew anything about python or it’s existence till about a month ago.
All I found out is that VBA won’t “talk” directly to JSON that I am aware of.
—,

I would probably think using .Net would be a logical alternative. .Net supports multiple languages, including Visual Basic.Net (VB.Net), which shouldn’t be too strange to someone who uses VBA. .Net is a rich environment, with a lot of open source code available to consume and communicate JSON. Visual Studio, Microsoft’s premier development IDE, uses Json.NET, a free, open source framework.

I have zilch-zero-nada, no experience with any of those.
All I have is statistics and architecture from own research.
—,

I am not sure what you are trying to do and how you are trying to do it.

Neither am I !
—,

The JSON here is this bit:

Code:
{“Username”:”yourusername”, “Password”:”yourpassword”, “Dob”:””, “DeviceName”:””, “DeviceKey”:””, “Referrer”:””}
Body = “{“”Username””:””” & userName & “””, “”Password””:””” & userPass & “””, “”Dob””:””””, “”DeviceName””:””””, “”DeviceKey””:””””, “”Referrer””:””””}”
Is this all you need to send?

The rest of the code is mostly to communicate with the server.

The username and password, since posting here last, I got it to work, it got the sessionID, the log-in and the last bit.
All was by my instance to use a .txt file/s made by VBA, and python’s script reads the txt files and sends that data to the server.
That phase worked, it got a home run. The entire “Loop” joined, finally for this initial phase.
They insisted python “talk” directly to Excel bypassing the use of txt files.
Well I insisted No, because if that was to have been the case, I be “trapped” and have to use something I have no experience with. How the heck to I know what’s in those scripts ?

However there is more, one final “home run” which is, the communication back from the server after the login, is the next thing to do. It’s official code title is “RESPONSE”
This code sample is also publicly available.
http://media.tatts.com/documentations/ICustomer/Methods/SellWageringToteBets.html

Look for the first code sample heading with the words that includes RESPONSE, the first sample is REQUEST.
I have up to REQUEST in Python scripts and it all worked as mention earlier in this post.
It may be interesting to see what .NET scripting may look like script form for, RESPONSE ?
To evaluate which which way to go with the project at this point
—,

Thanks.

Reply | Quote

I never knew anything about python or it’s existence till about a month ago.
All I found out is that VBA won’t “talk” directly to JSON that I am aware of

This is not true. JSON is just a text file and VBA can easily manipulate text files. The issue is that you must understand the format of JSON to use it and this is where the code samples provided come in.

cheers, Paul

Reply | Quote

Paul T

This is not true. JSON is just a text file and VBA can easily manipulate text files. The issue is that you must understand the format of JSON to use it and this is where the code samples provided come in.

cheers, Paul

Thanks,
There is always something “new” to understand
—,

ruirib
Hmmm… Let’s go by parts:

Your VBA script had several issues. I think I corrected most, that were due to the use of bad quote characters. I uploaded a new version of the zip file, with the corrections.

It errored when tried to test,
but then all of a sudden had to do some re-shuffling with the 3 computers, swap files etc etc, cos
one of the IBMs had a “hernia”,
BSoD, twice this week. I think it’s HD, acting up lately, noisy.
————–,
Anyway,

I have uploaded an actual working version of the process in question.
It has those py scripts, they worked because it actually got the sessionID, logged in and sent the required data. Checked my account on-line and sure enough, it worked.
The scripts were made by one who insisted it be done another way as far as picking up my txt files for username/password etc etc as described in previous post.

But also, there is a script that needs wanting and is missing, the RESPONSE.

So I was thinking, if this works up to this point, and there are more solutions such as .NET etc, perhaps if one can re-write, I’ll test them.
I need to “see” what and when the scripts fire up by way of it’s each own unique log.

But most importantly, very important, and I say this from past experience with propriety software of this type, and that is the following;

The last script that gets Run, is like a “one shot”, it only allows the bet to be sent once, it does not “know” if it “missed”, there is no response.
These misses when they happen, there has to be a up to and including 3 tries, and if they all miss, well that’s too bad, that’s the way it is.
The reason they miss when eligible, ( eligible means, “before the cut-off time”, the race has not started yet) is because of server over-load. It does happen usually at the last -30 seconds before a race is due to start.
There is an avalanche of others trying to do the same, and they all miss to if they don’t have a re-send.
Problem is, how does “it” know it missed ? The only script left to do is the RESPONSE, it does not seem to say much more.
There is one avenue and this may be tedious and not worth the effort, and that is, account balance before and account balance after, and again this is not sure, because it takes about a minute or so for the server’s other functions to update.
This is not necessary right away, but something I will keep an eye on when testing.

So, the upload is 3 py scripts, it uses python 3.4 32 bit.

Paul, if the JSON is as simple as you say it is, then I need to see a working example of how VBA does this, much appreciated.

I don’t know what else to say or ask at this point, it’s somewhat “blinding” it’s outside of what I’m used to, the VBA thing

Thanks

Reply | Quote

I don’t know exactly what type of environment you are running on, but if you are considering Python, I would probably think using .Net would be a logical alternative. .Net supports multiple languages, including Visual Basic.Net (VB.Net), which shouldn’t be too strange to someone who uses VBA. .Net is a rich environment, with a lot of open source code available to consume and communicate JSON. Visual Studio, Microsoft’s premier development IDE, uses Json.NET, a free, open source framework.

I am not sure what you are trying to do and how you are trying to do it. .Net (either natively or through a gazillion of frameworks and toolkits, some free, such as Syncfusion’s Enterprise Studio) supports accessing and processing Excel files, in a very similar way to what you do with VBA (it can be actually based on the same DLL’s VBA uses). So, it would seem to me that a .Net app, written in any of the support languages (be it VB.NET or any other – my favorite is C#), would be the “obvious” way to accomplish what you need. Not only can you process JSON in .Net, but you can also communicate with any server, sending and receiving JSON while you do it.

I am not knowledgeable enough about it, but I would even be looking at the possibility of using a framework such as Json.Net from VBA. I am sure it is not that far-fetched.

P.S.: Yep, a quick search showed a lot of interesting bits, including this:

How to: Access DLLs in Excel

It’s not the nicest syntax and it’s definitely not the easiest way to do it, especially if you are not used to using DLLs and such. if that is the case, maybe it would be worthwhile to explore the .Net possibility. It seems way more natural than getting Python into the mix. Access uses .Net, so …

P.S.2: The links I posted earlier seem still valid to me. You can do it from Excel VBA directly:

http://ramblings.mcpher.com/Home/excelquirks/json/excel-json-conversion

Seems easier than learning a new language.

P.S.3: Answering some of your questions. JSON is simply a text based format, not a language. So, it really doesn’t make sense to ask the VBA can talk to JSON and vice-versa. Surely you can generate a text file from VBA and you can read from a text file in VBA. If you can do that, you can write and read JSON. The issue is that, depending on what you want to communicate through JSON, generating JSON manually and back, from VBA, may be a lot more work than it needs to be. That’s where libraries come into play.

Reply | Quote

Hmmm… Let’s go by parts:

Your VBA script had several issues. I think I corrected most, that were due to the use of bad quote characters. I uploaded a new version of the zip file, with the corrections.

Code:

Sub Tatts_Login()

Dim sURL As String, sHTML As String
Dim oHttp As Object
Dim userName As String
Dim userPass As String
Dim Body As String
Dim sessionID As String 'Add this variable to the General Declarations section for use outside of this subroutine

'‘Change the details below to access your account
userName = Sheets("Example GUI").Range("C11").Value
'userName = 'Your user Number / ID / Email here'
'
userPass = Sheets("Example GUI").Range("C12").Value
'userPass = “Your Account Password here”

'‘Add a reference to the MSXML type library
Set oHttp = CreateObject(“MSXML2.XMLHTTP”)
'‘or
Set objRequest = New MSXML2.XMLHTTP

'‘The website to login to your account
sURL = "https://api.tatts.com/sales/vmax/web/account/login"

'‘ The json string to send. It is paramount that the syntax is correct, the result must return: {“Username”:”yourusername”, “Password”:”yourpassword”, “Dob”:””, “DeviceName”:””, “DeviceKey”:””, “Referrer”:””}
Body = "{""Username"":""" & userName & """, ""Password"":""" & userPass & """, ""Dob"":"""", ""DeviceName"":"""", ""DeviceKey"":"""", ""Referrer"":""""}"

oHttp.Open "POST", sURL, False
oHttp.setRequestHeader “Content - type”, “application / json”
oHttp.setRequestHeader “Accept”, “application / json”

'‘Send the login string to the server. Adding parentheses around (Body) forces it to be passed By Value.
oHttp.send (Body)

'‘Server response.
sHTML = oHttp.responseText

'‘The sessionID is required for a majority of the calls to the server
sessionID = Mid(sHTML, InStr(1, sHTML, "SessionId") + 12, 36) 'InStr(1, sHTML, “}”) – 35)

'‘Verify that the login was successful.
If Mid(sHTML, Len(sHTML) - 5, 6) = ":true}" Then
MsgBox "Logged ON, SessionID: " & sessionID

Else

Set oHttp = Nothing
MsgBox "An error occured logging on to the Tatts site. Please re-enter your login details."
Exit Sub
End If

Set oHttp = Nothing

End Sub

41444-JSON-PROJECT-1

I think I have an idea of what you want to do. This previous script simply would simply log you in

With .Net, there is really no “scripting”. You’d write a small app to do what you need.

To get the response and convert it to something useful from the .Net side, you’d use a small statement similar to:

Code:

BetResponse response = JsonConvert.DeserializeObject(sHTML);

This would presume that you defined what “BetResponse” is (I would define a class for that) and that sHTML contains the response from the server.

So it is conceptually simple, but presumes you’d have code to generate the request, as well – one class to define ToteBet and then the code to submit such a class to the server.

Reply | Quote

Sending the request and handling the reply would be rather simple, in C#.

You have several examples here:

http://stackoverflow.com/questions/4015324/http-request-with-post

With XP, Method 2 of the first answer would be the base to build something (I think .Net 4.5 is not available for XP, so Method 3 is not available).

In either method, you’d need to get the values from the file, convert them to JSON, perform the request, get the reply, convert it from JSON and write to the txt file.

Of course, the best method would be to read the values you need from Excel, directly and write the answer back to Excel, as well. This would require a bit more work.

Reply | Quote

As I don’t have a system to test this on I don’t want to attempt to make a version in VBA, or a python one. Happy to help in other ways.

cheers, Paul

Reply | Quote

I think I found something, and now at a glance, the py advocate may have a touch of bias, for insisting there is no other !
hmmm

http://ramblings.mcpher.com/Home/excelquirks/json

Now all I gotta do is search for something relatable among a very long list of examples, and convert the py scripts back to VBA !
yayy
🙂

Reply | Quote

Nearly solved, but a question regarding some batch files I had to make up from various examples

One in method particular,
VBA is to execute a .bat file, which in turns executes a script.
VBA coding did not seem to want to fully execute the same script, although clicking onto the said script, it works fine, and clicking onto the .bat file that execute the script worked fine also, but running a
Sub/ End Sub did nothing, yet no errors. The “did nothing” means, the script did not run.

So I found a way I think “tricks” Windows, if that is the correct terminology.

Code:

”’File name of script is: Script1

Option Explicit
Sub Run_Script1_bat()

  Dim PathCrnt As String

    ChDir “C:Folder_BAT_file_is_in”

  PathCrnt = ActiveWorkbook.Path

  Call Shell(PathCrnt & “Run_Script1.BAT ” & PathCrnt)
End Sub

————
File Name of bat file is: Run_Script1
The batch file code has:

Code:

@echo off
C:Folder_script_file_is_inscript1.py %*

The question is;

I need to have 4 ,(or later on progressively many more) of these all individually named
in one Module, do I need to have Option Explicit for each each and every Sub / End Sub,

or will Option Explicit become Global for that Module ?

Thanks

Reply | Quote

After a long winded approach, it’s finally worked out with collaboration in and out of this forum.
It’s at the time “new” to most, but in the end it required a bit of python, VBA and .bat improvisation to get it to function as a strategy required.
I think in time I’ll learn more of coding beyond my basic VBA to have a 100% VBA Run, or 100% python or whatever else to make a 3rd party’s requirements “communicate”.
An outsource worker said they could have made it work with 100% VBA/JSON, but the price was out of reach and I don’t really think they understood my “project” in it’s entirety.
But, it’s working, and now on to the next piece of the puzzle, at least this one is out the way.

Reply | Quote