• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 160
  • Last Modified:

Excel 2013 - Open CSV as Excel - Automated

Hi there,

I must very often "open csv file" in Excel 2013 (local install) with the following parameters.

How can that be automated this so I don`t have to always pass through this import wizard?

I don`t know if it is possible but for me, the best option would be to setup something so all I have to do is to right-click on the file -> "Send to" -> "Import that csv"

PARAMETERS:
Import Delimited
65001 : Unicode (UTF-8)
My data has headers
Delimiters = Comma

Columns data format:
A number
B text
C date YYYY-MM-DD
D number
E text
F text
G text
H number
I text
J-M text
N date YYYY-MM-DD
O number
P number
Q text
R text
S text
T text
U text
V date YYYY-MM-DD
W number

Import starting in A1
0
ReneGe
Asked:
ReneGe
  • 4
  • 3
  • 2
  • +1
3 Solutions
 
xtermieCommented:
If its a valid CSV file, then Excel can open it directly. So just write your VBscript to tell Excel to open the CSV file as though it was a workbook. Then you can copy the data you want from that workbook into your workbook.

The other way is VBA, which is the best way, but you should be able to mock it up in VBscript as well.
Here is what I think it should look like, though I cannot test to be sure. You can change the parameters to yours accordingly.
Sub ImportAllCSV()
  Dim FName As Variant, R As Long
  R = 1
  FName = Dir("*.csv")
  Do While FName <> ""
    ImportCsvFile FName, ActiveSheet.Cells(R, 1)
    R = ActiveSheet.UsedRange.Rows.Count + 1
    FName = Dir
  Loop
End Sub

Sub ImportCsvFile(FileName As Variant, Position As Range)
  With ActiveSheet.QueryTables.Add(Connection:= _
      "TEXT;" & FileName _
      , Destination:=Position)
      .Name = Replace(FileName, ".csv", "")
      .FieldNames = True
      .RowNumbers = False
      .FillAdjacentFormulas = False
      .RefreshOnFileOpen = False
      .BackgroundQuery = True
      .RefreshStyle = xlInsertDeleteCells
      .SavePassword = False
      .SaveData = True
      .AdjustColumnWidth = True
      .TextFilePromptOnRefresh = False
      .TextFilePlatform = xlMacintosh
      .TextFileStartRow = 1
      .TextFileParseType = xlDelimited
      .TextFileTextQualifier = xlTextQualifierDoubleQuote
      .TextFileConsecutiveDelimiter = False
      .TextFileTabDelimiter = True
      .TextFileSemicolonDelimiter = False
      .TextFileCommaDelimiter = False
      .TextFileSpaceDelimiter = False
      .TextFileOtherDelimiter = ","
      .TextFileColumnDataTypes = Array(1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1)
      .Refresh BackgroundQuery:=False
  End With
End Sub

Open in new window

0
 
GrahamSkanRetiredCommented:
The default application for .csv files is Excel, so you should only need to double-click the file name in windows explorer to open the file in Excel.

If Excel is already open, you can use File/Open and select Text Files or All File to see it in the Open dialogue.
0
 
GrahamSkanRetiredCommented:
Oops. Should have refreshed my browser view before posting.
0
Cloud Class® Course: Python 3 Fundamentals

This course will teach participants about installing and configuring Python, syntax, importing, statements, types, strings, booleans, files, lists, tuples, comprehensions, functions, and classes.

 
Rgonzo1971Commented:
HI,

pls try
Sub Macro()

'
strFileToOpen = Application.GetOpenFilename("Csv Files (*.csv), *.csv")
If strFileToOpen <> False Then


    With ActiveSheet.QueryTables.Add(Connection:= _
        "TEXT;" & strFileToOpen, Destination:=Range("$A$1"))
        .Name = Mid(strFileToOpen, InStrRev(strFileToOpen, "\") + 1)
        .FieldNames = True
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .TextFilePromptOnRefresh = False
        .TextFilePlatform = 65001
        .TextFileStartRow = 1
        .TextFileParseType = xlDelimited
        .TextFileTextQualifier = xlTextQualifierDoubleQuote
        .TextFileConsecutiveDelimiter = False
        .TextFileTabDelimiter = False
        .TextFileSemicolonDelimiter = False
        .TextFileCommaDelimiter = True
        .TextFileSpaceDelimiter = False
        .TextFileColumnDataTypes = Array(xlGeneralFormat, xlTextFormat, xlYMDFormat, xlGeneralFormat, xlTextFormat, xlTextFormat, _
                xlTextFormat, xlGeneralFormat, xlTextFormat, xlTextFormat, xlYMDFormat, xlGeneralFormat, xlGeneralFormat, _
                xlGeneralFormat, xlTextFormat, xlGeneralFormat, xlTextFormat, xlTextFormat, xlYMDFormat, xlGeneralFormat)
        .TextFileTrailingMinusNumbers = True
        .Refresh BackgroundQuery:=False
    End With
    For Each cn In ActiveWorkbook.Connections
        cn.Delete
    Next
End If
End Sub

Open in new window

Regards
0
 
ReneGeAuthor Commented:
This look awesome mate. Thanks :)

How do I use this for what I need?

I really appreciate what you did.  I just need to know how to apply it.

Cheers :)
0
 
Rgonzo1971Commented:
Hi,

You could have a excel file with a button to create the new file
Sub Macro()

'
strFileToOpen = Application.GetOpenFilename("Csv Files (*.csv), *.csv")
If strFileToOpen <> False Then

    Workbooks.Add
    With ActiveSheet.QueryTables.Add(Connection:= _
        "TEXT;" & strFileToOpen, Destination:=Range("$A$1"))
        .Name = Mid(strFileToOpen, InStrRev(strFileToOpen, "\") + 1)
        .FieldNames = True
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .TextFilePromptOnRefresh = False
        .TextFilePlatform = 65001
        .TextFileStartRow = 1
        .TextFileParseType = xlDelimited
        .TextFileTextQualifier = xlTextQualifierDoubleQuote
        .TextFileConsecutiveDelimiter = False
        .TextFileTabDelimiter = False
        .TextFileSemicolonDelimiter = False
        .TextFileCommaDelimiter = True
        .TextFileSpaceDelimiter = False
        .TextFileColumnDataTypes = Array(xlGeneralFormat, xlTextFormat, xlYMDFormat, xlGeneralFormat, xlTextFormat, xlTextFormat, _
                xlTextFormat, xlGeneralFormat, xlTextFormat, xlTextFormat, xlYMDFormat, xlGeneralFormat, xlGeneralFormat, _
                xlGeneralFormat, xlTextFormat, xlGeneralFormat, xlTextFormat, xlTextFormat, xlYMDFormat, xlGeneralFormat)
        .TextFileTrailingMinusNumbers = True
        .Refresh BackgroundQuery:=False
    End With
    For Each cn In ActiveWorkbook.Connections
        cn.Delete
    Next
End If
End Sub

Open in new window

ImportCsv.xlsm
0
 
ReneGeAuthor Commented:
Hi Rgonzo1971,

Sorry for taking so ling to reply.

Thanks for your macro.  It works :)

Now all I have to do, is to know how to start excel, start your macro and import the file.

Something like: this made up command line:
excel "ImportCsv.xlsm" /macro:"MacroName" "CsvFileName.csv"

This way, I'll be able to add this command line in the "send to" folder.

Thanks :)
0
 
Rgonzo1971Commented:
Hi,

the simplest way would be to call the macro at the open event (to be added In ThisWorkbook Module)

Private Sub Workbook_Open()
    Call MacroName
End Sub

Open in new window

then you can use the command

EXCEL.EXE /e "c:\ImportCsv.xlsm"
0
 
ReneGeAuthor Commented:
Ok thanks

I"ll test it later today

Theers :)
0
 
ReneGeAuthor Commented:
Hi Rgonzo,

I should be able to try this in the next few days.

Thank you so much for your help :)

Cheers mate!
0
 
xtermieCommented:
code provided works as author states
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

  • 4
  • 3
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now