Results 1 to 6 of 6
  1. #1
    Join Date
    Jan 2002
    Location
    Bay Area
    Posts
    511

    Question Unanswered: Excel VBA Not Doing What it Should

    Has anyone had this type of problem where Excel VBA works perfectly on your own PC but when running on another PC (laptop) with the same version of Excel, code similar to the following refuses to obey?

    Application.Workbooks(workingWkBook).Activate

    where workingWkBook is a variable with the name of the workbook.

    The app. has two workbooks open and should move to one of them to do some work. I watched the other person in another state (via NetMeeting) run the application, we went into debug mode and, even though the workbook was open and the variable had the correct file name assigned, the code failed to activate the workbook. Changing "Activate" to "Select" made no difference.

    The laptop has the same Add-ins and error checking settings as my desktop PC. One of the differences between the laptop and my PC is that the laptop uses sofware that encrypts all files in "My Documents", which is where the application is stored.

    Jerry

  2. #2
    Join Date
    Feb 2004
    Posts
    533
    Quote Originally Posted by JerryDal
    Has anyone had this type of problem where Excel VBA works perfectly on your own PC but when running on another PC (laptop) with the same version of Excel, code similar to the following refuses to obey?
    I have had these type of problems. I would:

    #1 make sure the Macro Security is set to Allow Macros Medium or Low setting. 'Tools->Macro->Security'

    #2 you should not have to specify the "Application" object it is assumed since you are running the MS Excel App.

    #3 While in the Debug Mode Test the command in the immediate window. for example this typed in the immediate window ?Workbooks("nameofWorkbook.xls").name will return an error or the name of the workbook if it is open in Excel and the command works.

    #4 Use the workbook name with the same case as the file name. may be case sensitive.

    #5 Dim the variable as a "String" Data type: Dim workingWkBook as String

    #6 re-install excel or MS Office profile, if all else fails to make sure VB components are installed.
    ~

    Bill

  3. #3
    Join Date
    Jan 2002
    Location
    Bay Area
    Posts
    511
    The failure of VBA code running on a different computer, a laptop, has been solved. I can not explain this, but my PC Excel application can recognise a file without including ".xls", while the same application on the laptop could not.
    For example, the following code would fail on the laptop if the variable workingWkBook was assigned the value "c:\Temp\Customers"
    Application.Workbooks(workingWkBook).Activate
    but it would work if the value was "c:\Temp\Customers.xls"

    Jerry

  4. #4
    Join Date
    Mar 2006
    Posts
    163
    Jerry

    I think this problem has something to do with Windows settings rather than Excel.

    It might also be something to do with whether or not the workbook has been saved.

    Anyways you seem to have it solved, but I would like to point out it's generally not actually needed to activate/select workbooks/worksheets/ranges etc to work with them.

    PS Just reread your last post, you shouldn't be using the full path.

  5. #5
    Join Date
    Jan 2002
    Location
    Bay Area
    Posts
    511
    Norie, I made an error when I included the path in my post. The VBA code does not include the full path when a workbook or worksheet is activated.

    I don't understand your comment about it generally not being necessary to activate a workbook or worksheet to do some work, however maybe my application falls outside of the general rule. When the application moves between workbooks and worksheets and does copying, pasting and formatting, and creating new information with formulas, it is critical that the correct workbook and worksheet is activated, where changes are made to produce the final reports.

    Thank you for your reply.
    Jerry

  6. #6
    Join Date
    Mar 2006
    Posts
    163
    Jerry

    My comment means basically what it says - you don't normally need to activate/select workbooks/worksheets/ranges etc especially not to do something like copying and pasting between workbooks.

    What you do need to do however is make sure you reference things correctly.

    Now I don't know where your code is running but you can use code like this in Excel.
    Code:
    Set wbThis = ThisWorkboook ' create reference to workbook the code is in
    Code:
    Set wbAct  = ActiveWorkbook ' create reference to the active workbook
    Code:
    Set wbOpen = Workbooks.Open("C:\MyPath\MyBook.xls") ' open workbook and create a reference to it
    Code:
        Set wbNew = Workbooks.Add ' create new workbook and a reference to it
    All these references can then be used in subsequent code, even to activate the workbooks if you do find out that's essential.

    Perhaps if you posted more code I could illustrate what I mean further.

Posting Permissions

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