Error Checking in Excel
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 6 of 6

Thread: Macro to Import a data file

  1. #1
    New Member
    Join Date
    May 2002
    Posts
    14
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    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
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    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
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    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
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    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
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    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
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Dim fileToOpen as String
    Change to:
    Dim fileToOpen

    Tom

Some videos you may like

User Tag List

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
  •