Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

I tried using a bit of code provided from this previous Super User Answer:

Sub OpenCsvAsText(ByVal strFilepath As String)

    Dim intFileNo As Integer
    Dim iCol As Long
    Dim nCol As Long
    Dim strLine As String
    Dim varColumnFormat As Variant
    Dim varTemp As Variant

    '// Read first line of file to figure out how many columns there are
    intFileNo = FreeFile()
    Open strFilepath For Input As #intFileNo
    Line Input #intFileNo, strLine
    Close #intFileNo
    varTemp = Split(strLine, ",")
    nCol = UBound(varTemp) + 1

    '// Prepare description of column format
    ReDim varColumnFormat(0 To nCol - 1)
    For iCol = 1 To nCol
        varColumnFormat(iCol - 1) = Array(iCol, xlTextFormat)
        ' What's this? See VBA help for OpenText method (FieldInfo argument).
    Next iCol

    '// Open the file using the specified column formats
    Workbooks.OpenText _
            Filename:=strFilepath, _
            DataType:=xlDelimited, _
            ConsecutiveDelimiter:=False, Comma:=True, _
            FieldInfo:=varColumnFormat

End Sub

I inserted the "Sub OpenCsvAsText... End Sub" code into a module in my Excel 2010 workbook. However I can't figure out how to run it. It does not appear in the list of available macros, even though other macros do (I set security to enable all macros.) On the page above usage is specified as:

OpenCsvAsText "C:\MyDir\MyFile.txt"

Am I supposed to run this from the command line, or from a prompt within Excel? If so, where?

share|improve this question
    
Please edit your question to include the actual code you are using so we can help you. –  CharlieRB May 29 '13 at 14:37
    
Try entering OpenCsvAsText "C:\MyDir\MyFile.txt" into a cell within the work sheet. –  KronoS May 29 '13 at 14:41
    
I tried entering it into a cell but nothing happens. I just end up with a cell containing that text. –  Samizdat2003 May 29 '13 at 19:13

1 Answer 1

up vote 0 down vote accepted

In the module where you've pasted the Sub you reference above, create a Macro to run the Sub.

Sub Macro_OpenCsvAsText()

Dim strfilepath_input As String 'create a variable to receive the filename from the macro

strfilepath_input = InputBox("Enter a filename", "Open CSV as Text", vbOKCancel) 'create an input box to ask for a name

OpenCsvAsText (strfilepath_input) 'run the sub you already copied to a module in your workbook

'add some error trapping in case you don't add a name, or the name is invalid

End Sub

You can add some sophistication by using a lookup dialog to navigate to a file, rather than having to type it in. Also, don't forget your error trapping, or you'll be chasing debugs all over the place.

share|improve this answer
    
But if I insert the code below into a module, I see a macro named "Count" in the Macro box even though the code does not start with SubMacro (). Why? –  Samizdat2003 May 29 '13 at 14:39
    
Sorry here is the code: Sub Count() myCount = Selection.Rows.Count 'Change Rows to Columns to count columns MsgBox myCount End Sub –  Samizdat2003 May 29 '13 at 14:40
    
See corrected code –  dav May 29 '13 at 19:21
    
Thanks, David; that worked. –  Samizdat2003 May 29 '13 at 20:05

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.