Export Specific Cells/Data from Google Sheet to Excel Desktop using VBA

[Manster]

Hi everyone!

First, apologize because I have no basic about the VBA. But I need a code/script to solve my problem. My asking is quite long, but I try to make it as short as possible.

I want to copy the specific cells/data from Google Sheet to the Excel Desktop using VBA. Yes, Excel has a "Get Data" tool, but it could not paste the specific cell or data.

So, I found a code/script on the internet. However, as I declared earlier, I have no basic about VBA, and I am only able to change the link address and run the script/code. That's it.

I have several considerations about this task, but I plan to solve them one by one. Maybe other considerations can proceed if this post can be solved.

But for the time being, I need your help to:
1) Data from "Testing Spreadsheet" Sheet1 B3:C5 exported to the "Source.xlsm" Sheet1 B3:C5
2) Data from "Testing Spreadsheet" Sheet2 E3:F6 exported to the "Source.xlsm" Sheet2 E3:F6
3) Then, if we refresh or update the "Source.xlsm", the data can be replaced on the same cells.

I have uploaded an excel file, namely the "Source.xlsm", here. The Google Sheet link and VBA code in that file.

I appreciate any help you can provide. Thank you.

This is the link. Please click here.

 

[Marc L]

Wild cross posting ‼​

According to any Excel forum rules you must give link for each other forum where you created the same thread …​

​

 

[Manster]

sorry about that sir, I didn't realize that rule and this is my mistake. I will edit this thread and put the link. Really sorry.

 

[Manster]

I would appreciate it if any excel ninjas here could assist or help me. I also google about this, and some advice using google API, and I do not know much about this. However, if any excel ninjas have another way, I appreciate it.

 

[Marc L]

Rather than grabbing web the easy way is to save data to a file format compatible with Excel …​

​

 

[p45cal]

It seems you can also specify a range in a google sheet to get data from; you can add a &range= part to the url. It seems you can also use the google range notation which leaves off the final row number to grab as many rows as there are to grab (ie. instead of B2:C5 you can miss off the 5 leaving B2:C and it will give you as many rows as necessary).
See attached, where a button at cell I1 of Sheet2 should update both sheets.
Note that I've added comments and changed some values in the macro but I've kept the original values as comments.
The final .Delete line is optional but I used it to stop having hundreds of queries lurking on the sheet.

 

[p45cal]

More cross posts:

Export Specific Cells/Data from Google Sheet to Excel Desktop

Manster Asks: Export Specific Cells/Data from Google Sheet to Excel Desktop First, apologize because I have no basic about the VBA. But I need a code/script to solve my problem. My asking is quite long, but I try to make it as short as possible. I want to copy the specific cells/data from...

solveforum.com

https://stackoverflow.com/questions/73341452/export-specific-cells-data-from-google-sheet-to-excel-desktop

Export Specific Cells/Data from Google Sheet to Excel Desktop using VBA

Hi everyone! First, apologize because I have no basic about the VBA. But I need a code/script to solve my problem. My asking is quite long, but I try to make it as short as possible. I want to copy the specific cells/data from Google Sheet to the Excel Desktop using VBA. Yes, Excel has a...

www.excelforum.com

 

[vletm]

You Manster wrote
sorry about that sir, I didn't realize that rule and this is my mistake. I will edit this thread and put the link. Really sorry.

How that could be possible?
Of course, You reread this Forum's rules today
... and still after that there has noted about three Cross-Posting.

Site Rules - New Users - Please Read

Hi all, Welcome to the Chandoo.org Forums. Posting Rules & Etiquette The Chandoo.org Forums is a collaborative and happy place to learn and expand your Excel knowledge. The Chandoo.org Forums consist of several Sub-Forums based on the type of question/area of Excel you are interested in...

chandoo.org

• Cross-Posting. Generally, it is considered poor practice to cross post. That is to post the same question on several forums in the hope of getting a response quicker.
• If you do cross-post, please put that in your post.
• Also if you have cross-posted and get an Solution elsewhere, have the courtesy of posting the Solution here so other readers can learn from the answer also, as well as stopping people wasting their time on your answered question.
  
  Ps. Those rules are for You too.

 

[Manster]

You Manster wrote
sorry about that sir, I didn't realize that rule and this is my mistake. I will edit this thread and put the link. Really sorry.

How that could be possible?
Of course, You reread this Forum's rules today
... and still after that there has noted about three Cross-Posting.

Site Rules - New Users - Please Read

Hi all, Welcome to the Chandoo.org Forums. Posting Rules & Etiquette The Chandoo.org Forums is a collaborative and happy place to learn and expand your Excel knowledge. The Chandoo.org Forums consist of several Sub-Forums based on the type of question/area of Excel you are interested in...

chandoo.org

• Cross-Posting. Generally, it is considered poor practice to cross post. That is to post the same question on several forums in the hope of getting a response quicker.
• If you do cross-post, please put that in your post.
• Also if you have cross-posted and get an Solution elsewhere, have the courtesy of posting the Solution here so other readers can learn from the answer also, as well as stopping people wasting their time on your answered question.
  
  Ps. Those rules are for You too.

Ok, I already deleted that posting in forum A, and I have sent a private message to the forum B moderator on how to delete that post as well. I am waiting for their reply. I should be alert about any forum's rules. It is hoped that you will accept my apologies and regrets. Sorry and thank you for giving me this opportunity to stay here to learn in this forum.

 

[Manster]

It seems you can also specify a range in a google sheet to get data from; you can add a &range= part to the url. It seems you can also use the google range notation which leaves off the final row number to grab as many rows as there are to grab (ie. instead of B2:C5 you can miss off the 5 leaving B2:C and it will give you as many rows as necessary).
See attached, where a button at cell I1 of Sheet2 should update both sheets.
Note that I've added comments and changed some values in the macro but I've kept the original values as comments.
The final .Delete line is optional but I used it to stop having hundreds of queries lurking on the sheet.

Awesome!!! Thank you, p45cal! This works for me. So, I can go another step to configure this excel and google sheet. If anything problem or query related to this, I will raise it here. Thank you again!

 

[Manster]

It seems you can also specify a range in a google sheet to get data from; you can add a &range= part to the url. It seems you can also use the google range notation which leaves off the final row number to grab as many rows as there are to grab (ie. instead of B2:C5 you can miss off the 5 leaving B2:C and it will give you as many rows as necessary).
See attached, where a button at cell I1 of Sheet2 should update both sheets.
Note that I've added comments and changed some values in the macro but I've kept the original values as comments.
The final .Delete line is optional but I used it to stop having hundreds of queries lurking on the sheet.

Hi p45c4l,
There are two conditions here which I can simplify my explanation below:

1) When I try to delete/erase the values in the cells, for example, Sheet 1 B3:C5, then I update in the Excel file, the cells in the excel file could not follow with the Google Sheet which is B3:C5 is empty now.

2) In addition, the header, "Sample" and "Value", will move to the third row. It is supposed to 'stay' in the second row, and I don't know why it happens.

Besides that, if we want to copy and paste (similar method before this) but with 2 or 3 different ranges in the same sheet (before this one range for a sheet), for example;
Data from "Testing Spreadsheet" Sheet1 B3:C5 F3:G5 K3:L5 exported to the "Source.xlsm" Sheet1 B3:C5 F3:G5 K3:L5

I appreciate any help you or anyone can provide. Thank you.

 

[p45cal]

When I try to delete/erase the values in the cells, for example

I too got weird results with the headers moving down a row. Worse when you have text as the first row the text moves into the headers!

and I don't know why it happens.

Nor do I.

but with 2 or 3 different ranges in the same sheet

You'll need 2 or 3 different querytables. You can have the likes of:
rngStr = "B3:C5, F3:G5, K3:L5"
in the code I gave you but this will just put the 3 ranges side by side in the destination workbook - not a lot of use.

A different approach is via Power Query and named rangesl. I can't add named ranges to your google sheet but if you were to add them for me for each of the 4 tables there. Make the number of rows in each named range longer than it will ever need to be and I'll write PQ query tables to bring in the data. It's a shame google sheets doesn't have dynamically resizing Tables like Excel does.

 

[Manster]

I too got weird results with the headers moving down a row. Worse when you have text as the first row the text moves into the headers!

Nor do I.

You'll need 2 or 3 different querytables. You can have the likes of:
rngStr = "B3:C5, F3:G5, K3:L5"
in the code I gave you but this will just put the 3 ranges side by side in the destination workbook - not a lot of use.

A different approach is via Power Query and named rangesl. I can't add named ranges to your google sheet but if you were to add them for me for each of the 4 tables there. Make the number of rows in each named range longer than it will ever need to be and I'll write PQ query tables to bring in the data. It's a shame google sheets doesn't have dynamically resizing Tables like Excel does.

Thank you, Sir, for the reply. Do you mean writing like this? If yes, I ran this code, and it became an error, Sir. My apologies due to weak about this.

.
.
.
For i = 1 To 2
  keyString = "1tNCvneyYlvlLJpVJLiina5T34edjXZYA5cq24K6v3a8"
  Select Case i
    Case 1
      gidString = "0"
      rngStr = "B3:C5, F3:G5, K3:L5"
      Set Destn = Sheets("Sheet1").Range("B2")
    Case 2
      gidString = "458138939"
      rngStr = "E2:F"
      Set Destn = Sheets("Sheet2").Range("E2")
  End Select
  .
  .
  .

 

[p45cal]

my mistake, the line:
rngStr = "B3:C5, F3:G5, K3:L5"
should be:
rngStr = "B3:C5,F3:G5,K3:L5"
(with no spaces).
As I mentionesd, this puts the 3 ranges next to each other as a single table, but with your sheet as it is, it also demponstrates how this fouls up:


with data in with the headers (field names). I don't know how to put it right.

So, on to plan B

A different approach is via Power Query and named rangesl. I can't add named ranges to your google sheet but if you were to add them for me for each of the 4 tables there (make the number of rows in each named range longer than it will ever need to be) and I'll write PQ query tables to bring in the data.

(The above is not quite my words but changed a bit to make clearer.)

 

[Manster]

my mistake, the line:
rngStr = "B3:C5, F3:G5, K3:L5"
should be:
rngStr = "B3:C5,F3:G5,K3:L5"
(with no spaces).
As I mentionesd, this puts the 3 ranges next to each other as a single table, but with your sheet as it is, it also demponstrates how this fouls up:
View attachment 80507

with data in with the headers (field names). I don't know how to put it right.

So, on to plan B
(The above is not quite my words but changed a bit to make clearer.)

Sorry for the late reply. Thank you for your feedback on the VBA code above. I think it should go for Plan B, as you mentioned. Just to double confirm, is the Power Query on the Google Sheet or in Excel?

 

[p45cal]

Power Query is Excel.
Have you added Named Ranges to the Google Sheet?

 

[Manster]

Power Query is Excel.
Have you added Named Ranges to the Google Sheet?

Yes, I had added it already.

 

[p45cal]

In the attached 4 tables as required.
To refresh them individually, right-click on one and choose Refresh.
To refresh all tables, click on the Refresh All buton on the Queries & Connections section of the Data tab of the ribbon.

 

[Manster]

In the attached 4 tables as required.
To refresh them individually, right-click on one and choose Refresh.
To refresh all tables, click on the Refresh All buton on the Queries & Connections section of the Data tab of the ribbon.

Wow..such amazing! Sir, can you teach me how you do that? I had done this using "Get Data > From Web." However, it is not 'good' like you did. Your kind attention is really appreciated.

 

[p45cal]

See the video linked to in the first response to your cross post at ExcelForum, it's:

It's not exactly the same, I didn't use the long spreadsheet ID twice, so ended up with

https://docs.google.com/spreadsheets/d/1tNCvneyYlvlLJpVJLiina5T34edjXZYA5cq24K6v3a8/export?format=xlsx

then you can follow the steps in the Power Query Editor.

In the last attachment I had 4 queries, each one separately having its own external connection and each fetching its data independently of the other tables. This is a waste and adds time. Instead you can have one query to get the data externally once, then have the other 4 tables refer to that one. See this attachment.

 

[Manster]

See the video linked to in the first response to your cross post at ExcelForum, it's:

It's not exactly the same, I didn't use the long spreadsheet ID twice, so ended up with

https://docs.google.com/spreadsheets/d/1tNCvneyYlvlLJpVJLiina5T34edjXZYA5cq24K6v3a8/export?format=xlsx

then you can follow the steps in the Power Query Editor.

In the last attachment I had 4 queries, each one separately having its own external connection and each fetching its data independently of the other tables. This is a waste and adds time. Instead you can have one query to get the data externally once, then have the other 4 tables refer to that one. See this attachment.

Ok, noted. I will learn from this. If I have any inquiries or problems related to this, hopefully, you will not be bored with me. Thank you so much Ninjas p45cal!