Results 1 to 9 of 9
  1. #1
    Join Date
    Feb 2005
    Posts
    4

    Unanswered: select CSV file to import problem

    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

  2. #2
    Join Date
    Jan 2004
    Location
    Aberdeen, Scotland
    Posts
    1,067
    Application.GetOpenFileName

    should do the trick for you

    HTH
    Dave

  3. #3
    Join Date
    Feb 2005
    Posts
    4
    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

  4. #4
    Join Date
    Jan 2004
    Location
    Aberdeen, Scotland
    Posts
    1,067
    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

  5. #5
    Join Date
    Feb 2005
    Posts
    4
    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(11)
            .
    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.

  6. #6
    Join Date
    Jan 2004
    Location
    Aberdeen, Scotland
    Posts
    1,067
    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

  7. #7
    Join Date
    Jan 2004
    Location
    Aberdeen, Scotland
    Posts
    1,067
    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

  8. #8
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Did you try to record a macro, do it manually and see what it does?
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  9. #9
    Join Date
    Feb 2005
    Posts
    4
    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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •