Close text file previously opened from excel-CodePudding

I keep getting a run time error 9 when trying to close a text file I have opened with excel. I have removed the rest of the code to keep this brief. I have had a bit of a look online and I can't see what's wrong. Please help.

Sub ImportBGSGeol()
'
' ImportBGSGeol Macro
' Import a text file of geology codes from BGS geoindex and process
'
' Define variables
Dim wkb As Workbook
Dim ws As Worksheet
Dim FName As String
     
Application.Calculation = xlAutomatic
    
' Assign variable to workbook and worksheet
Set wkb = ThisWorkbook

wkb.Worksheets.Add().Name = "Summary Table"

Set ws = ActiveSheet

    'Open text file and copy data

    FName = Application.GetOpenFilename(Title:="Please choose BGS text file downloaded from Geoindex to open", _
                                          FileFilter:="Text Files *.txt* (*.txt*),")

    Workbooks.OpenText Filename:= _
        FName _
        , Origin:=xlMSDOS, StartRow:=1, DataType:=xlDelimited, TextQualifier:= _
        xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, Semicolon:=False, _
        Comma:=False, Space:=False, Other:=False, FieldInfo:=Array(Array(1, 1), _
        Array(2, 1)), TrailingMinusNumbers:=True
               
        
    ActiveSheet.Range("A1").CurrentRegion.Copy
    
    'Paste data to Summary Table and format
    ws.Range("A1").PasteSpecial
    
    Application.Workbooks(FName).Close SaveChanges:=False
    
    ws.Activate
    Columns("A:A").EntireColumn.AutoFit
        
wkb.Save
    
End Sub

CodePudding user response：

https://www.mrexcel.com/board/threads/workbooks-open-vs-workbooks-opentext.294985/

I made a new workbook object from the opened text file and then closed it.

CodePudding user response：

Import Text File

Use variables: they don't slow down the code but make it more readable (maintainable).

Option Explicit

Sub ImportBGSGeol()
    '
    ' ImportBGSGeol Macro
    ' Import a text file of geology codes from BGS geoindex and process
    '
    Const dwsName As String = "Summary Table"
    
    Application.Calculation = xlCalculationAutomatic ' xlAutomatic??
        
    ' Destination
        
    ' Assign variable to workbook and worksheet
    Dim dwb As Workbook: Set dwb = ThisWorkbook
    
    Application.ScreenUpdating = False
    
    ' Delete the destination sheet if it already exists.
    Dim dws As Object
    On Error Resume Next
    Set dws = dwb.Sheets(dwsName)
    On Error GoTo 0
    If Not dws Is Nothing Then ' destination worksheet exists
        Application.DisplayAlerts = False ' delete without confirmation
        dws.Delete
        Application.DisplayAlerts = True
    'Else ' destination worksheet doesn't exist
    End If
    
    ' Add and rename a new (Destination) worksheet.
    Set dws = dwb.Worksheets.Add(After:=dwb.Sheets(dwb.Sheets.Count)) ' last
    dws.Name = dwsName
    
    ' Destination First Cell
    Dim dCell As Range: Set dCell = dws.Range("A1")
    
    ' Source
    
    Dim sFilePath As String: sFilePath = Application.GetOpenFilename( _
        Title:="Please choose BGS text file downloaded from Geoindex to open", _
        FileFilter:="Text Files *.txt* (*.txt*),")
    
    Dim swb As Workbook
    Set swb = Workbooks.OpenText( _
        Filename:=sFilePath, Origin:=xlMSDOS, StartRow:=1, _
        DataType:=xlDelimited, TextQualifier:=xlDoubleQuote, _
        ConsecutiveDelimiter:=False, Tab:=True, Semicolon:=False, _
        Comma:=False, Space:=False, Other:=False, _
        FieldInfo:=Array(Array(1, 1), Array(2, 1)), TrailingMinusNumbers:=True)
    Dim sws As Worksheet: Set sws = swb.Worksheets(1)
    Dim srg As Range: Set srg = sws.Range("A1").CurrentRegion
    
    ' Copy & Close.
    
    srg.Copy dCell
    swb.Close SaveChanges:=False
        
    ' Destination Finishing Touches
    
    dws.Columns("A").AutoFit
    'dws.Activate ' possibly not necessary
    dwb.Save
    
    Application.ScreenUpdating = True
    
    ' Inform.
    
    MsgBox "Data imported.", vbInformation
    
End Sub