Pulling external data to next empty row as static, then repeating with next empty row? (Backing up Google Sheet data to Excel)

Question

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?

Excelosaurus Excelosaurus · Accepted Answer · 2017-11-14T22:06:37

This code doesn't compile. You're missing a With in front of Sheet1:

Sub addData()
    newCell = Sheet1.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Address

    With 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

Pulling external data to next empty row as static, then repeating with next empty row? (Backing up Google Sheet data to Excel)

1 Answers