Import of CSV file causes file repair in Excel

207 Views Asked by At

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

0

There are 0 best solutions below