Issue with VBA Code

Question

Issue with VBA Code

Closed this issue 9 years ago · 15 comments

Hi when I'm trying to run the macro I get the following error;

The system cannot locate the object specified.

on debug, it points to the line of code;

Call xmlReader.parseURL(fileURL) 'xmlReader calls jiraXmlToTableCopier

I've also added the code to another workbook that then produces graphs based on the data that would be imported, but when running that version I get a different error of

Run-time error '440'
Automation error

On debug

    Call importerConfig.Init(ActiveSheet)```


Any ideas what could be causing either of these issues ?

Nick

Answer 1 · 2015-09-18T13:22:49.000Z

Hi Nick,
have you added a reference to the sax xml library that is needed?

I think I wrote a note about it, please check. If not, I can check.
BR! /marcus

Answer 2 · 2015-09-18T13:45:01.000Z

This is what I have in my references dialogue:

(Open VBA editor, menu Tools -> References)

Answer 3 · 2015-09-18T15:09:33.000Z

Hi yes. I have the same checked. Does hay cover the SAX xml or is that
separate?

Thanks for the help.

The comments in the code have helped loads too.

Nick
On Fri, 18 Sep 2015 at 14:45, Marcus notifications@github.com wrote:

This is what I have in my references dialogue:

[image: image]
https://cloud.githubusercontent.com/assets/559199/9960649/11c0c340-5e1c-11e5-8602-e0c205e52230.png

(Open VBA editor, menu Tools -> References)

—
Reply to this email directly or view it on GitHub
#3 (comment).

Answer 4 · 2015-09-18T16:20:51.000Z

Microsoft.xml covers it.

I am unsure of what the issue is.
have you modified the script at all? You write that you have added the code to another workbook.

DId you first test the downloaded workbook?
if not, please do so and see if you can get it working without any modifications to the code.

Answer 5 · 2015-09-20T18:44:21.000Z

Hi Marcus, no I haven't modified the code at all, just downloaded the
version from GitHub and tried to run it.

I've changed the connection settings within the sheet but that's it.

Is there a URL I can try directly to make sure it's not being blocked or
anything ?

Nick

On 18 September 2015 at 17:20, Marcus notifications@github.com wrote:

Microsoft.xml covers it.

I am unsure of what the issue is.
have you modified the script at all? You write that you have added the
code to another workbook.

DId you first test the downloaded workbook?
if not, please do so and see if you can get it working without any
modifications to the code.

—
Reply to this email directly or view it on GitHub
#3 (comment).

Answer 6 · 2015-09-20T21:27:12.000Z

I think I need to run the code myself in order to check this.

Looking at the code briefly, it looks as if it may be geared towards reading from the testdata file. If you are running it the first time, you need to ensure that you are running the code that tries to access the jira server. The lines may be commented either way in the module named modJira. Either they use the JiraXMLGet to query the jira server or just fetch the XML testdata file.

The line you refer to seem to say "fileurl" which leads me to be believe it is trying to get testdata from a file.

Answer 7 · 2015-09-21T11:12:35.000Z

Hi Marcus. I've swapped the comment over so it's now trying to access the
JIRA server. But it's still has the same error.

:(

On Sun, 20 Sep 2015 at 22:27, Marcus notifications@github.com wrote:

I think I need to run the code myself in order to check this.

Looking at the code briefly, it looks as if it may be geared towards
reading from the testdata file. If you are running it the first time, you
need to ensure that you are running the code that tries to access the jira
server. The lines may be commented either way in the module named modJira.
Either they use the JiraXMLGet to query the jira server or just fetch the
XML testdata file.

The line you refer to seem to say "fileurl" which leads me to be believe
it is trying to get testdata from a file.

—
Reply to this email directly or view it on GitHub
#3 (comment).

Answer 8 · 2015-09-21T11:52:15.000Z

Check that you also have uncommented the instantiation of the jiraxmlget
object some lines up.

On Mon, Sep 21, 2015 at 1:12 PM, Nick Wilson notifications@github.com
wrote:

Hi Marcus. I've swapped the comment over so it's now trying to access the
JIRA server. But it's still has the same error.

:(

On Sun, 20 Sep 2015 at 22:27, Marcus notifications@github.com wrote:

I think I need to run the code myself in order to check this.

Looking at the code briefly, it looks as if it may be geared towards
reading from the testdata file. If you are running it the first time, you
need to ensure that you are running the code that tries to access the
jira
server. The lines may be commented either way in the module named
modJira.
Either they use the JiraXMLGet to query the jira server or just fetch the
XML testdata file.

The line you refer to seem to say "fileurl" which leads me to be believe
it is trying to get testdata from a file.

—
Reply to this email directly or view it on GitHub
<#3 (comment)
.

—
Reply to this email directly or view it on GitHub
#3 (comment).

Answer 9 · 2015-09-21T12:53:24.000Z

Yes. Just found those. I've commented those and now it returns an error of
'the server name or address could not be resolved'. So I think this is
progress

Nick
On Mon, 21 Sep 2015 at 12:52, Marcus notifications@github.com wrote:

Check that you also have uncommented the instantiation of the jiraxmlget
object some lines up.

On Mon, Sep 21, 2015 at 1:12 PM, Nick Wilson notifications@github.com
wrote:

Hi Marcus. I've swapped the comment over so it's now trying to access the
JIRA server. But it's still has the same error.

:(

On Sun, 20 Sep 2015 at 22:27, Marcus notifications@github.com wrote:

I think I need to run the code myself in order to check this.

Looking at the code briefly, it looks as if it may be geared towards
reading from the testdata file. If you are running it the first time,
you
need to ensure that you are running the code that tries to access the
jira
server. The lines may be commented either way in the module named
modJira.
Either they use the JiraXMLGet to query the jira server or just fetch
the
XML testdata file.

The line you refer to seem to say "fileurl" which leads me to be
believe
it is trying to get testdata from a file.

—
Reply to this email directly or view it on GitHub
<
#3 (comment)
.

—
Reply to this email directly or view it on GitHub
<#3 (comment)
.

—
Reply to this email directly or view it on GitHub
#3 (comment).

Answer 10 · 2015-09-21T13:15:12.000Z

I added a watch to the URL and found it added an extra '/' in after the main URL.

I've also found that the temp URL is forbidden on our server so I've changed that to a query reference and it still shows the same error.

URL now looks like this.

https://jira.demo.co.uk/sr/jira.issueviews:searchrequest-xml/34112/SearchRequest.xml?jqlQuery=project%20%3D%20demo&tempMax=1000

And if I enter it manually it works fine.

But I've just realised that it's only now looking at the query linked to 341112.

Answer 11 · 2015-09-21T21:01:45.000Z

OK, 'the server name or address could not be resolved'.
Nick, cant help you much with the URL. That part is basically standard.
I do believe that I queried the jira server I was working with at the time
using just a browser client to get the resultset.
So, I would try accessing it with a browser to start with.

On Mon, Sep 21, 2015 at 3:15 PM, Nick Wilson notifications@github.com
wrote:

I added a watch to the URL and found it added an extra '/' in after the
main URL.

I've also found that the temp URL is forbidden on our server so I've
changed that to a query reference and it still shows the same error.

URL now looks like this.

https://jira.company.co.uk/sr/jira.issueviews:searchrequest-xml/34112/SearchRequest.xml?jqlQuery=project=DEMO%20AND%20issuetype%20=%20Bug

—
Reply to this email directly or view it on GitHub
#3 (comment).

Answer 12 · 2015-09-22T12:45:43.000Z

OK, thanks. The URL works directly, but not via excel, although I think it could be a proxy issue, so I will carry on working it through, really keen to get it working as it will save so much time.

Thanks for your help.

Nick

Answer 13 · 2015-09-22T14:00:33.000Z

Yes, proxy is likely culprit. I had the server on a local network, so it
did not need a proxy at that time.

There should be a way to get it to use the proxy settings configured in
windows, if that is your situation.

Let me know if you get stuck.

On Tue, Sep 22, 2015 at 2:45 PM, Nick Wilson notifications@github.com
wrote:

OK, thanks. The URL works directly, but not via excel, although I think it
could be a proxy issue, so I will carry on working it through, really keen
to get it working as it will save so much time.

Thanks for your help.

Nick

—
Reply to this email directly or view it on GitHub
#3 (comment).

Answer 14 · 2015-09-24T16:46:14.000Z

Hi Marcus, just to update you the proxy was indeed the issue. Managed to fix it using.

MyRequest.SetProxy 2, "proxy_address_here", "" 'Set proxy

Thanks for your help and patience.

Answer 15 · 2015-09-24T18:06:57.000Z

Thanks for the followup Nick.
Its obvious I need to fix the commented lines.

Let me know if you run into any other snag, now that you actually have it working... :-)