2

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?

Community
  • 1
Samizdat2003
  • 23

1 Answers1

1

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.

dav
  • 10,618