CODEWITHSUNDEEP
excelms-accessvba
user2204315
user2204315

Reputation:

Close Excel file from Access vba

I have created an excel xls file from Access vba. 

Private Sub ModifyXl()

Dim XLapp As Excel.Application
Dim xlWB As Excel.Workbook
Set XLapp = New Excel.Application
Dim xlSh As Excel.Worksheet

Set xlWB = XLapp.Workbooks.Open(DskTp & "NHL Doctors.xls", , False)
Set xlSh = xlWB.Sheets("NHLDocs")

Cells.Select
Selection.Font.Name = "Trebuchet MS"
Rows("1:1").Select
Selection.Font.Bold = True
Range("A1").HorizontalAlignment = xlCenter
Columns("A:A").EntireColumn.AutoFit

xlWB.Save
xlWB.Close
XLapp.Quit
Set XLapp = Nothing

End Sub

'Cells.Select' didn't work but the file now exists. I can't delete it because it says it is already open - but it doesn't appear as open.

I have trawled the internet trying to find code that will close the file and quit excel - without success. Help!

Upvotes: 2

Views: 36537

Answers (1)

Dmitry Pavliv
Dmitry Pavliv

Reputation: 35853

Your code didn't work, because you haven't activated sheet (add xlSh.Activate).But it's not the best way of solving your problem. Try to avoid using Select/Active statements as in following code:

Private Sub ModifyXl()

    Dim XLapp As Excel.Application
    Dim xlWB As Excel.Workbook
    Set XLapp = New Excel.Application
    Dim xlSh As Excel.Worksheet
    'Dim DskTp as String

    'DskTp = "C:\"  
    Set xlWB = XLapp.Workbooks.Open(DskTp & "NHL Doctors.xls", , False)
    Set xlSh = xlWB.Sheets("NHLDocs")

    With xlSh
        .Cells.Font.Name = "Trebuchet MS"
        .Range("1:1").Font.Bold = True
        .Range("A1").HorizontalAlignment = xlCenter
        .Range("A:A").EntireColumn.AutoFit
    End With

    xlWB.Close True
    Set xlWB = Nothing
    XLapp.Quit
    Set XLapp = Nothing

End Sub

BTW, can't find where you initialized DskTp variable (is it a global variable?). I've added it's initialization as comments (in the case you're not using global variable - uncomment thouse lines).

Upvotes: 4

Related Questions