Page 1 of 3 123 LastLast
Results 1 to 15 of 34
  1. #1
    3 Star Lounger
    Join Date
    Aug 2006
    Location
    Gtr London, England
    Posts
    256
    Thanks
    2
    Thanked 0 Times in 0 Posts

    Run Time Error 1004 (VBA Excel 2003)

    Edited by HansV to replace Word doc with the screenshot it contained

    Hi,

    Ive written a macro that basically imports data into excel, sorts through it putting specific data for certain people onto separate worksheets, so they can see what work they have for the day.

    The macro works fine and well on my pc, but keeps bringing up the attached message, even though the scripts are exactly the same.

    Ive made sure that the worksheets are NOT being called the same name......As stated above, it works on mine fine but run the script on another pc and the message appears...... <img src=/S/brickwall.gif border=0 alt=brickwall width=25 height=15>

    Any ideas??

    Cheers.
    Lee

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Run Time Error 1004 (VBA Excel 2003)

    Try clicking the Debug button (on a PC where the error occurs), and hover the mouse over the variables in the code to see their values. Perhaps you can see what causes the conflict.

  3. #3
    Plutonium Lounger
    Join Date
    Nov 2001
    Posts
    10,550
    Thanks
    0
    Thanked 7 Times in 7 Posts

    Re: Run Time Error 1004 (VBA Excel 2003)

    I once saw a very similar error caused by trying to give a worksheet a name that was all numbers, with no alphabetic characters.

    StuartR

  4. #4
    3 Star Lounger
    Join Date
    Aug 2006
    Location
    Gtr London, England
    Posts
    256
    Thanks
    2
    Thanked 0 Times in 0 Posts

    Re: Run Time Error 1004 (VBA Excel 2003)

    Hi Hans,

    Thanks for the tip, I'll see what I can find.

    Cheers.
    Lee

  5. #5
    3 Star Lounger
    Join Date
    Aug 2006
    Location
    Gtr London, England
    Posts
    256
    Thanks
    2
    Thanked 0 Times in 0 Posts

    Re: Run Time Error 1004 (VBA Excel 2003)

    Hi,

    Ok thanks, I'll keep it in mind when Im checking through.

    Cheers.
    Lee

  6. #6
    3 Star Lounger
    Join Date
    Aug 2006
    Location
    Gtr London, England
    Posts
    256
    Thanks
    2
    Thanked 0 Times in 0 Posts

    Re: Run Time Error 1004 (VBA Excel 2003)

    Hi Hans,

    Im not able to work on the pc where the macro is causing problems until around lunchtime, however, looking at the macro working on my own pc Ive noticed that when it is run, it opens and executes in 'Book 1', which is fine, but another workbook, 'Book1.xls' is also opened.

    Is this meant to happen and could it have any bearing on the original problem reported?

    Cheers.
    Lee

  7. #7
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Run Time Error 1004 (VBA Excel 2003)

    Is 'Book1.xls' only opened when you run the macro that opens 'Book 1'? If so, there are several possibilities:
    - Something in your code opens 'Book1.xls'
    - 'Book 1' has code that opens 'Book1.xls'
    - Your code is stored in 'Book1.xls'.

  8. #8
    3 Star Lounger
    Join Date
    Aug 2006
    Location
    Gtr London, England
    Posts
    256
    Thanks
    2
    Thanked 0 Times in 0 Posts

    Re: Run Time Error 1004 (VBA Excel 2003)

    The code appears to be stored in book1.xls, Im a little unclear has to how 'book1' and 'book1.xls' came about but all seems to work fine on my pc.

    I suspect that this is the root of the reported problem and I guess it is likely one of the workbooks has a worksheet the same name thereby causing the conflict and error message.

    I'll let you know later once I can get to work on the pc in question.

    Cheers.
    Lee

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

    Re: Run Time Error 1004 (VBA Excel 2003)

    Since you did not post your code, I'm shooting in the dark, but another possibility (that has bitten me in the posterior in the past) occurs when you are running code from an Excel template (*.xlt file) upon it being opened, and referring to the workbook's .Name Property in the code.

    Code that runs from a template that has been opened but not yet saved will report its name without any extension (it doesn't know what it will be when it grows up). So if a user opens 'mytemplate.xlt' and Workbook_Open code runs, ThisWorkbook.Name returns "mytemplate1". As soon as the template is saved as a standard Excel worksheet, ThisWorkbook.Name then returns "whatever_the_user_or_code_named_me.xls" (or, of course, it reports the file extension matching the kind of file it has been saved as).

    Code adapted from the macro recorder is susceptible to this kind of name conflict error.
    -John ... I float in liquid gardens
    UTC -7ąDS

  10. #10
    3 Star Lounger
    Join Date
    Aug 2006
    Location
    Gtr London, England
    Posts
    256
    Thanks
    2
    Thanked 0 Times in 0 Posts

    Re: Run Time Error 1004 (VBA Excel 2003)

    Hi,

    1 of two 2 postings

    Thanks for that, it seems that you may well be along the right lines. I do admit though that I find the way excel seems to have a workbook in xlstart, in the office program files, all a bit confusing and I am sure that this is the root of the problem.

    Attached is a screen shot showing the beginning of the code and project editor window, which I hope can help you understand how things are set up.

    Lee

  11. #11
    3 Star Lounger
    Join Date
    Aug 2006
    Location
    Gtr London, England
    Posts
    256
    Thanks
    2
    Thanked 0 Times in 0 Posts

    Re: Run Time Error 1004 (VBA Excel 2003)

    2 postings of 2

    Further to my other postings, I have fiddled further in the path - 'C:Program FilesMicrosoft OfficeOfficeXLStart'. On my pc there is also 'C:Program FilesMicrosoft Office 2003OfficeXLStart' . In both these locations there are two workbooks as shown in the attached.

    When I now run the macro I get the message "C:Book1.xls '!importcases' cannot be found".

    Any suggestions will be very welcome.

    Cheers .
    Lee

  12. #12
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Run Time Error 1004 (VBA Excel 2003)

    I'm a bit suspicious about that space after Book1.xls, before the closing apostrophe. Is that a typo, or is it really present in the error message?

  13. #13
    3 Star Lounger
    Join Date
    Aug 2006
    Location
    Gtr London, England
    Posts
    256
    Thanks
    2
    Thanked 0 Times in 0 Posts

    Re: Run Time Error 1004 (VBA Excel 2003)

    No, its a typo on my behalf. There is no space in the actual error message when generated.

    Regards
    Lee

  14. #14
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Run Time Error 1004 (VBA Excel 2003)

    Do you, or did you, have a workbook Book1.xls in the root of C: ?

  15. #15
    3 Star Lounger
    Join Date
    Aug 2006
    Location
    Gtr London, England
    Posts
    256
    Thanks
    2
    Thanked 0 Times in 0 Posts

    Re: Run Time Error 1004 (VBA Excel 2003)

    No, I dont think I did have workbook Book1.xls in the root of Criginally.

Page 1 of 3 123 LastLast

Posting Permissions

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