CODEWITHSUNDEEP
vbaexcel
yaeer
yaeer

Reputation: 5

Close temporary workbook

I picked up this code from the web. It merges several Excel files to single file (each in separate sheets).

The files the DATA is imported from don't automatically close. This means that I need to manually close 8-10 files and "Do not Save" them, and that takes lots of time. What is the missing code?

Option Explicit
Sub CombineExcelFiles()
    Dim FilesToOpen
    Dim x As Integer
    Dim wkbAll As Workbook
    Dim wkbTemp As Workbook
    Dim sDelimiter As String

On Error GoTo ErrHandler
    Application.ScreenUpdating = False

sDelimiter = "|"

FilesToOpen = Application.GetOpenFilename _
  (FileFilter:="Excel Files (*.*xl*), *.*xl*", _
  MultiSelect:=True, Title:="Excel Files to Open")

If TypeName(FilesToOpen) = "Boolean" Then
    MsgBox "No Files were selected"
    GoTo ExitHandler
End If

x = 1
Set wkbTemp = Workbooks.Open(Filename:=FilesToOpen(x))
wkbTemp.Sheets(1).Copy
Set wkbAll = ActiveWorkbook
wkbTemp.Close (False)
wkbAll.Worksheets(x).Columns("A:A").TextToColumns _
  Destination:=Range("A1"), DataType:=xlDelimited, _
  TextQualifier:=xlDoubleQuote, _
  ConsecutiveDelimiter:=False, _
  Tab:=False, Semicolon:=False, _
  Comma:=False, Space:=False, _
  Other:=True, OtherChar:="|"
x = x + 1

While x <= UBound(FilesToOpen)
    Set wkbTemp = Workbooks.Open(Filename:=FilesToOpen(x))
    With wkbAll
        wkbTemp.Sheets(1).Move After:=.Sheets(.Sheets.Count)
        .Worksheets(x).Columns("A:A").TextToColumns _
          Destination:=Range("A1"), DataType:=xlDelimited, _
          TextQualifier:=xlDoubleQuote, _
          ConsecutiveDelimiter:=False, _
          Tab:=False, Semicolon:=False, _
          Comma:=False, Space:=False, _
          Other:=True, OtherChar:=sDelimiter
    End With
    x = x + 1
Wend

ExitHandler:
Application.ScreenUpdating = True
Set wkbAll = Nothing
Set wkbTemp = Nothing
Exit Sub

ErrHandler:
MsgBox Err.Description
Resume ExitHandler
End Sub

Upvotes: 0

Views: 1179

Answers (1)

Pᴇʜ
Pᴇʜ

Reputation: 57683

wkbTemp.Close False should close the workbook without saving it.

But another question is why do you process the first file outside the While loop? I see no reason for this. Therefore we can shorten that code to:

Option Explicit
Public Sub CombineExcelFiles()
    Dim FilesToOpen
    Dim x As Integer
    Dim wkbAll As Workbook
    Dim wkbTemp As Workbook
    Dim sDelimiter As String

    On Error GoTo ErrHandler
    Application.ScreenUpdating = False

    sDelimiter = "|"

    FilesToOpen = Application.GetOpenFilename _
      (FileFilter:="Excel Files (*.*xl*), *.*xl*", _
      MultiSelect:=True, Title:="Excel Files to Open")

    If TypeName(FilesToOpen) = "Boolean" Then
        MsgBox "No Files were selected"
        GoTo ExitHandler
    End If

    x = 1
    Set wkbAll = ActiveWorkbook

    While x <= UBound(FilesToOpen)
        Set wkbTemp = Workbooks.Open(Filename:=FilesToOpen(x))
        With wkbAll
            wkbTemp.Sheets(1).Move After:=.Sheets(.Sheets.Count)
            wkbTemp.Close False
            .Worksheets(x).Columns("A:A").TextToColumns _
              Destination:=Range("A1"), DataType:=xlDelimited, _
              TextQualifier:=xlDoubleQuote, _
              ConsecutiveDelimiter:=False, _
              Tab:=False, Semicolon:=False, _
              Comma:=False, Space:=False, _
              Other:=True, OtherChar:=sDelimiter
        End With
        x = x + 1
    Wend

ExitHandler:
    Application.ScreenUpdating = True
    Set wkbAll = Nothing
    Set wkbTemp = Nothing
    Exit Sub

ErrHandler:
    MsgBox Err.Description
    Resume ExitHandler
End Sub

Some Important Notes

  • I recommend to avoid ActiveWorkbook if possible because it is not a defined workbook but any workbook that is active at just this moment. Also note that there is a difference between ActiveWorkbook and ThisWorkbook (which is a defined workbook. It is the workbook the code runs at this point).*

  • Another thing is that .Worksheets(x) can be, but not necessarily must be the actually moved worksheet. I would say because you move the new sheet after the last sheet you also need to access the last sheet here: .Worksheets(.Worksheets.Count).

  • Also there is a difference between using Sheets and Worksheets. The Sheets collection contains worksheets but also charts etc, but the Worksheets collection only contains worksheets. Therefore you should decide which one is correct, I recommend always to use Worksheets unless you really need Sheets.

  • And I see no need for setting the variables to nothing.

    Set wkbAll = Nothing
Set wkbTemp = Nothing

    If I'm not totally wrong then Excel does this automatically when the procedure ends.

Upvotes: 1

Related Questions