Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Need help with macro
Dear friends,
I need help in a macro In an excel file, I have a webquery. I wrote a macro to get certain details from the webpage. url is http://www.buzzingstocks.com/in/btest.pl?t=SUZLON&f=2 (I want to get "Date" "Last Recommendation" "Return on Investment" & "Annualized returns" for each company Sheet1 has the web query. name of the query is "btest" Sheet2 has folowing data SYMBOL , NAME OF COMPANY , Date , Type , ROI , AR 20MICRONS , 20 Microns Limited , , , , 3IINFOTECH , 3i Infotech Limited , , , , 3MINDIA , 3M India Limited , , , , AARTIDRUGS , Aarti Drugs Ltd. , , , , AARTIIND , Aarti Industries Ltd. , , , , AARVEEDEN , Aarvee Denims & Exports Limited , , , , ABAN , Aban Offshore Ltd. , , , , Above is a sample data, In all I have 1500 rows of data I want the macro to fill up the columns C D E F Here is the macro Sub Macro1() Dim ctr As Long For ctr = 2 To 8 'Cells(Rows.Count, "a").End(xlUp).Row On Error Resume Next With Sheet1 With .QueryTables("btest") .Connection = _ "URL;http://www.buzzingstocks.com/in/btest.pl?" _ & "t=" & Cells(ctr, "a") & "&f=2" End With Cells(ctr, "c").Value = .Cells(26, "a").Value Cells(ctr, "d").Value = .Cells(26, "i").Value Cells(ctr, "e").Value = .Cells(28, "d").Value Cells(ctr, "f").Value = .Cells(28, "g").Value End With Next ctr ThisWorkbook.Save End Sub Somehow it does not fetches entries for few companies. Please help |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Need help with macro
I couldn't get the query top work. Instead of using an internet explorer to
get the data. It puts the results on sheet 1 using Column A and Column B from sheet 2. Sub WebQuery() With Sheets("Sheet1") .Cells.ClearContents .Range("A1") = "SYMBOL " .Range("B1") = "Name of Company" .Range("C1") = "Date" .Range("D1") = "Day's Close" .Range("E1") = "Action" .Range("F1") = "Shares Held" .Range("G1") = "Market Value" .Range("H1") = "Cash Assets" .Range("I1") = "Total Assets" .Range("J1") = "ROI(%)" .Range("K1") = "Recommendations" RowCount = 2 End With Set IE = CreateObject("InternetExplorer.Application") IE.Visible = True With Sheets("Sheet2") LastRow = .Range("A" & Rows.Count).End(xlUp).Row For ctr = 2 To LastRow Symbol = .Range("A" & ctr) Company = .Range("B" & ctr) URL = "http://www.buzzingstocks.com/in/btest.pl?" & _ "t=" & Symbol & "&f=2" 'get web page IE.Navigate2 URL Do While IE.readyState < 4 DoEvents Loop Do While IE.busy = True DoEvents Loop Set Tables = IE.document.getElementsByTagname("Table") 'find correct table tableNum = 0 For Each tbl In Tables MyStr = "Trading Methodology:" If Left(tbl.innertext, Len(MyStr)) = MyStr Then Exit For End If tableNum = tableNum + 1 Next tbl Set DataTable = Tables(tableNum) Start = False For Each MyRow In DataTable.Rows If Start = False Then If Left(UCase(MyRow.innertext), 4) = _ "DATE" Then Start = True End If Else If MyRow.innertext = "" Then Exit For End If With Sheets("Sheet1") .Range("A" & RowCount) = Symbol .Range("B" & RowCount) = Company ColCount = 3 For Each Mycol In MyRow.Cells .Cells(RowCount, ColCount) = _ Mycol.innertext ColCount = ColCount + 1 Next Mycol RowCount = RowCount + 1 End With End If Next MyRow Next ctr End With End Sub "Maxi" wrote: Dear friends, I need help in a macro In an excel file, I have a webquery. I wrote a macro to get certain details from the webpage. url is http://www.buzzingstocks.com/in/btest.pl?t=SUZLON&f=2 (I want to get "Date" "Last Recommendation" "Return on Investment" & "Annualized returns" for each company Sheet1 has the web query. name of the query is "btest" Sheet2 has folowing data SYMBOL , NAME OF COMPANY , Date , Type , ROI , AR 20MICRONS , 20 Microns Limited , , , , 3IINFOTECH , 3i Infotech Limited , , , , 3MINDIA , 3M India Limited , , , , AARTIDRUGS , Aarti Drugs Ltd. , , , , AARTIIND , Aarti Industries Ltd. , , , , AARVEEDEN , Aarvee Denims & Exports Limited , , , , ABAN , Aban Offshore Ltd. , , , , Above is a sample data, In all I have 1500 rows of data I want the macro to fill up the columns C D E F Here is the macro Sub Macro1() Dim ctr As Long For ctr = 2 To 8 'Cells(Rows.Count, "a").End(xlUp).Row On Error Resume Next With Sheet1 With .QueryTables("btest") .Connection = _ "URL;http://www.buzzingstocks.com/in/btest.pl?" _ & "t=" & Cells(ctr, "a") & "&f=2" End With Cells(ctr, "c").Value = .Cells(26, "a").Value Cells(ctr, "d").Value = .Cells(26, "i").Value Cells(ctr, "e").Value = .Cells(28, "d").Value Cells(ctr, "f").Value = .Cells(28, "g").Value End With Next ctr ThisWorkbook.Save End Sub Somehow it does not fetches entries for few companies. Please help |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Need help with macro
I get error "Object variable or With block variable not set" on line :
For Each MyRow In DataTable.Rows ???? On May 6, 4:17*am, joel wrote: I couldn't get the query top work. *Instead of using an internet explorer to get the data. *It puts the results on sheet 1 using Column A and Column B from sheet 2. Sub WebQuery() With Sheets("Sheet1") |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Need help with macro
Try the macro with the original companies that you posted. i think the
problem is one of the compnies in your list doesn't exist or is returning bad data. The code worked perfectly on my PC at home. At work I'm having a problem but I think it is due to the latest updates are not installed on my PC at work. Let me know which company isn't working. Go to the website and verify that the company Symbol is a valid symbol. "Maxi" wrote: I get error "Object variable or With block variable not set" on line : For Each MyRow In DataTable.Rows ???? On May 6, 4:17 am, joel wrote: I couldn't get the query top work. Instead of using an internet explorer to get the data. It puts the results on sheet 1 using Column A and Column B from sheet 2. Sub WebQuery() With Sheets("Sheet1") |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Need help with macro
In message of Tue,
5 May 2009 16:17:01 in microsoft.public.excel.programming, joel writes I couldn't get the query top work. Instead of using an internet explorer to get the data. It puts the results on sheet 1 using Column A and Column B from sheet 2. Sub WebQuery() [snip] Set IE = CreateObject("InternetExplorer.Application") IE.Visible = True [snip] IE.Navigate2 URL Joel's demonstration code is REALLY interesting to me. I run Excel 2003 with IE7 on Windows XP SP2. A second machine has a similar system, but with Windows XP SP3. I think I have a configuration issue in IE. At "IE.Visible = True", an instance of IE is shown with Favorites open; on the 2nd machine Favorites is not opened. "IE.Navigate2 URL" results in a 2nd instance of IE being opened - probably a child of the 1st - and IE.readyState retains a value of READYSTATE_UNINITIALIZED. On the 2nd machine, the URL is opened in the 1st instance and IE.readyState soon goes to READYSTATE_COMPLETE which I infer is 4. What should I do to correct this problem? Where should I read about the methods (and properties) of the "InternetExplorer.Application" object? Thanks! -- Walter Briscoe |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Need help with macro
Walter: I experience the same problem yesterda with READYSTATE_UNINITIALIZED
(3). I wrote th e macro on my PC and home which has a version of excel 2002/2003 and it worked. I tried the code at work which has a newer verion of excel which is 2003 but doesn't have all the updates from microsoft and got the READYSTATE_UNINITIALIZED problem. I assumed the problem was due to some updates that was missing. This website is weird. The table numbers and data vary each time you run the code. I needed to get the table starting with the string "Trading Methodology:". Normally when I run code like this the table number is fixed. this time I found sometimes the "Trading Methodology:" table was table number 10 and sometimes it was 11. So I added code to the macro to actually search for the string in all the tables. I used INSTR to find the string and found it appear in the middle of one of the tables and was at the beginning of anothe table. I then switched from using INSTR() to LEFT() = "Trading Methodology:". I don't have permission at work to download upgrades from microsoft.con. If you can try getting all the upgrades and service packs and see if the problem disappears. I had to go to microsoft.com and search for READYSTATE_UNINITIALIZED to find out the value was 3. I still can't get the code to run on my pc at work. I have to comment out the test for IE.readyState and step through the code to get it too work. You could add a time and wait for 5 seconds instead of the test. "Walter Briscoe" wrote: In message of Tue, 5 May 2009 16:17:01 in microsoft.public.excel.programming, joel writes I couldn't get the query top work. Instead of using an internet explorer to get the data. It puts the results on sheet 1 using Column A and Column B from sheet 2. Sub WebQuery() [snip] Set IE = CreateObject("InternetExplorer.Application") IE.Visible = True [snip] IE.Navigate2 URL Joel's demonstration code is REALLY interesting to me. I run Excel 2003 with IE7 on Windows XP SP2. A second machine has a similar system, but with Windows XP SP3. I think I have a configuration issue in IE. At "IE.Visible = True", an instance of IE is shown with Favorites open; on the 2nd machine Favorites is not opened. "IE.Navigate2 URL" results in a 2nd instance of IE being opened - probably a child of the 1st - and IE.readyState retains a value of READYSTATE_UNINITIALIZED. On the 2nd machine, the URL is opened in the 1st instance and IE.readyState soon goes to READYSTATE_COMPLETE which I infer is 4. What should I do to correct this problem? Where should I read about the methods (and properties) of the "InternetExplorer.Application" object? Thanks! -- Walter Briscoe |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Need help with macro
In message of Thu,
7 May 2009 03:10:01 in microsoft.public.excel.programming, joel writes Joel, Walter: I experience the same problem yesterda with READYSTATE_UNINITIALIZED (3). I wrote th e macro on my PC and home which has a version of excel 2002/2003 and it worked. I tried the code at work which has a newer verion of excel which is 2003 but doesn't have all the updates from microsoft and got the READYSTATE_UNINITIALIZED problem. I assumed the problem was due to some updates that was missing. This website is weird. The table numbers and data vary each time you run the code. I needed to get the table starting with the string "Trading Methodology:". Normally when I run code like this the table number is fixed. this time I found sometimes the "Trading Methodology:" table was table number 10 and sometimes it was 11. So I added code to the macro to actually search for the string in all the tables. I used INSTR to find the string and found it appear in the middle of one of the tables and was at the beginning of anothe table. I then switched from using INSTR() to LEFT() = "Trading Methodology:". I don't have permission at work to download upgrades from microsoft.con. If you can try getting all the upgrades and service packs and see if the problem disappears. I had to go to microsoft.com and search for READYSTATE_UNINITIALIZED to find out the value was 3. I still can't get the code to run on my pc at work. I have to comment out the test for IE.readyState and step through the code to get it too work. You could add a time and wait for 5 seconds instead of the test. "Walter Briscoe" wrote: In message of Tue, 5 May 2009 16:17:01 in microsoft.public.excel.programming, joel writes I couldn't get the query top work. Instead of using an internet explorer to get the data. It puts the results on sheet 1 using Column A and Column B from sheet 2. Sub WebQuery() [snip] Set IE = CreateObject("InternetExplorer.Application") IE.Visible = True [snip] IE.Navigate2 URL Joel's demonstration code is REALLY interesting to me. I run Excel 2003 with IE7 on Windows XP SP2. A second machine has a similar system, but with Windows XP SP3. I think I have a configuration issue in IE. At "IE.Visible = True", an instance of IE is shown with Favorites open; on the 2nd machine Favorites is not opened. "IE.Navigate2 URL" results in a 2nd instance of IE being opened - probably a child of the 1st - and IE.readyState retains a value of READYSTATE_UNINITIALIZED. On the 2nd machine, the URL is opened in the 1st instance and IE.readyState soon goes to READYSTATE_COMPLETE which I infer is 4. What should I do to correct this problem? Where should I read about the methods (and properties) of the "InternetExplorer.Application" object? Thanks! -- Walter Briscoe -- Walter Briscoe |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Need help with macro
In message of Thu,
7 May 2009 03:10:01 in microsoft.public.excel.programming, joel writes Joel, Thanks for a quick response. The problem I have on one system is with IE rather than a particular URL. CreateObject + IE.Visible = TRUE opens one window. IE.Navigate2 URL opens a second window. i.e. the second window has no obvious connection with the first. I take it the problem is with my Internet Explorer configuration rather than anything else. I compared tools\internet options - particularly focussing on the advanced tab - eliminated all differences and nothing changed. My problem is not with the site. It is that the URL is opened in a child of the object rather than in the object. I am inclined to install IE8 and see if the problem goes away. Walter: I experience the same problem yesterda with READYSTATE_UNINITIALIZED (3). I wrote th e macro on my PC and home which has a version of excel 2002/2003 and it worked. I tried the code at work which has a newer verion of excel which is 2003 but doesn't have all the updates from microsoft and got the READYSTATE_UNINITIALIZED problem. I assumed the problem was due to some updates that was missing. This website is weird. The table numbers and data vary each time you run the code. I needed to get the table starting with the string "Trading Methodology:". Normally when I run code like this the table number is fixed. this time I found sometimes the "Trading Methodology:" table was table number 10 and sometimes it was 11. So I added code to the macro to actually search for the string in all the tables. I used INSTR to find the string and found it appear in the middle of one of the tables and was at the beginning of anothe table. I then switched from using INSTR() to LEFT() = "Trading Methodology:". I don't have permission at work to download upgrades from microsoft.con. If you can try getting all the upgrades and service packs and see if the problem disappears. I had to go to microsoft.com and search for READYSTATE_UNINITIALIZED to find out the value was 3. I still can't get the code to run on my pc at work. I have to comment out the test for IE.readyState and step through the code to get it too work. You could add a time and wait for 5 seconds instead of the test. "Walter Briscoe" wrote: In message of Tue, 5 May 2009 16:17:01 in microsoft.public.excel.programming, joel writes I couldn't get the query top work. Instead of using an internet explorer to get the data. It puts the results on sheet 1 using Column A and Column B from sheet 2. Sub WebQuery() [snip] Set IE = CreateObject("InternetExplorer.Application") IE.Visible = True [snip] IE.Navigate2 URL Joel's demonstration code is REALLY interesting to me. I run Excel 2003 with IE7 on Windows XP SP2. A second machine has a similar system, but with Windows XP SP3. I think I have a configuration issue in IE. At "IE.Visible = True", an instance of IE is shown with Favorites open; on the 2nd machine Favorites is not opened. "IE.Navigate2 URL" results in a 2nd instance of IE being opened - probably a child of the 1st - and IE.readyState retains a value of READYSTATE_UNINITIALIZED. On the 2nd machine, the URL is opened in the 1st instance and IE.readyState soon goes to READYSTATE_COMPLETE which I infer is 4. What should I do to correct this problem? Where should I read about the methods (and properties) of the "InternetExplorer.Application" object? Thanks! -- Walter Briscoe -- Walter Briscoe |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Need help with macro
I would go to the Office Developer - Web component group and ask the
question. let me know what you find. My suspecions is it has to do wiith an Active X component. When I get the READYSTATE_UNINITIALIZED there is still one object that need to be downloaded on the webpage which is an ad ware object. I think somehow you are loosing the connection between and excel and the IE that is causing a 2nd window to open or an active X is interfering with the process. "Walter Briscoe" wrote: In message of Thu, 7 May 2009 03:10:01 in microsoft.public.excel.programming, joel writes Joel, Thanks for a quick response. The problem I have on one system is with IE rather than a particular URL. CreateObject + IE.Visible = TRUE opens one window. IE.Navigate2 URL opens a second window. i.e. the second window has no obvious connection with the first. I take it the problem is with my Internet Explorer configuration rather than anything else. I compared tools\internet options - particularly focussing on the advanced tab - eliminated all differences and nothing changed. My problem is not with the site. It is that the URL is opened in a child of the object rather than in the object. I am inclined to install IE8 and see if the problem goes away. Walter: I experience the same problem yesterda with READYSTATE_UNINITIALIZED (3). I wrote th e macro on my PC and home which has a version of excel 2002/2003 and it worked. I tried the code at work which has a newer verion of excel which is 2003 but doesn't have all the updates from microsoft and got the READYSTATE_UNINITIALIZED problem. I assumed the problem was due to some updates that was missing. This website is weird. The table numbers and data vary each time you run the code. I needed to get the table starting with the string "Trading Methodology:". Normally when I run code like this the table number is fixed. this time I found sometimes the "Trading Methodology:" table was table number 10 and sometimes it was 11. So I added code to the macro to actually search for the string in all the tables. I used INSTR to find the string and found it appear in the middle of one of the tables and was at the beginning of anothe table. I then switched from using INSTR() to LEFT() = "Trading Methodology:". I don't have permission at work to download upgrades from microsoft.con. If you can try getting all the upgrades and service packs and see if the problem disappears. I had to go to microsoft.com and search for READYSTATE_UNINITIALIZED to find out the value was 3. I still can't get the code to run on my pc at work. I have to comment out the test for IE.readyState and step through the code to get it too work. You could add a time and wait for 5 seconds instead of the test. "Walter Briscoe" wrote: In message of Tue, 5 May 2009 16:17:01 in microsoft.public.excel.programming, joel writes I couldn't get the query top work. Instead of using an internet explorer to get the data. It puts the results on sheet 1 using Column A and Column B from sheet 2. Sub WebQuery() [snip] Set IE = CreateObject("InternetExplorer.Application") IE.Visible = True [snip] IE.Navigate2 URL Joel's demonstration code is REALLY interesting to me. I run Excel 2003 with IE7 on Windows XP SP2. A second machine has a similar system, but with Windows XP SP3. I think I have a configuration issue in IE. At "IE.Visible = True", an instance of IE is shown with Favorites open; on the 2nd machine Favorites is not opened. "IE.Navigate2 URL" results in a 2nd instance of IE being opened - probably a child of the 1st - and IE.readyState retains a value of READYSTATE_UNINITIALIZED. On the 2nd machine, the URL is opened in the 1st instance and IE.readyState soon goes to READYSTATE_COMPLETE which I infer is 4. What should I do to correct this problem? Where should I read about the methods (and properties) of the "InternetExplorer.Application" object? Thanks! -- Walter Briscoe -- Walter Briscoe |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
Need help with macro
In message of Thu,
7 May 2009 07:18:17 in microsoft.public.excel.programming, joel writes I have added microsoft.public.office.developer.web.components to my list of subscribed newsgroups and will kibitz there for a while before daring to ask my question. Thank you for your good will. I would go to the Office Developer - Web component group and ask the question. let me know what you find. My suspecions is it has to do wiith an Active X component. When I get the READYSTATE_UNINITIALIZED there is still one object that need to be downloaded on the webpage which is an ad ware object. I think somehow you are loosing the connection between and excel and the IE that is causing a 2nd window to open or an active X is interfering with the process. "Walter Briscoe" wrote: In message of Thu, 7 May 2009 03:10:01 in microsoft.public.excel.programming, joel writes Joel, Thanks for a quick response. The problem I have on one system is with IE rather than a particular URL. CreateObject + IE.Visible = TRUE opens one window. IE.Navigate2 URL opens a second window. i.e. the second window has no obvious connection with the first. I take it the problem is with my Internet Explorer configuration rather than anything else. I compared tools\internet options - particularly focussing on the advanced tab - eliminated all differences and nothing changed. My problem is not with the site. It is that the URL is opened in a child of the object rather than in the object. I am inclined to install IE8 and see if the problem goes away. Walter: I experience the same problem yesterda with READYSTATE_UNINITIALIZED (3). I wrote th e macro on my PC and home which has a version of excel 2002/2003 and it worked. I tried the code at work which has a newer verion of excel which is 2003 but doesn't have all the updates from microsoft and got the READYSTATE_UNINITIALIZED problem. I assumed the problem was due to some updates that was missing. This website is weird. The table numbers and data vary each time you run the code. I needed to get the table starting with the string "Trading Methodology:". Normally when I run code like this the table number is fixed. this time I found sometimes the "Trading Methodology:" table was table number 10 and sometimes it was 11. So I added code to the macro to actually search for the string in all the tables. I used INSTR to find the string and found it appear in the middle of one of the tables and was at the beginning of anothe table. I then switched from using INSTR() to LEFT() = "Trading Methodology:". I don't have permission at work to download upgrades from microsoft.con. If you can try getting all the upgrades and service packs and see if the problem disappears. I had to go to microsoft.com and search for READYSTATE_UNINITIALIZED to find out the value was 3. I still can't get the code to run on my pc at work. I have to comment out the test for IE.readyState and step through the code to get it too work. You could add a time and wait for 5 seconds instead of the test. "Walter Briscoe" wrote: In message of Tue, 5 May 2009 16:17:01 in microsoft.public.excel.programming, joel writes I couldn't get the query top work. Instead of using an internet explorer to get the data. It puts the results on sheet 1 using Column A and Column B from sheet 2. Sub WebQuery() [snip] Set IE = CreateObject("InternetExplorer.Application") IE.Visible = True [snip] IE.Navigate2 URL Joel's demonstration code is REALLY interesting to me. I run Excel 2003 with IE7 on Windows XP SP2. A second machine has a similar system, but with Windows XP SP3. I think I have a configuration issue in IE. At "IE.Visible = True", an instance of IE is shown with Favorites open; on the 2nd machine Favorites is not opened. "IE.Navigate2 URL" results in a 2nd instance of IE being opened - probably a child of the 1st - and IE.readyState retains a value of READYSTATE_UNINITIALIZED. On the 2nd machine, the URL is opened in the 1st instance and IE.readyState soon goes to READYSTATE_COMPLETE which I infer is 4. What should I do to correct this problem? Where should I read about the methods (and properties) of the "InternetExplorer.Application" object? Thanks! -- Walter Briscoe -- Walter Briscoe -- Walter Briscoe |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
Need help with macro
Dear Joel,
I was unwell... could not reply. I still get the same error. I checked my companies, they are correct and returns output on the website. A strange thing is that when I get the error "Object variable or With block variable not set" on line : For Each MyRow In DataTable.Rows, I click "Debug" - go to the vba code, shift the yellow arrow little above and keep it on the line Set DataTable = Tables(tableNum) then I press F8 till it crosses the line For Each MyRow In DataTable.Rows then I press F5 and everything works This happens lot of times. If you want, I can send you the list of all 1272 companies in an attachment. Thanks On May 6, 3:58*pm, joel wrote: Try the macro with the original companies that you posted. *i think the problem is one of the compnies in your list doesn't exist or is returning bad data. *The code worked perfectly on my PC at home. *At work I'm having a problem but I think it is due to the latest updates are not installed on my PC at work. *Let me know which company isn't working. Go to the website and verify that the company Symbol is a valid symbol. "Maxi" wrote: I get error "Object variable or With block variable not set" on line : For Each MyRow In DataTable.Rows ???? On May 6, 4:17 am, joel wrote: I couldn't get the query top work. *Instead of using an internet explorer to get the data. *It puts the results on sheet 1 using Column A and Column B from sheet 2. Sub WebQuery() With Sheets("Sheet1")- Hide quoted text - - Show quoted text - |
#12
Posted to microsoft.public.excel.programming
|
|||
|
|||
Need help with macro
I tried with the original companies 20MICRONS 3IINFOTECH 3MINDIA
AARTIDRUGS AARTIIND AARVEEDEN ABAN Still the same error. Please help. On May 6, 3:58*pm, joel wrote: Try the macro with the original companies that you posted. *i think the problem is one of the compnies in your list doesn't exist or is returning bad data. *The code worked perfectly on my PC at home. *At work I'm having a problem but I think it is due to the latest updates are not installed on my PC at work. *Let me know which company isn't working. Go to the website and verify that the company Symbol is a valid symbol. "Maxi" wrote: I get error "Object variable or With block variable not set" on line : For Each MyRow In DataTable.Rows ???? On May 6, 4:17 am, joel wrote: I couldn't get the query top work. *Instead of using an internet explorer to get the data. *It puts the results on sheet 1 using Column A and Column B from sheet 2. Sub WebQuery() With Sheets("Sheet1") |
#13
Posted to microsoft.public.excel.programming
|
|||
|
|||
Need help with macro
error "Object variable or With block variable not set"
means that you probably either haven't DIM'd a variable correctly or haven't created the object the statement For Each MyRow In DataTable.Rows requires that the variable named MyRow is a range, so you must have either DIM MyRow as Range ' PREFERRED or DIM MyRow '(better than not having it), it defaults to Variant and at the top of the code module have OPTION EXPLICIT this forces you to DIM all vatiables, and is great for spotting typos. Also, you get to use intellisence where you dim objects specifically Aside from MyRow, the error could be that DataTable doesn't exist. I assume that it too is a range object? so you must have DIM DataTable as Range and SET DataTable = range(???) in both, DIM allocates memory while SET creates the object. "Maxi" wrote in message ... Dear Joel, I was unwell... could not reply. I still get the same error. I checked my companies, they are correct and returns output on the website. A strange thing is that when I get the error "Object variable or With block variable not set" on line : For Each MyRow In DataTable.Rows, I click "Debug" - go to the vba code, shift the yellow arrow little above and keep it on the line Set DataTable = Tables(tableNum) then I press F8 till it crosses the line For Each MyRow In DataTable.Rows then I press F5 and everything works This happens lot of times. If you want, I can send you the list of all 1272 companies in an attachment. Thanks On May 6, 3:58 pm, joel wrote: Try the macro with the original companies that you posted. i think the problem is one of the compnies in your list doesn't exist or is returning bad data. The code worked perfectly on my PC at home. At work I'm having a problem but I think it is due to the latest updates are not installed on my PC at work. Let me know which company isn't working. Go to the website and verify that the company Symbol is a valid symbol. "Maxi" wrote: I get error "Object variable or With block variable not set" on line : For Each MyRow In DataTable.Rows ???? On May 6, 4:17 am, joel wrote: I couldn't get the query top work. Instead of using an internet explorer to get the data. It puts the results on sheet 1 using Column A and Column B from sheet 2. Sub WebQuery() With Sheets("Sheet1")- Hide quoted text - - Show quoted text - |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
AutoRun Macro with a delay to give user the choice to cancel the macro | Excel Programming | |||
Macro Help Needed - Excel 2007 - Print Macro with Auto Sort | Excel Worksheet Functions | |||
Macro not showing in Tools/Macro/Macros yet show up when I goto VBA editor | Excel Programming | |||
macro to delete entire rows when column A is blank ...a quick macro | Excel Programming | |||
Start Macro / Stop Macro / Restart Macro | Excel Programming |