I have an Excel sheet that I would like to serve as the backup of a Google sheet, which I'll be clearing out periodically to prevent it from slowing down. I'm attempting to write a macro which, after a set period of time, will find the next empty row in the Excel sheet, activate the cell in column "A", and import the data from the Google Sheet. I don't want to "refresh" the data in Excel, because the plan is to delete the data in the Google Sheet every so often while the Excel sheet serves as a continuous record. I would simply like to pull the current Google Sheet data into the first cell of the next empty row, and schedule this to repeat.
Here's what I've been trying:
Sub addData()
newCell = Sheet1.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Address
MsgBox newCell
Sheet1.QueryTables.Add(Connection:= _
"URL;googleSheetURL" _
, Destination:=Range(newCell))
.PostText = "transaction-data_1"
.Name = False
.FieldNames = False
.RefreshStyle = xlInsertDeleteCells
.RowNumbers = False
.FillAdjacentFormulas = False
.HasAutoFormat = False
.RefreshOnFileOpen = 2
.BackgroundQuery = False
.TablesOnlyFromHTML = True
.SaveData = True
.Refresh BackgroundQuery:=False
.UseListObject = False
End With
End Sub
Where googleSheetURL
is replaced with the published link of the sheet.
I just keep getting errors, debug mode highlights the Refresh BackgroundQuery
line. I disabled background refresh because I didn't want the queries to update once I pulled them. Does anyone have any insight?