Results 1 to 5 of 5
  1. #1
    Star Lounger
    Join Date
    Nov 2002
    Posts
    91
    Thanks
    0
    Thanked 0 Times in 0 Posts

    importing data file (Excel 2000)

    I'm having problems importing a delimited file into excel. When i use the "file - open" menu, some of the data gets changed. When i use the "data - get external data" menu, i get this error message: "runtime error 1004. excel cannot find the text file to refresh this external data range." I need an inputbox to capture the file name. can anyone help please? Below is the macro i've written:

    ' Macro3 Macro
    ' Macro recorded 16/02/2004 by leb01res
    '
    Dim boxname As String
    Let boxname = InputBox("name of file to import")

    'With ActiveSheet.QueryTables.Add(Connection:="TEXT;F:LA B4Dbtests0003577." _
    ' , Destination:=Range("A1"))
    ' .Name = "0003577."

    With ActiveSheet.QueryTables.Add(Connection:="TEXT;F:LA B4Dbtestsboxname." _
    , Destination:=Range("A1"))
    .Name = "boxname."
    .FieldNames = True
    .RowNumbers = False
    .FillAdjacentFormulas = False
    .PreserveFormatting = True
    .RefreshOnFileOpen = False
    .RefreshStyle = xlInsertDeleteCells
    .SavePassword = False
    .SaveData = True
    .AdjustColumnWidth = True
    .RefreshPeriod = 0
    .TextFilePromptOnRefresh = False
    .TextFilePlatform = xlWindows
    .TextFileStartRow = 1
    .TextFileParseType = xlDelimited
    .TextFileTextQualifier = xlTextQualifierDoubleQuote
    .TextFileConsecutiveDelimiter = False
    .TextFileTabDelimiter = True
    .TextFileSemicolonDelimiter = False
    .TextFileCommaDelimiter = False
    .TextFileSpaceDelimiter = False
    .TextFileOtherDelimiter = ":"
    .TextFileColumnDataTypes = Array(1, 2)
    .Refresh BackgroundQuery:=False

    End With
    Range("B13:F36").Select
    Selection.Copy
    Workbooks.Open Filename:="F:LAB4Dbtestspftdata.xls"
    Range("B13:F36").Select
    ActiveSheet.Paste

  2. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: importing data file (Excel 2000)

    The methods I think you should review in VB are:
    GetOpenFilename
    OpenText

    The first will allow you to prompt for the user to browse and find the file (better than having them just type the name). It essentially opens the "Open dialog box"

    Then pass that name as a parameter in
    Workbooks.OpenText

    In this method, you can set the parameters for setting the file to parse as desired.

    I think with GetOpenFileName and OpenText Methods you can do what you want directly without having to do the Query at all.

    If you need additional help after reviewing them, post back and we can try to help further.

    Steve

  3. #3
    Star Lounger
    Join Date
    Nov 2002
    Posts
    91
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: importing data file (Excel 2000)

    thanks Steve. that's worked a treat! muchly appreciated.

    Van

  4. #4
    Star Lounger
    Join Date
    Nov 2002
    Posts
    91
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: importing data file (Excel 2000)

    Hi again Steve. one more question: after i've imported the file, then exported into Access, then closed file.....how do i then have the file deleted from the directory? thanks, Van

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: importing data file (Excel 2000)

    The Kill instruction can be used to delete a file. Syntax:

    Kill "F:LAB4Dbtests" & BoxName

Posting Permissions

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