Closing 2nd WorkBook

brgipple · Feb 20th 2006

I start my code in 1 workbook, this code will open a file in another workbook so i can copy information to my main workbook, i would like to close the 2nd workbook once i'm done with it.

Code

'Load the CSV file from the selected path..
    Workbooks.Open Filename:=CSVfile, Origin:=xlWindows
    If IsEmpty(Range("A2")) Or IsEmpty(Range("AG2")) Then
            Message = "The CSV was not a FULL CSV file."
            Message = Message & "Plaese use the ADHOC Query to obtain a full file"
            MsgBox Message, vbOKOnly, "Problem with CSV file!"
            Quiting = True
            Exit Sub
    End If
    Cells.Select
    Selection.Copy
    Set Sourcebook = ActiveWorkbook
    Workbooks("Calculate_XP.xls").Activate
    Worksheets("Import Sheet").Select
    Range("A1").Select
    ActiveCell.PasteSpecial
    Cells.Select
    With Selection.Font
        .Name = "Comic Sans MS"
        .Size = 8
    End With
    Columns("A:AZ").AutoFit
    With Selection
        .HorizontalAlignment = xlCenter
        .VerticalAlignment = xlBottom
    End With
    'Workbooks(Sourcebook).Activate
    'ActiveWindow.Close

Display More

i can't seem to get the last 2 lines to work. i have set 'sourcebook' to be a workbook at the beginning of the macro.

any thought would help

Thanks Brian G.

Dave Hawley · Feb 20th 2006

Re: Closing 2nd WorkBook

Hi Brian

If I understand you correctly the code below should suffice

Code

Dim wbCSV As Workbook


 'Load the CSV file from the selected path..
Set wbCSV = Workbooks.Open(Filename:=CSVfile, Origin:=xlWindows)


    With wbCSV.Worksheets(1)
    
        If IsEmpty(.Range("A2")) Or IsEmpty(.Range("AG2")) Then
            Message = "The CSV was not a FULL CSV file."
            Message = Message & "Please use the ADHOC Query to obtain a full file"
            MsgBox Message, vbOKOnly, "Problem with CSV file!"
            Quiting = True
            wbCSV.Close SaveChanges:=False
            Exit Sub
        End If


             With .Cells
                  .Copy Destination:=ThisWorkbook.Sheets("Import Sheet").Range("A1")
                  .Font.Name = "Comic Sans MS"
                  .Font.Size = 8
                  .Columns("A:AZ").AutoFit
                  .Columns("A:AZ").HorizontalAlignment = xlCenter
                  .Columns("A:AZ").VerticalAlignment = xlBottom
            End With
            
    End With
    
    wbCSV.Close SaveChanges:=True

Display More

Closing 2nd WorkBook

Participate now!

Share