Results 1 to 4 of 4
  1. #1
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Dublin, Ireland, Republic of
    Posts
    2,697
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: changing cell contents from access (Excel 97/SR2)

    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

  2. #2
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: changing cell contents from access (Excel 97/SR2)

    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:

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

    Legare Coleman

  3. #3
    2 Star Lounger
    Join Date
    May 2001
    Location
    Omaha, NE USA, Nebraska, USA
    Posts
    119
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: changing cell contents from access (Excel 97/SR2)

    Thanks for your help. <img src=/S/salute.gif border=0 alt=salute width=15 height=20>
    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:
    <pre> XLApp.Range("E3").Value = "07/01/01"
    </pre>

    with this one:
    <pre> XLApp.Sheets("Update").Range("E3").Value = "07/01/01"
    </pre>


    and everything works great now. It even worked with putting the input box statement back in. <img src=/S/groovin.gif border=0 alt=groovin width=21 height=21>

  4. #4
    2 Star Lounger
    Join Date
    May 2001
    Location
    Omaha, NE USA, Nebraska, USA
    Posts
    119
    Thanks
    0
    Thanked 0 Times in 0 Posts

    changing cell contents from access (Excel 97/SR2)

    This question is related to a previous <A target="_blank" HREF=http://www.wopr.com/cgi-bin/w3t/showflat.pl?Cat=&Board=xl&Number=52012&part=1> thread</A>.

    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, <font color=blue>update a date field</font color=blue>, 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.

    <pre>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_AstAttendance200 1SUP#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
    </pre>


    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 <img src=/S/help.gif border=0 alt=help width=23 height=15>

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •