Link to home
Start Free TrialLog in
Avatar of durick
durickFlag for United States of America

asked on

vba coding not working with Office 365(Excel)

Running Access 2003 and creating an Excel spreadsheet using vba coding
My Access 2003 vba code works correctly in deleting columns in an Excel 2013 spreadsheet.
But when run on user pc running Office 365( I think it is Excel 2016) the code runs without errors, but fails to delete the proper columns.
her is my vba coding:
     Dim xls     As Excel.Application
     Dim wkb     As Excel.Workbook
     Dim wks, wks2    As Excel.Worksheet

     Set xls = New Excel.Application
     Set wkb = xls.Workbooks.Open(excelfilename)
     Set wks = wkb.Worksheets(1)
     Set wks2 = wkb.Worksheets(2)
        xls.Worksheets(1).Range("A:A,B:B,D:D,H:H,Q:Q,R:R,S:S").EntireColumn.Delete
    xls.Worksheets(1).Columns("A:L").EntireColumn.AutoFit

  xls.Worksheets(1).Name = "Excluded Holdings"
      xls.Worksheets(2).Range("A:A,B:B,H:H,P:P").EntireColumn.Delete
          xls.Worksheets(2).Columns("A:L").EntireColumn.AutoFit


  xls.Worksheets(2).Name = "Information Table"

     wkb.Close True

     Set wks = Nothing
     Set wkb = Nothing

     xls.Quit
          Set xls = New Excel.Application
     xls.Visible = True
     Set wkb = xls.Workbooks.Open(excelfilename)
varreturn = SysCmd(acSysCmdClearStatus)
Avatar of Norie
Norie

Have you tried making Excel visible and then stepping through the code to see what's actually happening?
Avatar of durick

ASKER

No I do not have Office 365
Oops, missed that part.

As far as I can see there's nothing wrong with the posted code, so to see if anything is going wrong the best way would be to make Excel visible and step through.

To make Excel visible is straightforward.
Set xls = New Excel.Application

xls.Visible = True

Open in new window

Ask the user to make that change and then to report back any errors.
ASKER CERTIFIED SOLUTION
Avatar of durick
durick
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial