I explain one way to convert json to range in this video: ruclips.net/video/dSZ1DJuutnI/видео.htmlsi=ZC9VIVKepde5r4z7 It's a simple way, not perfect though. There is a vba-json full converter available, just google it. You can adapt/modified the above to convert to html as explained in this other video: ruclips.net/video/Ifc2I6wmWIk/видео.htmlsi=IQkMdVdqAewg4HxN
Great question. You need to get the length of the HTML table after the HTTP request response is ready as follows: tableLength = HTML.getElementsByTagName("tr").Length Then use two row variables instead of 1, something like r1 and r2, the first to loop through HTML table, the second to determine the row in the worksheet. Add a condition to only get data if r1 = 1 (first row with headers probably) and r1 > tableLength - 10. See code below: For Each elem In HTML.getElementsByTagName("tr") If elem.getElementsByTagName("td").Length > 0 Then r1 = r1 + 1 If r1 = 1 Or r1 > tableLength - 10 Then r2 = r2 + 1 For Each ele In elem.getElementsByTagName("td") c = c + 1 Cells(r2, c).Value = ele.innerText Next ele End If c = 0 End If Next elem
I assume you refer to update the values in a row or cell in Google Sheets. One way to do that is using the Google Sheets API. Another possibility is using Google Visualization API (needs JSON data). I rather create my own macro in Google Sheets to do that, but that requires some Google Apps Script knowledge. I may upload some about it in the near future. Learn more about macros in Google Sheets here: ruclips.net/video/qnAboKJafg8/видео.html
@@ExcelMacroMania I want to update a given cell in google sheet while staying on the excel file. I heard about the http put method as a replacement for the post method
@@yawj.amlanman7835 Yes, you need to use POST or PUT instead of GET. But you need to implement the Google Sheets API, get an API key, implement OAuth, etc, to do that. An alternative, and what I personally do, is creating my own Google script/macro/API with authorization, and then send a HTTP POST request from Excel VBA. But that requires some knowledge of Google Apps Script. I may upload something about that in the near future. Hope that helps!
That was covered earlier - import all data: ruclips.net/video/wYXf6vyomCk/видео.htmlsi=RuFxgRFBWZR4E93j Check also - 5 methods to import data: ruclips.net/video/lqFoVeNpA-4/видео.htmlsi=ov_2Qs3uFF4WcGJ8 And get restricted data: ruclips.net/video/VmlSN-C2GGo/видео.htmlsi=02wbib_nz2QF7sp4
Hello Sir, thank you for the very helpful video, I have succeeded in importing specific cells, but I have problems importing the data, I want the format of the imported data to be nominal and percentages in Excel, I can adjust it, can you give me advice?
You can use NumberFormat. For example, for column A (c=1) would be: If c = 1 Then With Cells(r, c) .Value = td.innerText .NumberFormat = "General" ... or any other format - see below some examples for a number with 2 decimals or a % with 2 decimals .NumberFormat = "0.00" .NumberFormat = "0.00%" End With End If
@@ExcelMacroMania I have try this but not work, i have coloum data A, C,D,E = Text B = Date F - I = Number J - N = Percetage I want import data to excell as this format Help me please for script, thanks before
The Great,, How I want Import connect to Dropdown multi cheklist userform criteria to spesific data import,. its possible ? and i want The table format in Excel does not change, Tq so much for help me.
Not sure I understand. I didn't work with multi-select dropdown in google sheets, I think is not possible by default and needs a google script to allow multi-select. So, if you have multiple options selected in a cell and they are separated by a comma or semi-colon, that should be imported a text as any other cell.
You need to use the gid of the sheet you want to import the data from. The gid is shown in the sharing or editing url usually at the end, and is different for each sheet in the Google spreadsheet. You add it to the modified url presented in this video as follows: spreadsheets.google.com/tq?tqx=out:html&key=" & key & "&gid=" & gid Another possibility is using the Google Sheets API, which allows to add the sheet name and range of cells to import directly to the url. However, here you need to setup a project, get an API key, and the response is JSON, so is a bit more complicated. I will probably show how to do that in other video.
How to pull data from multiple sheets using 1 macros only? When I try it, I'm only able to pull the data from the first sheet and then then rest, nothing.
You need to put all that in a loop, for example For i = 1 To numberofsheets Inside the loop you need to add a new worksheet in Excel: Sheets.Add Then you need to update the URL for each sheet in Google spreadsheet - here you need to know the gid of the sharing/editing link of your Google spreadsheet. Then you concatenate that in the URL as below: URL = "spreadsheeets.google.com/tq?tqx=out:html&key=" & sheetid & "&gid=" & gid
Hi , i have a problem because this macro importing some values from the table (google sheets) into one cell in excel spreadsheet . Can you help me ? thx
I believe you missed or miss-typed the variables that defines the row and column where the data is added (the variables in the video are r and c). That's probably why they all come in the same cell (same row and column).
@@ExcelMacroMania Hi , Thank you for your answer , The problem is rather in the code. I want to copy only one column from A1:A94 from google spreedsheet to column in excel B31:B125 . When the first value in google spredsheet is a number, all values are copied, but for example when the first value is KK123, the second, for example KK124, and the third is a number, say 12345 ... the fourth KK125, then the values from 12345 are copied to the excel table, and KK123 and KK124 into one cell B30 in excel. I have no idea why this is happening. I found a workaround, add cell A1 in google spreadsheet = 1, then all values from A1 are copied correctly. Please help .
@@EwangelicznyKatolik Not sure why that happens, but I believe it could be due to number format issues in Excel. To avoid that, you could probably set the cell number format before adding the value. For example: Cells(r,c).NumberFormat = "@" -> everything as text Cells(r,c).NumberFormat = "0" or "0.00" etc -> for numbers without or with decimals
Brilliant set of videos. Thanks so much for sharing with us!
Just great. Thank you very much
Awesome
am interested in converting the json format to html to the sheets so help..
I explain one way to convert json to range in this video: ruclips.net/video/dSZ1DJuutnI/видео.htmlsi=ZC9VIVKepde5r4z7
It's a simple way, not perfect though. There is a vba-json full converter available, just google it.
You can adapt/modified the above to convert to html as explained in this other video: ruclips.net/video/Ifc2I6wmWIk/видео.htmlsi=IQkMdVdqAewg4HxN
Hi. I need to import the last 5 rows in a Table... Can this be done through this method? Thanks a lot 😊
Great question. You need to get the length of the HTML table after the HTTP request response is ready as follows:
tableLength = HTML.getElementsByTagName("tr").Length
Then use two row variables instead of 1, something like r1 and r2, the first to loop through HTML table, the second to determine the row in the worksheet. Add a condition to only get data if r1 = 1 (first row with headers probably) and r1 > tableLength - 10. See code below:
For Each elem In HTML.getElementsByTagName("tr")
If elem.getElementsByTagName("td").Length > 0 Then
r1 = r1 + 1
If r1 = 1 Or r1 > tableLength - 10 Then
r2 = r2 + 1
For Each ele In elem.getElementsByTagName("td")
c = c + 1
Cells(r2, c).Value = ele.innerText
Next ele
End If
c = 0
End If
Next elem
Thanks. How to edit a single line with VBA code
I assume you refer to update the values in a row or cell in Google Sheets. One way to do that is using the Google Sheets API. Another possibility is using Google Visualization API (needs JSON data). I rather create my own macro in Google Sheets to do that, but that requires some Google Apps Script knowledge. I may upload some about it in the near future. Learn more about macros in Google Sheets here: ruclips.net/video/qnAboKJafg8/видео.html
@@ExcelMacroMania
I want to update a given cell in google sheet while staying on the excel file. I heard about the http put method as a replacement for the post method
@@yawj.amlanman7835 Yes, you need to use POST or PUT instead of GET. But you need to implement the Google Sheets API, get an API key, implement OAuth, etc, to do that. An alternative, and what I personally do, is creating my own Google script/macro/API with authorization, and then send a HTTP POST request from Excel VBA. But that requires some knowledge of Google Apps Script. I may upload something about that in the near future. Hope that helps!
@@ExcelMacroMania Thank you for your feedback but I really don't have the knowledge required for what you are saying so maybe another video will do.
How to import all the data from that one sheet?
That was covered earlier - import all data: ruclips.net/video/wYXf6vyomCk/видео.htmlsi=RuFxgRFBWZR4E93j
Check also - 5 methods to import data: ruclips.net/video/lqFoVeNpA-4/видео.htmlsi=ov_2Qs3uFF4WcGJ8
And get restricted data: ruclips.net/video/VmlSN-C2GGo/видео.htmlsi=02wbib_nz2QF7sp4
@@ExcelMacroMania Thank you, sir. Have a nice day.
thank you.
Hello Sir, thank you for the very helpful video, I have succeeded in importing specific cells, but I have problems importing the data, I want the format of the imported data to be nominal and percentages in Excel, I can adjust it, can you give me advice?
You can use NumberFormat. For example, for column A (c=1) would be:
If c = 1 Then
With Cells(r, c)
.Value = td.innerText
.NumberFormat = "General"
... or any other format - see below some examples for a number with 2 decimals or a % with 2 decimals
.NumberFormat = "0.00"
.NumberFormat = "0.00%"
End With
End If
@@ExcelMacroMania I have try this but not work, i have coloum data
A, C,D,E = Text
B = Date
F - I = Number
J - N = Percetage
I want import data to excell as this format
Help me please for script, thanks before
@@ruangbelajarku2023
Columns("A").NumberFormat = "@"
Columns("B").NumberFormat = "mm/dd/yyyy" 'or dd/mm/yyyy non-US or other
Columns("C:E").NumberFormat = "@"
Columns("G:I").NumberFormat = "0.00"
Columns("J:N").NumberFormat = "0.00%"
The Great,, How I want Import connect to Dropdown multi cheklist userform criteria to spesific data import,. its possible ? and i want The table format in Excel does not change, Tq so much for help me.
Not sure I understand. I didn't work with multi-select dropdown in google sheets, I think is not possible by default and needs a google script to allow multi-select. So, if you have multiple options selected in a cell and they are separated by a comma or semi-colon, that should be imported a text as any other cell.
@@ExcelMacroMania thank you🙏
how to import if spread sheet has more than one sheet?
You need to use the gid of the sheet you want to import the data from. The gid is shown in the sharing or editing url usually at the end, and is different for each sheet in the Google spreadsheet. You add it to the modified url presented in this video as follows:
spreadsheets.google.com/tq?tqx=out:html&key=" & key & "&gid=" & gid
Another possibility is using the Google Sheets API, which allows to add the sheet name and range of cells to import directly to the url. However, here you need to setup a project, get an API key, and the response is JSON, so is a bit more complicated. I will probably show how to do that in other video.
Thanks! It worked!
@@ExcelMacroMania hope soon as
How to pull data from multiple sheets using 1 macros only? When I try it, I'm only able to pull the data from the first sheet and then then rest, nothing.
You need to put all that in a loop, for example For i = 1 To numberofsheets
Inside the loop you need to add a new worksheet in Excel: Sheets.Add
Then you need to update the URL for each sheet in Google spreadsheet - here you need to know the gid of the sharing/editing link of your Google spreadsheet. Then you concatenate that in the URL as below:
URL = "spreadsheeets.google.com/tq?tqx=out:html&key=" & sheetid & "&gid=" & gid
@@ExcelMacroMania I will try this out. Hope it works. Thanks, mate!
Hi , i have a problem because this macro importing some values from the table (google sheets) into one cell in excel spreadsheet . Can you help me ? thx
I believe you missed or miss-typed the variables that defines the row and column where the data is added (the variables in the video are r and c). That's probably why they all come in the same cell (same row and column).
@@ExcelMacroMania Hi , Thank you for your answer ,
The problem is rather in the code. I want to copy only one column from A1:A94 from google spreedsheet to column in excel B31:B125 .
When the first value in google spredsheet is a number, all values are copied, but for example when the first value is KK123, the second, for example KK124, and the third is a number, say 12345 ... the fourth KK125, then the values from 12345 are copied to the excel table, and KK123 and KK124 into one cell B30 in excel.
I have no idea why this is happening.
I found a workaround, add cell A1 in google spreadsheet = 1, then all values from A1 are copied correctly.
Please help .
@@EwangelicznyKatolik Not sure why that happens, but I believe it could be due to number format issues in Excel. To avoid that, you could probably set the cell number format before adding the value. For example:
Cells(r,c).NumberFormat = "@" -> everything as text
Cells(r,c).NumberFormat = "0" or "0.00" etc -> for numbers without or with decimals