VBScript Excel, processing empty strings; Error 62

Question

Hi all,

I am currently trying to sync Google Sheets to an Excel sheet file. The way it's currently configured is that all changes made in Google Sheets, are saved to a text file in my Dropbox account. Excel reads that text file and updates the required cells in the Excel sheet. However, if I delete cells in Google Sheets, it means the text files are empty. If Excel tries to read these changes it will give me "Input past end of file (Error 62)", because it cannot read empty strings from what I understand.

Sub SyncFromGoogle()
Dim FileName As String, FolderPath As String, FilePath As String, CellValue As String, CellAdd As String, SheetName As String

FolderPath = "C:\Users\Me\Dropbox\ExcelSync\"
FileName = Dir(FolderPath & "*.txt")

Do While Len(FileName) > 0
FilePath = FolderPath & FileName
Open FilePath For Input As #1
Line Input #1, CellValue
Close #1

SheetName = Left(FileName, InStr(FileName, "-") - 1)
CellAdd = Mid(FileName, InStr(FileName, "-") + 1, InStr(FileName, ".") - InStr(FileName, "-") - 1)
ThisWorkbook.Sheets(SheetName).Range(CellAdd).Value = CellValue
Kill (FilePath)
FileName = Dir()

Loop

End Sub

Is there a way to detect empty files, so that it actually empties the applied cells in my Excel sheet? Thanks in advance.

Answer 1

Try one of approaches:

. . .
Dim skip As Boolean
On Error Resume Next
Line Input #1, CellValue
skip = Err.Number = 62
On Error GoTo 0
Close #1
If Not skip Then
   ' process the value . . .
   . . .
End If

Improve it to handle (do not ignore) other errors.