durick
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(excelfi lename)
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").E ntireColum n.Delete
xls.Worksheets(1).Columns( "A:L").Ent ireColumn. AutoFit
xls.Worksheets(1).Name = "Excluded Holdings"
xls.Worksheets(2).Range("A :A,B:B,H:H ,P:P").Ent ireColumn. Delete
xls.Worksheets(2).Columns( "A:L").Ent ireColumn. 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(excelfi lename)
varreturn = SysCmd(acSysCmdClearStatus )
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(excelfi
Set wks = wkb.Worksheets(1)
Set wks2 = wkb.Worksheets(2)
xls.Worksheets(1).Range("A
xls.Worksheets(1).Columns(
xls.Worksheets(1).Name = "Excluded Holdings"
xls.Worksheets(2).Range("A
xls.Worksheets(2).Columns(
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(excelfi
varreturn = SysCmd(acSysCmdClearStatus
Have you tried making Excel visible and then stepping through the code to see what's actually happening?
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.
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
Ask the user to make that change and then to report back any errors.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.