4

I have a csv file that constantly needs opened into Excel and then have the data copied over to a separate workbook.

I find the process of having to click through all of the dialogs, setting the text identifier, setting the columns to all be text extremely tedious.

In many actions with data like this in regards to MSSQL or Access the program will ask you if you wish to save these steps however Excel doesn't readily ask that.

Is there any way to get a comparable usage with Excel?

2 Answers 2

2

Once you have imported the data, you can select the data range, right-click, and select "Refresh Data".

This will prompt you for a file – and it inserts the updated data without needing to go through all the steps you mention above.

It works in Office 2011 for OS X, but it should also work in earlier Windows versions.

6
  • So for this solution would you recommend just creating a .xslx file in the same directory as my csv file, do this once and then let the csv be overwriten as needed and push refresh? Jun 20, 2011 at 16:38
  • It doesn't need to be in the same directory -- but yeah, after you've updated the CSV, hit "Refresh" and it'll ask you for a file, select that file again, done. Just try it out if it works for you!
    – slhck
    Jun 20, 2011 at 16:42
  • This worked out great, any chance you know of a way for me to establish that I trust my file instead of getting the stupid data connection warning everytime? Jun 24, 2011 at 12:58
  • I guess there's a way -- check out this help file from Microsoft about external content!
    – slhck
    Jun 24, 2011 at 13:00
  • 1
    Digging through that I found adding a trusted location is what I wanted to do. Jun 24, 2011 at 13:10
2

Use the macro recorder, and record it all in a macro.

Here's what I got when I macroed a common import of mine:

With ActiveSheet.QueryTables.Add(Connection:="TEXT;E:\AEP_out.txt", _
    Destination:=Range("A1"))
    .Name = "AEP_out"
    .FieldNames = True
    .RowNumbers = False
    .FillAdjacentFormulas = False
    .PreserveFormatting = True
    .RefreshOnFileOpen = False
    .RefreshStyle = xlInsertDeleteCells
    .SavePassword = False
    .SaveData = True
    .AdjustColumnWidth = True
    .RefreshPeriod = 0
    .TextFilePromptOnRefresh = False
    .TextFilePlatform = 437
    .TextFileStartRow = 1
    .TextFileParseType = xlFixedWidth
    .TextFileTextQualifier = xlTextQualifierDoubleQuote
    .TextFileConsecutiveDelimiter = False
    .TextFileTabDelimiter = True
    .TextFileSemicolonDelimiter = False
    .TextFileCommaDelimiter = False
    .TextFileSpaceDelimiter = False
    .TextFileColumnDataTypes = Array(2, 1, 1, 1)
    .TextFileFixedColumnWidths = Array(27, 16, 22)
    .TextFileTrailingMinusNumbers = True
    .Refresh BackgroundQuery:=False
End With
0

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .