Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

Excel 2013 - Open CSV as Excel - Automated

Posted on 2016-09-23
11
Medium Priority
?
131 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 18

Assisted Solution

by:xtermie
xtermie earned 400 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 77

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 77

Expert Comment

by:GrahamSkan
ID: 41812046
Oops. Should have refreshed my browser view before posting.
0
Free Backup Tool for VMware and Hyper-V

Restore full virtual machine or individual guest files from 19 common file systems directly from the backup file. Schedule VM backups with PowerShell scripts. Set desired time, lean back and let the script to notify you via email upon completion.  

 
LVL 54

Accepted Solution

by:
Rgonzo1971 earned 1600 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
 
LVL 54

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 54

Assisted Solution

by:Rgonzo1971
Rgonzo1971 earned 1600 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 18

Expert Comment

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

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.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Microsoft's Excel has many features that most people will never need nor take advantage of.  Conditional formatting is one feature that you may find a necessity once you start using it.
In this article, I will demonstrate that how to do a PST migration from Exchange Server to Office 365. This method allows importing one single PST, or multiple PST's at once.
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…

581 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