Ms Access VBA to open & edit Excel

[Knicks]

I am using MS Access 2010.

I would like to create an Excel worksheet from an Access query and then afterwards have MS Access VBA open the worksheet and lock the first row of column headers and save. End users will ultimately send the file back with data for importing and I want to make sure they don't change the headers in the Excel file. I would like to use late binding to avoid having to use references, but if I have to early bind that is OK.

Thank you!

 

[SkipVought]

Hi,

Locking row 1 cells is not sufficient to prevent changes to the heading data. You must also PROTECT the sheet: password optional.

Open an Excel workbook and macro record opening some other workbook, locking row1 cells, protecting the sheet & saving the file. Copy your generated code & paste into Access VBA module. Set a reference for an Excel object library in Tools > References.

Post back with your recorded code if you need help.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[lameid]

In additon to just recording a Macro, you will have to open the excel file and use stuff off the Excel object. I usually use something like the below to open an Excel file. Most things can hang off of the application or worksheet object (worksheets are members of workbooks). Some things are easier to translate than others... SkipVought is by far the expert I have scene on these fora for Excel.

    Dim XLAPP As Object 'Excel.Application
    Dim XLWorkBook As Object 'Excel.Workbook

Set XLAPP = CreateObject("Excel.Application")
       With XLAPP
          '.Visible = True  'When you debug, sometimes it helps to see what is going on
          lngArrayPos = .Workbooks.Count
          .Workbooks.Open FileName:= _
               strFilePath
          Set XLWorkBook = .Workbooks(lngArrayPos + 1)
       End With