Results 1 to 3 of 3
  1. #1
    New Lounger
    Join Date
    Feb 2004
    Location
    Hereford, Herefordshire, England
    Posts
    18
    Thanks
    0
    Thanked 0 Times in 0 Posts
    I'm importing information from another application which is saved as in csv format (but with a txt extension)

    I have found a piece of code from an answer by Hans (thanks as ever Hans) which does the job perfectly, but at I want to close the .txt file at the end.

    I've tried lots of different options, but I need to be careful because a user might have other workbooks at the time and I cannot risk all workbooks being closed:

    Here is the code:

    Dim wkb As Workbook
    Dim rng As Range
    Dim sPathFile As String

    Set rng = ActiveWorkbook.Names("Import").RefersToRange

    sPathFile = Application.GetOpenFilename( _
    "Exports (*.txt),*.txt")
    Workbooks.OpenText Filename:=sPathFile, _
    Origin:=xlWindows, StartRow:=1, DataType:=xlDelimited, _
    TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=True, _
    Tab:=True, Semicolon:=False, Comma:=True, Space:=False, Other:=False
    Set wkb = ActiveWorkbook

    rng.Parent.Cells.Clear
    wkb.Worksheets(1).UsedRange.Copy rng

    Set rng = Nothing
    Set wkb = Nothing
    End Sub

    I think I'm missing something very obvious - which means I can't see what it is!

    Any help greatly appreciated

    Chris

  2. #2
    Plutonium Lounger
    Join Date
    Nov 2001
    Posts
    10,550
    Thanks
    0
    Thanked 7 Times in 7 Posts
    [quote name='ChrisDL' post='770146' date='11-Apr-2009 22:58']I think I'm missing something very obvious - which means I can't see what it is![/quote]
    You could add
    wkb.Close SaveChanges:=False
    just before
    Set wkb = Nothing.

  3. #3
    New Lounger
    Join Date
    Feb 2004
    Location
    Hereford, Herefordshire, England
    Posts
    18
    Thanks
    0
    Thanked 0 Times in 0 Posts
    [quote name='StuartR' post='770148' date='11-Apr-2009 23:29']You could add
    wkb.Close SaveChanges:=False
    just before
    Set wkb = Nothing.[/quote]

    Thanks Stuart - that works perfectly!

Posting Permissions

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