Why is my code merging cells?

[tmd63]

I open a text CSV file for import into a new workbook then copy the data out into my workbook!
But the copied data has merged 2 columns together and I cannot figure out why.

    Workbooks.OpenText Filename:=MyFile, Origin:=xlMSDOS, StartRow:=2, DataType:=xlDelimited, TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=False, Semicolon:=False, Comma:=True, Space:=False, Other:=False, FieldInfo:=Array(Array(1, 1), Array(2, 1)), TrailingMinusNumbers:=True
  
    LastRow = ActiveSheet.Cells.Find("*", , xlFormulas, xlPart, xlByRows, xlPrevious).Row
    ActiveSheet.Range("A1:B" & LastRow).Select
    Selection.Copy
  
    Application.DisplayAlerts = False
  
    ActiveWorkbook.Close
  
    Worksheets("CSV").Select
    ActiveSheet.Range("A1:B" & LastRow).Select
    Worksheets("CSV").PasteSpecial

I open this new file from line 2 as line 1 is blank.
But why does the opened CSV file have XXXX in column A and R28 in column B but the copy in my workbook is column A only as XXXX[Tab]R28??

 

[tmd63]

I have managed to work out the issue is with the copy

    LastRow = ActiveSheet.Cells.Find("*", , xlFormulas, xlPart, xlByRows, xlPrevious).Row
    ActiveSheet.Range("A1:B" & LastRow).Select
    Selection.Copy

Appears to merge columns A and B when copying from a code opened CSV file! As I stopped the code after the copy and when pasting, it is already merged. I am trying to copy two columns from a CSV file into an xls sheet.

After I get the user to find the file I use Workbooks.OpenText to open the file.

    Workbooks.OpenText Filename:=MyFile, Origin:=xlMSDOS, StartRow:=2, DataType:=xlDelimited, TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=False, Semicolon:=False, Comma:=True, Space:=False, Other:=False, FieldInfo:=Array(Array(1, 1), Array(2, 1)), TrailingMinusNumbers:=True

 

[Joe4]

It looks like your code is using the comma as a delimiter, and double-quotes as Text Qualifier.
Are the two fields split in your CSV with a comma, and NOT enclosed within double-quotes?

It would probably be beneficial if we could see a small sample of data in your CSV file (BEFORE opening in Excel).
It is really difficult to tell you why the data is merged if we cannot see what the data actually looks like!

 

[tmd63]

It looks like your code is using the comma as a delimiter, and double-quotes as Text Qualifier.
Are the two fields split in your CSV with a comma, and NOT enclosed within double-quotes?

It would probably be beneficial if we could see a small sample of data in your CSV file (BEFORE opening in Excel).
It is really difficult to tell you why the data is merged if we cannot see what the data actually looks like!

[ 100036790 , U7 ]
[ 100036790 , U6 ]
[ 100120628 , J3 ]
[ 100270885 , U9 ]

There are a number of spaces in each line.

Should this file have :
"100036790","U7"
"100036790","U6" etc instead?

 

[Joe4]

I see what you mean. Really odd, it does not seem to like it if you close the CSV file before pasting the data.
This adaptation works for me.

    Dim wb1 As Workbook, wb2 As Workbook
    
'   Capture current workbook as wb1
    Set wb1 = ActiveWorkbook
    
    Workbooks.OpenText Filename:=MyFile, Origin:=xlMSDOS, StartRow:=2, DataType:=xlDelimited, TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=False, Semicolon:=False, Comma:=True, Space:=False, Other:=False, FieldInfo:=Array(Array(1, 1), Array(2, 1)), TrailingMinusNumbers:=True
  
'   Capture new workbook as wb2
    Set wb2 = ActiveWorkbook

    LastRow = Cells.Find("*", , xlFormulas, xlPart, xlByRows, xlPrevious).Row
    Range("A1:B" & LastRow).Copy
  
    Application.DisplayAlerts = False
  
    wb1.Activate
    Worksheets("CSV").Select
    Range("A1").Select
    ActiveSheet.Paste
    
'   Close wb2
    wb2.Close
    
    Application.DisplayAlerts = True

 

[tmd63]

I see what you mean. Really odd, it does not seem to like it if you close the CSV file before pasting the data.
This adaptation works for me.

    Dim wb1 As Workbook, wb2 As Workbook
   
'   Capture current workbook as wb1
    Set wb1 = ActiveWorkbook
   
    Workbooks.OpenText Filename:=MyFile, Origin:=xlMSDOS, StartRow:=2, DataType:=xlDelimited, TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=False, Semicolon:=False, Comma:=True, Space:=False, Other:=False, FieldInfo:=Array(Array(1, 1), Array(2, 1)), TrailingMinusNumbers:=True
 
'   Capture new workbook as wb2
    Set wb2 = ActiveWorkbook

    LastRow = Cells.Find("*", , xlFormulas, xlPart, xlByRows, xlPrevious).Row
    Range("A1:B" & LastRow).Copy
 
    Application.DisplayAlerts = False
 
    wb1.Activate
    Worksheets("CSV").Select
    Range("A1").Select
    ActiveSheet.Paste
   
'   Close wb2
    wb2.Close
   
    Application.DisplayAlerts = True

Not quite working perfectly, but then I am using the backward compatibility and saving the original excel as 97-2003. but this does point me to where the issue was. Thank you.

 

[Joe4]

Not quite working perfectly, but then I am using the backward compatibility and saving the original excel as 97-2003. but this does point me to where the issue was. Thank you.

What was the issue?

 

[tmd63]

What was the issue?

The uploaded CSV file was being closed before the paste.

Sub Insert_CSV()
'
' Insert_CSV Macro
' This copies the CSV external data into the CSV table
       
    Application.ScreenUpdating = False
    Dim MyFile As String
    Dim LastRow As Long
    
    'new variables (from MrExcel)
    Dim wb1 As Workbook, wb2 As Workbook
      
    'Rev AF added error handling
On Error GoTo Err_Insert

    'Workbooks.Open (MyFile)
    
    'Capture current workbook as wb1
    Set wb1 = ActiveWorkbook

    'Clear the CSV page before use
    Worksheets("CSV").Select
    LastRow = ActiveSheet.Cells.Find("*", , xlFormulas, xlPart, xlByRows, xlPrevious).Row
    ActiveSheet.Range("A1:U" & LastRow).Select
    Selection.Delete
     
    'Set the selection interface for users to find the file
    MyFile = Application.GetOpenFilename("Excel Files (*.*),*.*", , "Select Procurement CSV File", "Open", False)

    'Open the selected file as a comma delimited workbook
    Workbooks.OpenText Filename:=MyFile, Origin:=xlMSDOS, StartRow:=2, DataType:=xlDelimited, TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=False, Semicolon:=False, Comma:=True, Space:=False, Other:=False, FieldInfo:=Array(Array(1, 1), Array(2, 1)), TrailingMinusNumbers:=True
      
    'Capture new workbook as wb2
    Set wb2 = ActiveWorkbook
      
    'Select the first 2 columns of the new workbook and copy
    LastRow = ActiveSheet.Cells.Find("*", , xlFormulas, xlPart, xlByRows, xlPrevious).Row
    ActiveSheet.Range("A1:B" & LastRow).Select
    Selection.Copy
    
    Application.DisplayAlerts = False
    
    wb1.Activate
    Worksheets("CSV").Select
    ActiveSheet.Range("A1").Select
    Worksheets("CSV").Paste
    
    'Close the new workbook
    wb2.Close
    
    Application.DisplayAlerts = True
        
    MsgBox ("CSV has been loaded")
    
    Exit Sub

Err_Insert:
    MsgBox Err.Description, vbCritical, Err.Number
    
End Sub

 

[Joe4]

Ah, so it was what I said.

I see what you mean. Really odd, it does not seem to like it if you close the CSV file before pasting the data.

I don't pretend to understand why that is a problem (and it is a problem on 365 too), but noticed that it does seem to work when you wait to close the file until after you paste.
I wish I had an explanation as to "why", but I guess as long as it works, that is the important thing.