select CSV file to import problem

yoniman · #1 (**permalink**) 02-15-05, 10:37

hi all
here is the deal
I want to import a CSV file into excel worksheet but I can't tell in advanve from which file; that is I want the program to ask me which file to import (just like one can do with open or save). any ideas?
thanks
yoniman

DavidCoutts · #2 (**permalink**) 02-15-05, 10:51

Application.GetOpenFileName

should do the trick for you

HTH
Dave

yoniman · #3 (**permalink**) 02-15-05, 11:06

thanks Dave but not good enough -
the csv is tab deliminated and so I must go through import external data and cann't just open the file.

any other suggestions

DavidCoutts · #4 (**permalink**) 02-15-05, 11:17

instead of opening use the opentext method of the workbook class
try something like this

Code:

Sub opntext()
    Dim OpenName As Variant
    Dim flName As Variant
    
    OpenName = Application.GetOpenFilename
    
    If IsArray(OpenName) Then
        For Each flName In OpenName
            Workbooks.OpenText flName, , , , , , True
        Next flName
    Else
        Workbooks.OpenText OpenName, , , , , , True
    End If
    
End Sub

yoniman · #5 (**permalink**) 02-16-05, 10:20

sorry, it's not doing the job -
sure the sub opens the csv file but it doesn't allow me to go through the page where I choose the tab delimiters as the "import data" routine does.
when I record the sub I get:
"TEXT;D:\My Documents\EXP\TOF\2495a.csv", Destination:=Range("A1"))Destination:=Range("A1"))

PHP Code:


			
        .Name = fname

        .FieldNames = True

        .RowNumbers = False

        .FillAdjacentFormulas = False

        .PreserveFormatting = True

        .RefreshOnFileOpen = False

        .RefreshStyle = xlInsertDeleteCells

        .SavePassword = False

        .SaveData = True

        .AdjustColumnWidth = True

        .RefreshPeriod = 0

        .TextFilePromptOnRefresh = False

        .TextFilePlatform = 862

        .TextFileStartRow = 1

        .TextFileParseType = xlDelimited

        .TextFileTextQualifier = xlTextQualifierDoubleQuote

        .TextFileConsecutiveDelimiter = False

        .TextFileTabDelimiter = True

        .TextFileSemicolonDelimiter = False

        .TextFileCommaDelimiter = False

        .TextFileSpaceDelimiter = False

        .TextFileColumnDataTypes = Array(1, 1)

        .TextFileTrailingMinusNumbers = True

        .Refresh BackgroundQuery:=False

    End With

and the lineI need to modify is the first one where instead of a full address I want an open question to pop, letting me to9 choose the file.

DavidCoutts · #6 (**permalink**) 02-16-05, 11:30

ok then try this

Code:

Sub opntext()
    Dim OpenName As String
    Dim FileNum As Integer
    Dim I As Integer
    Dim inputstring As String
    
    'turn off alerts
    Application.DisplayAlerts = False
    
    'get the filename required (allow only 1 file)
    OpenName = Application.GetOpenFilename(, , , , False)
    
    FileNum = FreeFile
    Open OpenName For Input As #FileNum
        I = 1
        'read textfile to lines of worksheet
        Do Until EOF(FileNum)
            Line Input #1, inputstring
            Cells(I, 1).Value = inputstring
            I = I + 1
        Loop
    Close #FileNum
    
    'select range and bring up texttocolumns dialog
    Range(Cells(1, 1), Cells(I - 1, 1)).Select
    Application.Dialogs(xlDialogTextToColumns).Show
        
    'do some other stuff
            
    'turn alerts back on
    Application.DisplayAlerts = True
    
End Sub

DavidCoutts · #7 (**permalink**) 02-16-05, 11:37

2ndly what you asked for twice now is to return the application name, by which i presume you mean filepath, filename, from a dialog like the open dialog

which is what
Application.GetOpenFilenam
Does

Brett Kaiser · #8 (**permalink**) 02-16-05, 12:58

Did you try to record a macro, do it manually and see what it does?

yoniman · #9 (**permalink**) 02-17-05, 02:35

thanx Dave - I'll try it now and let u know.
Brett - the initial problem was that by recording I can specify one file only and what I need is the possibility to choose which file I want at every deferent macro run.

Internet Services

Web Development

Digital Marketing

Consumer Tech