.csv file won't import correctly into excel file with macro

[fmoaveni]

Hi All,
I am using Excel 2013 and am having trouble with an import macro.

I have a macro that I am currently using to try and import a file into my excel workbook. The file that I am trying to pull in is a .csv file, but it opens in excel when I open it on it's own and it reads just fine. Everything is spaced out great, all columns have correct headers, etc.

The macro pops up a box where it allows me to find the file I need and import it.

It then imports the file and all of the information, but the formatting is all over the place. Most everything ends up in column A and the spacing is so strange.

Does anyone know how to edit this macro to pull the information in the exact same format as the .csv shows normally when it is open without messing it up when I import it?

Here is a copy of the macro:

Sub import()

Dim wb1 As Workbook
Dim wb2 As Workbook
Dim Sheet As Worksheet
Dim PasteStart As Range

Set wb1 = ActiveWorkbook
Set PasteStart = [Artikelinfo!A1]

Sheets("Artikelinfo").Select
Cells.Select
Selection.ClearContents

FileToOpen = Application.GetOpenFilename _
(Title:="Please choose a Report to Parse", _
FileFilter:="Report Files *.csv (*.csv),")

If FileToOpen = False Then
MsgBox "No File Specified.", vbExclamation, "ERROR"
Exit Sub
Else
Set wb2 = Workbooks.Open(Filename:=FileToOpen)

For Each Sheet In wb2.Sheets
With Sheet.UsedRange
.Copy PasteStart
Set PasteStart = PasteStart.Offset(.Rows.Count)
End With
Next Sheet

End If

wb2.Close

End Sub



Thank you! I love this site by the way. So many nice and helpful people!

 

[BarryL]

anytime I open or use CSV files, I have to use the "Text To Columns" function on the data tab.

generally, I think csv files require this. incorporate this in your macro and you should be fine.

 

[Andrew Poulsom]

What is the purpose of this?

    For Each Sheet In wb2.Sheets
        With Sheet.UsedRange
            .Copy PasteStart
            Set PasteStart = PasteStart.Offset(.Rows.Count)
        End With
    Next Sheet

If you want to import a text file into an existing worksheet you need to use OpenText rather than Open. The macro recorder will give you some code that you can adapt.

 

[fmoaveni]

Hi Andrew,
I thought it was to reference just the first sheet in the workbook I want to copy the data from and then paste it into the current excel file.

I am not good at writing macros by any means and I found a sample online and just modified it for my book. Do you have a suggestion on how to use the OpenText? Is that something I would write into the macro? Thank you for the prompt responses.

 

[Andrew Poulsom]

Can you post a sample of your csv file?

 

[fmoaveni]

Sorry but do to private content I am not allowed to post the file.

 

[Andrew Poulsom]

If you record a macro while opening the csv you should get some VBA code.

 

[fmoaveni]

Hi Andrew,
I ended up figuring it out based on the suggestion I found here. I just recorded a macro to format the cells and entered it in the original macro. It is not the best, but it works. Thank you for your time and your help!