I discovered that the below code for importing a csv file into Excel corrupts the file structure and triggers file repair. The error message is "Repaired Records: External formula reference from /xl/connections.xml part (Data connection)". The repair mode failes and restarts when reopening the saved file.
Error log: error273200_01.xmlErrors were detected in file 'C:\Users\User\Desktop\New.xlsm'Repaired Records: External formula reference from /xl/connections.xml part (Data connection)
The error is reproducible when I run the code in a blank spreadsheet.
Anyone had the same issue and know how to fix?
Sub CSV_Import()
Dim ws As Worksheet, strFile As String
Dim ws1 As Worksheet
Dim SourceRng As Range
Set lookRange = Sheets("Destination_Sheet").Range("C2:C999")
Set StartRange = Sheets("Destination_Sheet").Range("B2:B999")
Set ws1 = ActiveWorkbook.Sheets("Destination_Sheet")
'import csv file
Worksheets.Add
ActiveSheet.Name = "Import_CSV"
Set ws = ActiveWorkbook.Sheets("Import_CSV")
strFile = Application.GetOpenFilename("Text Files (*.csv),*.csv", , "Select csv file...")
With ws.QueryTables.Add(Connection:="TEXT;" & strFile, Destination:=ws.Range("A1"))
.TextFileParseType = xlDelimited
.TextFileCommaDelimiter = True
.Refresh
End With
' Transfer data to Destination Sheet
Dim wsCopy As Worksheet
Dim wsDest As Worksheet
Dim lCopyLastRow As Long
Dim lDestLastRow As Long
Set wsCopy = Worksheets("Import_CSV")
Set wsDest = Worksheets("Destination_Sheet")
Application.DisplayAlerts = False
Worksheets("Destination_Sheet").Cells.Clear
Application.DisplayAlerts = True
wsCopy.Cells.Copy Destination:=wsDest.Range("A1")
Application.CutCopyMode = False
wsCopy.Cells.ClearContents
' delete temp sheet
Application.DisplayAlerts = False
Sheets("Import_CSV").Delete
Application.DisplayAlerts = True
End Sub