Results 1 to 9 of 9
  1. #1
    Join Date
    Sep 2006
    Location
    Surrey, UK
    Posts
    994
    Provided Answers: 2

    Post Unanswered: Run-time error 462 when controlling Excel from Access

    Hi

    I've been wondering about posting this problem here for a while, not least because I wasn't sure whether it would be better in this forum or the Excel one! On the grounds that the code sits in a .mdb file, I thought that this was probably the best place.

    I have a process in which a .csv file gets imported into an Access database, the contents are normalised, processed, denormalised and then exported to a .xls file. This output file then needs to be amended so that it can be uploaded into an Essbase OLAP cube, and the results of the upload tested.

    During the export process, five queries are exported to the one .xls file. During the amendment process, the file is then opened and various aspects of the exported sheets are amended (names, values in cells, new lines added, etc). Finally, a new page is added to the end of the workbook and populated with various formats, values and formulae in order to reconcile what was sent to the OLAP cube with what the cube actually received.

    Without exception, every time I run the export process, it stalls during the "With" statement that amends the exported sheets. An error message is displayed in Access at this point:

    "Run-time error '462'
    The remote server machine does not exist or is unavailable"


    The line that prompts this error is "Selection.EntireRow.Insert". Each time, I stop the sub in its tracks, close and delete the Excel file and start again. The second time around, it usually works like a dream!

    As it's only a monthly process, I'm not too worried about having to start it twice. However, I would like to know what causes it and if anything can be done to prevent it. This will become more important if the department in question go back to weekly forecasting rather than monthly!

    In case it makes a difference, I'm using Office 2000 on WinXP.

  2. #2
    Join Date
    Apr 2004
    Location
    Derbyshire, UK
    Posts
    789
    Provided Answers: 1
    Quote Originally Posted by weejas
    Hi

    I've been wondering about posting this problem here for a while, not least because I wasn't sure whether it would be better in this forum or the Excel one! On the grounds that the code sits in a .mdb file, I thought that this was probably the best place.

    I have a process in which a .csv file gets imported into an Access database, the contents are normalised, processed, denormalised and then exported to a .xls file. This output file then needs to be amended so that it can be uploaded into an Essbase OLAP cube, and the results of the upload tested.

    During the export process, five queries are exported to the one .xls file. During the amendment process, the file is then opened and various aspects of the exported sheets are amended (names, values in cells, new lines added, etc). Finally, a new page is added to the end of the workbook and populated with various formats, values and formulae in order to reconcile what was sent to the OLAP cube with what the cube actually received.

    Without exception, every time I run the export process, it stalls during the "With" statement that amends the exported sheets. An error message is displayed in Access at this point:

    "Run-time error '462'
    The remote server machine does not exist or is unavailable"


    The line that prompts this error is "Selection.EntireRow.Insert". Each time, I stop the sub in its tracks, close and delete the Excel file and start again. The second time around, it usually works like a dream!

    As it's only a monthly process, I'm not too worried about having to start it twice. However, I would like to know what causes it and if anything can be done to prevent it. This will become more important if the department in question go back to weekly forecasting rather than monthly!

    In case it makes a difference, I'm using Office 2000 on WinXP.
    Hi

    Without seeing a little more code or knowing what the "With" referes to, my best guess is to try qualifying the statment with the Excell Application object ie

    Code:
    xlApp.Selection.EntireRow.Insert
    
    or perhaps
    
    With xlApp
        .Selection.EntireRow.Insert
        
        
    End With
    Just a thought.

    [I assume you have an Excel App object!]

    ??

    MTB

  3. #3
    Join Date
    Sep 2006
    Location
    Surrey, UK
    Posts
    994
    Provided Answers: 2
    I knew I'd forgotten something...
    The code for the With statement is:
    Code:
        Set objExcel = CreateObject("Excel.Application")
        objExcel.Visible = True
        Set objBook = objExcel.Application.Workbooks
        objBook.Open Filename:=strFile
        Set objThis = objExcel.Application.ActiveWorkbook
        
        '2008-Apr-23 - rename the sheets
        
        For Each objSheet In objThis.Worksheets
    
            objSheet.Name = Right(objSheet.Name, Len(objSheet.Name) - 5)
    
        Next objSheet
        
        strSht = "Whey_fcst_" & ForecastBits(strForecast, True) & "_" & ForecastBits(strForecast, False) & "_" & Left(strFYear, 2)
        objThis.Worksheets("Whey_fcst").Name = strSht
        
        '8.   Add the required extra data to the worksheets
        '2008-May-12: Pick up the date range variables
        strDates = DateRanges(strFYear, bytPeriod)
        
        'Work through each of the sheets, adding a row and removing or adding values as required
        With objThis
        
            .Worksheets("Cases").Select
            Selection.EntireRow.Insert
            .Worksheets("Cases").Range("A2").ClearContents
            .Worksheets("Cases").Range("B1") = "Forecast " & strForecast
            .Worksheets("Cases").Range("C1") = "all.flav.type"
            .Worksheets("Cases").Range("D1") = "Casbase"
            .Worksheets("Cases").Range("E1") = "Macc.mr"
            
            .Worksheets("Pricebible").Select
            Selection.EntireRow.Insert
            .Worksheets("Pricebible").Range("A2").ClearContents
            .Worksheets("Pricebible").Range("B1") = "Forecast " & strForecast
            .Worksheets("Pricebible").Range("C1") = "all.flav.type"
            .Worksheets("Pricebible").Range("D1") = "pricebible"
            .Worksheets("Pricebible").Range("E1") = "Macc.mr"
        
            .Worksheets("Tonnes").Select
            Selection.EntireRow.Insert
            .Worksheets("Tonnes").Range("A2").ClearContents
            .Worksheets("Tonnes").Range("B1") = "Forecast " & strForecast
            .Worksheets("Tonnes").Range("C1") = "all.flav.type"
            .Worksheets("Tonnes").Range("D1") = "Tonbase"
            .Worksheets("Tonnes").Range("E1") = "Macc.mr"
            
        End With
    Does this help?

  4. #4
    Join Date
    Apr 2004
    Location
    Derbyshire, UK
    Posts
    789
    Provided Answers: 1
    Hi

    Well, yes it does help.

    As previously indicated I would put a period in front of Selection.EntireRow.Insert
    and see if that fixes it before going any further.

    From the code I assume you are late binding (but not necessarily), this will definitly produce an error.


    MTB

  5. #5
    Join Date
    Sep 2006
    Location
    Surrey, UK
    Posts
    994
    Provided Answers: 2
    From memory, adding a period in front of Selection.EntireRow.Insert simply produces a different error.

    I thought that I was using early binding, but the book's examples imply that it is late binding. By extension, I should replace
    Code:
    Set objExcel = CreateObject("Excel.Application")
    with
    Code:
    Set objExcel = New Excel.Application
    Think that's worth a try?

    ***EDIT***
    Apparently not. Fell over the in same place. I tried adding the uncertain period, and got a new error:
    Run-time error '438':
    Object doesn't support this property or method


    Which is just nuts, as tapping that line into the Immediate window in Excel worked fine!
    Last edited by weejas; 12-10-08 at 11:22.

  6. #6
    Join Date
    Apr 2004
    Location
    Derbyshire, UK
    Posts
    789
    Provided Answers: 1
    Hi

    A very quick though before I leave for the day, is that you have not selected anything in the sheet and, therefore, it doese not have a Row postion to insert at ie this is like trying this

    Sheets("Sheet1").EntireRow.Insert

    whch gives the same error you indicated.

    Try this and see what happens




    Code:
    .Worksheets("Cases").Select
            Selection.EntireRow.Insert

  7. #7
    Join Date
    Apr 2004
    Location
    Derbyshire, UK
    Posts
    789
    Provided Answers: 1
    Hi

    A very quick though before I leave for the day, is that you have not selected anything in the sheet and, therefore, it doese not have a Row postion to insert at ie this is like trying this

    Sheets("Sheet1").EntireRow.Insert

    whch gives the same error you indicated.

    Try this and see what happens




    Code:
        .Worksheets("Cases").Select
        .Range("A1").Select
        .Selection.EntireRow.Insert
    ??

    MTB

    ps sorry about previous post, pressed the wrong button in my haste!

  8. #8
    Join Date
    Sep 2006
    Location
    Surrey, UK
    Posts
    994
    Provided Answers: 2
    I wouldn't have thought that that would make any difference - A1 is the default selection for a new worksheet, and that's what these are.

    However, Access being what it is, I'll give that a try and see what happens. Thanks for your time!

    ***EDIT***
    This didn't work, but it did point me in the right direction!
    The fix is:
    Code:
        .Worksheets("Cases").Select
        .Worksheets("Cases").Range("A1").Select
        Selection.EntireRow.Insert
    For some reason, you have to explicitly state the sheet in each line. I just tested it twice, and it worked like a charm. Many thanks for your help!
    Last edited by weejas; 12-11-08 at 12:58.

  9. #9
    Join Date
    Apr 2004
    Location
    Derbyshire, UK
    Posts
    789
    Provided Answers: 1
    Quote Originally Posted by weejas
    This didn't work, but it did point me in the right direction!
    The fix is:
    Code:
        .Worksheets("Cases").Select
        .Worksheets("Cases").Range("A1").Select
        Selection.EntireRow.Insert
    For some reason, you have to explicitly state the sheet in each line. I just tested it twice, and it worked like a charm. Many thanks for your help!
    I obviously hadn't tried it, but we were heading in the right direction!

    Glad you resolved, and thanks for letting us know


    MTB

Posting Permissions

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