Results 1 to 6 of 6

Macro to Import a data file

This is a discussion on Macro to Import a data file within the Excel Questions forums, part of the Question Forums category; I am trying to create a macro to import a data file which is in .PRN format. I was able ...

  1. #1
    New Member
    Join Date
    May 2002
    Posts
    14

    Default

    I am trying to create a macro to import a data file which is in .PRN format. I was able create macro to open the file, But i dont have an option to open a diiferent file each time.

    I need some help to create a file popup menu to select the data file. The file has to be imported to a different sheet.

    This is my recorded Macro code to do the function:

    Sub Import()
    '
    ' '
    Sheets("Raw Data").Select
    Range("A1").Select
    With ActiveSheet.QueryTables.Add(Connection:= _
    "TEXT;G:PRN'ssp01020402.prn" _
    , Destination:=Range("A1"))
    .Name = "sp01020402_1"
    .FieldNames = True
    .RowNumbers = False
    .FillAdjacentFormulas = False
    .PreserveFormatting = True
    .RefreshOnFileOpen = False
    .RefreshStyle = xlOverwriteCells
    .SavePassword = False
    .SaveData = True
    .AdjustColumnWidth = False
    .RefreshPeriod = 0
    .TextFilePromptOnRefresh = False
    .TextFilePlatform = xlWindows
    .TextFileStartRow = 1
    .TextFileParseType = xlDelimited
    .TextFileTextQualifier = xlTextQualifierDoubleQuote
    .TextFileConsecutiveDelimiter = False
    .TextFileTabDelimiter = False
    .TextFileSemicolonDelimiter = False
    .TextFileCommaDelimiter = True
    .TextFileSpaceDelimiter = False
    .TextFileColumnDataTypes = Array(1, 1, 1, 1, 1, 1, 1, 1, 1, 1)
    .Refresh BackgroundQuery:=False
    End With
    End Sub

    Please help me create a popup menu to Selct a particular data file

  2. #2
    Board Regular
    Join Date
    Apr 2002
    Posts
    85

    Default

    Try the GetOpenFile () function.

    I just found it want it works great for me

  3. #3
    New Member
    Join Date
    May 2002
    Posts
    14

    Default

    I guess it helps but i dont where i need to place the getopenfile()function in my code.

    help Wanted......


  4. #4
    Board Regular
    Join Date
    Apr 2002
    Posts
    85

    Default

    This will prompt to choose location and only ".xls" files. If you need to view other files then change the parameters.

    sub ImportFile ()
    dim fileToOpen as string

    fileToOpen = Application.GetOpenFilename("Microsoft Excel Files (*.xls), *.xls")
    If fileToOpen <> False Then
    Workbooks.Open fileToOpen
    Range("A6").Select
    Else
    Exit Sub
    End If


    end Sub

  5. #5
    New Member
    Join Date
    May 2002
    Posts
    14

    Default

    ThIs isn't working.there is an error at the IF statement....

  6. #6
    Board Regular
    Join Date
    Mar 2002
    Location
    Cincinnati, Ohio, USA
    Posts
    6,824

    Default

    Dim fileToOpen as String
    Change to:
    Dim fileToOpen

    Tom

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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


DMCA.com