How to remove error while extracting table from a saved web page into excel worksheet.

I am extracting table from a saved web page "BAJAJHLDNG.htm" following code contained in file "BOOK177.xlsm"
If I hard-code file name I get the output to "BAJAJHLDNGa.xlsx" properly by following code line.
 .Open "GET", "C:\Users\kakkarsk@hotmail.com\Downloads\exp_2010\BAJAJHLDNG.htm", False

Open in new window

I prefer to get it from a sheet range so if Ichange it to
Filename = WbMaster.Sheets("Sheet2").Range("A" & j).Value
.....
....
 .Open "GET", "C:\Users\kakkarsk@hotmail.com\Downloads\exp_2010\" & Filename & ".htm", False
        .send

Open in new window

This gives me the following error on
.send code

Open in new window

line.
error221015a.jpg
I require help in modification of code so that program take a filename from Sheet range. I have Excel 2016 and VBA program is utilising Excel VBE. I have to automate it further as nealry 2000 web pages are to be processed over a duration of 40 days.
book177.xlsm
BAJAJHLDNG.htm
BAJAJHLDNGa.xlsx
Sunil KakkarAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

regmigrantCommented:
firstly your data is in sheet1 so Filename = WbMaster.Sheets("Sheet2").Range("A" & j).Value picks up nothing at all.
Secondly you need a fully qualified filename wrapped in quote so you will need the path name in the cell (or added by the code) then :

Filename = chr(34) & WbMaster.Sheets("Sheet2").Range("A" & j).Value & chr(34)

if that still doesn't work please come back
Sunil KakkarAuthor Commented:
Hi Regmigrant
It was left behind as sheet2  as I was trying various alternatives  otherwise I was using Sheet1 only. However I am going to tryot your suggestion
regmigrantCommented:
ok, I thought it might just be a typo but thought I should point it out :)

One other thought occurred - I missed that you were adding the pathname in the Get clause, have you tried checking for leading/trailing spaces on the filename?

I use similar sequences all the time so your approach definitely works, which makes me think its the data
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

Sunil KakkarAuthor Commented:
Hi regmigrant,

I have changed the code line as per your instruction as mentioned below.

Filename = Chr(34) & WbMaster.Sheets("Sheet1").Range("A" & j).Value & Chr(34)

Open in new window


Still I am getting the same error.

Thanks

Kind Regards

Sunil
Sunil KakkarAuthor Commented:
Hi regmigrant,

I was having this doubt myself so i removed trailing spaces. Additionally I tried with Trim also. This problem still persists. Debug.Print is printing File name as "BAJAJHLDNG" which seems to be correct.

Kind Regards

Sunil
Sunil KakkarAuthor Commented:
Hi regmigrant,

In the past similiar coding fot taking a value from range has worked for me correctly. I am puzzled why it is not working now. I have already submitted code file Book177.xlsm, you may like to have  a look at it for problem resolution.

Kind Regards,

Sunil
regmigrantCommented:
This must be data related, have you tried adding the path outside the get statement? try the following and debug.print the filename the copy and paste to a browser to see if it will fail with some more usegful information

Filename = WbMaster.Sheets("Sheet2").Range("A" & j).Value

Filename =  "C:\Users\kakkarsk@hotmail.com\Downloads\exp_2010\" & Filename  & ".htm"
....
 .Open "GET", Filename, False
        .send
.....
....
 .Open "GET", "C:\Users\kakkarsk@hotmail.com\Downloads\exp_2010\" & Filename & ".htm", False
        .send
regmigrantCommented:
I looked at your code and made it work with this:
 Filename = "f:\Users\regmigrant\Downloads\" & Trim(WbMaster.Sheets("Sheet1").Range("A" & j).Value) & ".htm"
    
   Debug.Print Filename
    Set WbCopy = Workbooks.Open("f:\Users\regmigrant\Downloads\output.xlsx")
         Set xml = CreateObject("MSXML2.XMLHTTP.6.0")
         With xml
         '.Open "GET", "http://www.nseindia.com/live_market/dynaContent/live_watch/live_index_watch.htm", False
        .Open "GET", Filename, False
        .send

Open in new window

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Sunil KakkarAuthor Commented:
regmigrant is quite helpful and looks for various alternatives to resolve the problem.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.