vba code adjustment when importing csv file into excel

[londa_vba]

Hello below is the current code I've been using to import csv file. It works as is but would like to know if its possible to modify what is imported rather than deleting and formatting after it's been imported. I would like to omit columns c,f,h, and i from importing.

Sub ImportCSV()

On Error GoTo eMessage

'set the directory for the exported results folder
If Len(Dir("C:\")) Then
ChDrive "C"
ChDir "C:\LabSave\"

'run the import code (ImportTextFile)
ImportTextFile Fname:=Application.GetOpenFilename(FileFilter:="CSV (Comma Delimited) (*.csv),*.csv"), Sep:=","
If Fname = False Then
Application.ScreenUpdating = True
Exit Sub
End If
Else
retVal = MsgBox("There was an error while connecting to the C: drive. " & vbNewLine & vbNewLine _
& " Select Yes to attempt to find the LabSave folder manually." & vbNewLine & vbNewLine _
& " Select No to cancel the import and verify your network connection.", vbYesNo, "C: drive connection error")
If retVal = vbYes Then
'run the import code (ImportTextFile)
ImportTextFile Fname:=Application.GetOpenFilename(FileFilter:="CSV (Comma delimited) (*.csv),*.csv"), Sep:=","
If Fname = False Then
Application.ScreenUpdating = True
Exit Sub
End If
ElseIf retVal = vbNo Then
Exit Sub
End If
End If
Exit Sub

eMessage:
MsgBox "There was an error while connecting to the C: drive. " & vbNewLine & vbNewLine _
& " Please verify network connection to the C: drive.", vbCritical, "C: drive connection error"
Exit Sub

End Sub

Public Sub ImportTextFile(Fname As String, Sep As String)

Application.ScreenUpdating = False
Application.Cursor = xlWait
Application.DisplayStatusBar = True
Application.StatusBar = "Importing the PLIMS worksheet... "

Dim RowNdx As Long
Dim ColNdx As Integer
Dim TempVal As Variant
Dim WholeLine As String
Dim Pos As Integer
Dim NextPos As Integer
Dim SaveColNdx As Integer

On Error GoTo EndMacro:

SaveColNdx = ActiveCell.Column
RowNdx = ActiveCell.Row

Open Fname For Input Access Read As #1


Dim i As Long
i = 0
While Not EOF(1) And i < 10
Line Input #1, WholeLine
i = i + 1
Wend



While Not EOF(1)
Line Input #1, WholeLine
If Right(WholeLine, 1) <> Sep Then
WholeLine = WholeLine & Sep
End If
ColNdx = SaveColNdx
Pos = 1
NextPos = InStr(Pos, WholeLine, Sep)
While NextPos >= 1
TempVal = Mid(WholeLine, Pos, NextPos - Pos)
Cells(RowNdx, ColNdx).Value = TempVal
Pos = NextPos + 1
ColNdx = ColNdx + 1
NextPos = InStr(Pos, WholeLine, Sep)
Wend
RowNdx = RowNdx + 1
Wend

EndMacro:
On Error GoTo 0

Application.Cursor = xlDefault
Application.StatusBar = False
Application.ScreenUpdating = True

Close #1

End Sub

 

[rlv01]

Generally speaking, it is almost always easier just to import the .csv data then use VBA to delete the columns you do not want.

    With ActiveSheet
        .Columns("C").Delete
        .Columns("F").Delete
        .Columns("H").Delete
        .Columns("I").Delete
    End With

(Tip: For future posts , you should try to use code tags like I did above when posting your code. It makes it easier to read.)

How to Post Your VBA Code

Post your VBA code.

www.mrexcel.com

 

[londa_vba]

Generally speaking, it is almost always easier just to import the .csv data then use VBA to delete the columns you do not want.

    With ActiveSheet
        .Columns("C").Delete
        .Columns("F").Delete
        .Columns("H").Delete
        .Columns("I").Delete
    End With

(Tip: For future posts , you should try to use code tags like I did above when posting your code. It makes it easier to read.)

How to Post Your VBA Code

Post your VBA code.

www.mrexcel.com

ok thank you. I did that originally but I need to be able to import multiple CSV files onto one sheet. When import the first CSV and then delete the columns, it works just fine. Then when I import the second file on the same sheet on a lower row, deleting the columns on the second import messes up the first import so instead of trying to set ranges for each import and columns to delete I was trying to get ahead of the issue. Any additional suggestions?

 

[rlv01]

Any additional suggestions

1. When you do the imports, use a separate, temporary worksheet. Once you import the data and delete the columns, then move the data to your main sheet.

2. Try using the text-to-column import wizard with the macro recorder turned on (the wizard will allow you to exclude columns). Inspecting the macro recorder code might give you some fresh ideas.

The problem with adjusting your current import routine is that it is too quirky and and data driven to be able to untangle it without access to the before/after data and I suspect you are doing things the hard way, given you are trying to process the .csv file line by line as a text file instead of using the many tools excel has available for importing data.

 

[londa_vba]

Thank you for the suggestions. I inherited this code that has been passed down and edited for years all by scientists (non-tech personnel). I will try your suggestions thanks.