Results 1 to 8 of 8
  1. #1
    3 Star Lounger
    Join Date
    Aug 2002
    Location
    milton keynes, Buckinghamshire
    Posts
    252
    Thanks
    0
    Thanked 0 Times in 0 Posts

    macro run time error (Excel 2000)

    We have developed a macro which:
    1. Opens other s/s's,
    2. Extracts the required info (which it holds temporarily in a 2 dimensional array variable),
    3. Closes the donor s/s and then
    4. Copies the info from the array into a predefined sheet of the current book.

    The macro runs successfully every time on the computer upon which it was developed, but when we try to run it from other machines it closes the donor s/s and then shows a run time error 1004. Are there any settings which need to be changed to overcome this problem?

    Thanks

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

    Re: macro run time error (Excel 2000)

    Its impossible to say without seeing the code and knowing whic line of code produces the error. Would it be possible to upload a copy of the workbook, changing any information that is confidential?
    Legare Coleman

  3. #3
    3 Star Lounger
    Join Date
    Aug 2002
    Location
    milton keynes, Buckinghamshire
    Posts
    252
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: macro run time error (Excel 2000)

    Legare

    Thank you for your reply. size constraints and confidentiality prevent me from supplying a copy of the file. However please find attached a copy of the relevant code. I have high-lighted the area where I think it fails in red.

    I have also highlighted another area in blue. This relates to an if statement based on values held in the 2 dimensional array. When I enable the if statement the program runs but no values are copied into the cells. It is as if the program considers none of the variables interrogated to be greater than 0 which is not the case.

    Any ideas?

  4. #4
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: macro run time error (Excel 2000)

    Post deleted by JohnBF
    -John ... I float in liquid gardens
    UTC -7ąDS

  5. #5
    3 Star Lounger
    Join Date
    Aug 2002
    Location
    milton keynes, Buckinghamshire
    Posts
    252
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: macro run time error (Excel 2000)

    OK - Sorry - Second attempt
    Attached Files Attached Files

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

    Re: macro run time error (Excel 2000)

    Is the procedure "WorkbookAfterClose" available to all systems using the code ?

    Andrew C

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

    Re: macro run time error (Excel 2000)

    Why did you hilite three lines in red and say that you "think" this is where the error is occuring? When the error occurs, you should be able to click on the Debug button and the line causing the error will be hilited for you. The only way I could see for the "ActiveWorkbook.Close" line to cause this error would be if there was no workbook open, which would mean that there would be no code to be running. The next line "Application.Run "WorkbookAfterClose"" might cause that error if it could not find the macro WorkbookAfterClose. Since I can not find a macro by that name in what you sent, and since there is no workbook name included to say where the macro is, that might be the problem.

    Since your TimeCopy array is DIMed as a variant, it is difficult to know what is in column 6 since I can't see the worksheet the values are copied from. Since I did find one place where you set elements in that column to null string values, then the column may contain strings and your IF statement is comparing to numeric zero. That may be your problem there.
    Legare Coleman

  8. #8
    3 Star Lounger
    Join Date
    Aug 2002
    Location
    milton keynes, Buckinghamshire
    Posts
    252
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: macro run time error (Excel 2000)

    Legare,

    I used the term "think" because the debug option was greyed out in the relevant macro error box, so I had to try to work out where the error was by which stage the application had reached. The help option box which was available only took me to a high level explanation of the term "run time error."

    As soon as I disabled the "Application.Run "WorkbookAfterClose"" line, the program worked fine. I must admit that I am a bit confused as to where this line originally came from and which program was being called. I cannot recall creating this and I think that it is something that I might have remembered. However disabling it appears to have done the trick. I then tried re-enabling the line and re-running the program on my machine. It worked fine, but I still could not trace the program being called.

    With regard to the IF statement, please find attached a copy of the relevant output sheet. Column F is the offending item currently compiled without the IF statement. As soon as the IF statement is enabled only the top title rows appear. I do not understand your comment regarding null string values. Does this refer to clearing the columns of previous run time data before entering the current data?

    Many thanks for all your help
    Attached Files Attached Files

Posting Permissions

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