Import a cell in Google sheets as a VBA variable (in Word)

[stuff84]

Hi everyone.
I am relatively new to VBA, and while I have found several threads to import entire ranges from a Google sheet to MS Excel, I haven't been able to find this answer.
I want to be able to produce a form in Google sheets, and use it to populate content control in a Word document.
The part I am getting stuck on is how do i import a single cell from a Google Sheet (A1), and use it as a string variable in VBA (for Word).
Thank you very much!

 

[stuff84]

To elaborate a little more I was able to figure this out in Google Sheets Scripting, but I am not as familiar with VBA.

In the following, a variable "NewVariable" will be pulled from the Google Sheet located at Variable to Import

Does anyone know how to do this in VBA (for Word, not Excel)?

Thank you!!

var NewVariable = ('Variable_from_Google_Sheet');
NewVariable = SpreadsheetApp.open(DriveApp.getFileById("1voJ22SzX10iJWTLMG9BEHELBgH2ShzU_BBklOGOZd4g")).getRange('A1').getValue();

 

[stuff84]

Me again, lol.
I am such a noob. I am hoping it is not just me, and there isn't a way that I can edit my posts to add more details (rather than me talking to myself in 3 posts, lol).
Anyways, I wanted to add, that I found this method that gets me 90% of the way there, using Query tables.
However, with this method, you must define a destination range for the imported data, when what I want is to use the range (of a single cell) and have it used as a variable.

Thank you all again for any suggestions.

Taken from: How to import data from Google Sheets into Excel with VBA

Sub importGsheetLads()

t
 Dim keyString As String
  
 Dim gidString As String
 
 
 
 '' change the below keyString and gidString values as required
 
 
 keyString = "14hOICbxkYfCPpwpf8E6kwQHjrp2b33KjWfjMzjqGG8E"
 
 gidString = "493421964"
 
 
 
 
 
  
     With ActiveSheet.QueryTables.Add(Connection:="URL;https://spreadsheets.google.com/tq?tqx=out:html&tq=&key=" & keyString & "&gid=" & gidString, Destination:=Range("$A$1"))
        .Name = "q?s=goog_2"
        .FieldNames = True
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .BackgroundQuery = True
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .WebSelectionType = xlSpecifiedTables
        .WebFormatting = xlWebFormattingNone
        .WebTables = "1,2"
        .WebPreFormattedTextToColumns = True
        .WebConsecutiveDelimitersAsOne = True
        .WebSingleBlockTextImport = False
        .WebDisableDateRecognition = False
        .WebDisableRedirections = False
        .Refresh BackgroundQuery:=False
        
        
 End With


End Sub

 

[Macropod]

I don't use Google Sheets, so I can't help directly with that. The following link, though, shows how you might populate Word content controls from an Excel workbook:

How to import list from Excel into drop-down list into word

[Solved] How to import list from Excel into drop-down list into word Word VBA

www.msofficeforums.com

Maybe you can adapt that to your needs.