Manipulating exported spreadsheet from Access

[Malcy]

Hi
I have an application that generates Excel spreadsheets to be emailed to outside parties so they can enter pricing information and return it. The application then picks up the spreadsheets and imports the data back into Access. This is done since the recipients will almost certainly not have Access on their systems but are also very familiar with Excel.
In order to safeguard some of my reference fields I wish to manipulate the spreadsheet by formatting some cells and locking down others.
My code (part of a bigger sequence) is currently

' Open up the spreadsheet
    Set xlapp = CreateObject("Excel.Application")
    xlapp.Application.Visible = False
    xlapp.Workbooks.Open strPath
    
    Set xlwkb = xlapp.Application.ActiveWorkbook

' Ensure all relevant cells are unlocked
'    ActiveSheet.Columns("A:G").Locked = False

' Set overall font to Arial, 8pt
    xlapp.Cells.Select
    xlapp.Selection.Font.Name = "Arial"
    xlapp.Selection.Font.Size = "8"

' Set the background to yellow for cells G1:G500
'   ActiveSheet.Columns("G").Interior.ColorIndex = 44

' Now put row headings to bold
    xlapp.Application.Rows("1:1").Select
    With xlapp.Application.Selection.Font
        .Bold = True
    End With

' Now autofit columns
    xlapp.Cells.Select
    xlapp.Selection.Columns.AutoFit
    xlapp.Application.Rows("1:1").Select

' Rename the worksheet to strMonth
    Set oSheet = xlwkb.Sheets("tblExportSup")
    oSheet.Name = strMonth

' Ensure all relevant cells are unlocked
    ActiveSheet.Columns("A:G").Locked = False

' Lock down cells in columns A to F
    ActiveSheet.Columns("A:F").Locked = True
    
' Allow editing cells in column G
    ActiveSheet.Columns("G").Locked = False
    
' Protect the worksheet
    ActiveSheet.Protect Password:="Gordon"
    
' Save and close the spreadsheets
    xlapp.ActiveWorkbook.Save
    xlapp.ActiveWorkbook.Close

It keeps seeming to fail at the locking columns part so I added a line to unlock all the columns first in case that was the problem, but it doesn't seem to have sorted. Can anyone advise where I am going wrong?
Also when it falls over the spreadsheet is left open for editing so I cannot then delete it to try again. Any ideas how to trap error so that if it fails it saves and closes what it has done?
Have not done much work with Excel VBA from Access so have cobbled much of it together from other examples (which may be where my problems creep in). Hopefully the comments show what I am trying to do - my attempt to put a yellow background failed and so are commented out.
Thanks for any help on this one. It would be much appreciated.
Best wishes

Malcy

 

[RoyVidar]

You have several unqualified references to Excel objects, properties and methods, which will create an extra instance of Excel in memory, and perhaps also the other things of which you speak.

First, since you do have a separate workbook object, use

set xlwkb = xlapp.Workbooks.Open(strPath)

in stead of

xlapp.Workbooks.Open strPath

Set xlwkb = xlapp.Application.ActiveWorkbook

Never, ever use the unqualified
ActiveSheet....

Always either qualify it against parent object

xlwkb.ActiveSheet...

or declare and instantiate an extra sheet object (as you do later), and

oSheet.Columns...

Try some of these alterations, and see if they get you any further (be sure to kill the extra instance of Excel through Task Manager before running any code)

 

[Malcy]

Wow Roy
You da man! Thanks!
Worked a treat and I can even understand why just couldn't see it/didn't know it before.
For anyone's future reference I have put the revised code in below.

PS You don't know how to fix the yellow background to column G do you, wizard that you are!?!

Revised code

' Open up the spreadsheet
    Set xlapp = CreateObject("Excel.Application")
    xlapp.Application.Visible = False
    Set xlwkb = xlapp.Workbooks.Open(strPath)
    
' Set overall font to Arial, 8pt
    xlapp.Cells.Select
    xlapp.Selection.Font.Name = "Arial"
    xlapp.Selection.Font.Size = "8"

' Set the background to yellow for cells G1:G500
'   ActiveSheet.Columns("G").Interior.ColorIndex = 44

' Now put row headings to bold
    xlapp.Application.Rows("1:1").Select
    With xlapp.Application.Selection.Font
        .Bold = True
    End With

' Now autofit columns
    xlapp.Cells.Select
    xlapp.Selection.Columns.AutoFit
    xlapp.Application.Rows("1:1").Select

' Rename the worksheet to strMonth
    Set oSheet = xlwkb.Sheets("tblExportSup")
    oSheet.Name = strMonth

' Ensure all relevant cells are unlocked
    oSheet.Columns("A:G").Locked = False

' Lock down cells in columns A to F
    oSheet.Columns("A:F").Locked = True
    
' Allow editing cells in column G
    oSheet.Columns("G").Locked = False
    
' Protect the worksheet
    oSheet.Protect Password:="Gordon"
    
' Save and close the spreadsheets
    xlapp.ActiveWorkbook.Save
    xlapp.ActiveWorkbook.Close

 

[Malcy]

Hi again
Sorted the yellow myself by moving the code down the sequence and then changing to .Sheet.Column
Only trouble is 44 is a horrid muddy orangy yellow not the nice pale one I wanted but I am sure I can now sort this
Thanks again for the help!

 

[RoyVidar]

Good to see you've sorted it. I'm a fan of

oSheet.Columns("G").Interior.Color = vbYellow

or just play with the macrorecorder in Excel, and check out the codes of the colours you prefer.