Solved

Excel 2013 - Open CSV as Excel - Automated

Posted on 2016-09-23
11
45 Views
Last Modified: 2016-10-23
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
Comment
Question by:ReneGe
  • 4
  • 3
  • 2
  • +1
11 Comments
 
LVL 17

Assisted Solution

by:xtermie
xtermie earned 100 total points (awarded by participants)
ID: 41812037
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
 
LVL 76

Expert Comment

by:GrahamSkan
ID: 41812044
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
 
LVL 76

Expert Comment

by:GrahamSkan
ID: 41812046
Oops. Should have refreshed my browser view before posting.
0
 
LVL 48

Accepted Solution

by:
Rgonzo1971 earned 400 total points (awarded by participants)
ID: 41812053
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
 
LVL 10

Author Comment

by:ReneGe
ID: 41812223
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
Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

 
LVL 48

Expert Comment

by:Rgonzo1971
ID: 41812253
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
 
LVL 10

Author Comment

by:ReneGe
ID: 41816588
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
 
LVL 48

Assisted Solution

by:Rgonzo1971
Rgonzo1971 earned 400 total points (awarded by participants)
ID: 41817202
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
 
LVL 10

Author Comment

by:ReneGe
ID: 41823884
Ok thanks

I"ll test it later today

Theers :)
0
 
LVL 10

Author Comment

by:ReneGe
ID: 41825676
Hi Rgonzo,

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

Thank you so much for your help :)

Cheers mate!
0
 
LVL 17

Expert Comment

by:xtermie
ID: 41855848
code provided works as author states
0

Featured Post

Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

Join & Write a Comment

Recently Microsoft released a brand new function called CONCAT. It's supposed to replace its predecessor CONCATENATE. But how does it work? And what's new? In this article, we take a closer look at all of this - we even included an exercise file for…
In this article we discuss how to recover the missing Outlook 2011 for Mac data like Emails and Contacts manually.
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

708 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now