changing cell contents from access (Excel 97/SR2)

Topic

New Reply

If you just require to update one value, treat the merged cell(s) as a single cell with address. i.e. E3. I’m not sure if the Access coonnection is relevant, but try the above and see if it helps.

Andrew C

Reply | Quote

Replies

This question is related to a previous thread.

I am looking for process to open multiple files in mulitple directories and print one worksheet from each of the files. I have written the following module to open the excel files, update a date field, then print the appropriate worksheet. The module then closes the file and moves on to the next file. I have attached the code I am using, but am having trouble updating range (e3:h3). This is a merged cell that should contain a date in the mm/dd/yy format. The code below will open the workbook, navigate to the correct worksheet, then printout the worksheet, but NOT update the range (e3:h3). What am I doing wrong?

BTW, don’t know if it makes any difference, but this code is being ran from within Access 97. If this belongs in the Access forum, let me know and I will repost.

Sub P22()
    Dim XLApp As New Excel.Application, strMsg As String
    'strMsg = "What date do you want to use for the Updates page?" & vbCrLf & _
    '    vbCrLf & "(Must be a Sunday," & vbCrLf & "in the following format 'MM/DD/YY')"
    'mstrDate = InputBox(strMsg, "Input Date")

    'Start printing the Update sheet for the Production Assistants
      XLApp.Workbooks.Open _
        "Srvnt01SprodmgrShendersFilesProd_AstAttendance2001SUP#022.XLS"
      XLApp.Range("E3:H3").Select
      XLApp.ActiveCell.FormulaR1C1 = "07/01/01" 'mstrDate
      XLApp.Range("E4").Select
      'XLApp.Workbooks.Application.ActivePrinter = "SRVNT01PRT_SCLR on Ne01:"
      XLApp.Sheets("Update").PrintOut
      XLApp.ActiveWorkbook.Saved = True
      XLApp.ActiveWorkbook.Close
    XLApp.Quit
    Set XLApp = Nothing
End Sub

At first I thought I could use an input box to choose the new value for range (e3:h3), but that didn’t work, so I dimmed those statements out and went with a hardcode date. That didn’t work either

Reply | Quote

There are several issues with what you are doing.

1- It is much faster to just assign the value to the range without selecting it first if you don’t have some other reason for wanting it selected.

2- Your code says that you are assigning a formula to the range, but what you assign is not a formula (there is no equal sign). Excel will probably interpret the date as a formula and assing the value of 7 to the cell (7 divided by 1 divided by 1) which would be interpreted as the seventh day of 1900 if formatted as a date.

3- If the cells in the range E3:H3 are merged, then you should only need to assign the value to the first cell in the range.

I think that what you want to do is:

    XLApp.Range("E3").Value = "07/01/01"

Reply | Quote

Thanks for your help.
I changed my code as per your recommendations and tested the macro. Same results as before. Then like a bolt of lightening, I was struck with an inspiration. The workbook that I am opening contains several worksheets. When opening the workbook, the “Updates” sheet is not always the active sheet. Therefore, the value that I was trying to place into cell E3 was getting placed on someother worksheet. (Where is the ‘ID 10 T’ smiley when you need it).

I replaced this line of code:

     XLApp.Range("E3").Value = "07/01/01"

with this one:

      XLApp.Sheets("Update").Range("E3").Value = "07/01/01"

and everything works great now. It even worked with putting the input box statement back in.

Reply | Quote