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.
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!
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
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.
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
Awesome
Just great. Thank you very much
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🙏
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 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!
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
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