VBA Problem



  • I hesitate to ask VBA questions because they are usually so customized to the business people are in.

    We have a major Excel/VBA project that I had to update code in to point to our new file server when we migrated a few weeks ago. The spreadsheet attaches a XLA file to a project each time it is opened. So, the info in the XLA file is there and updated.

    Somewhere along the line, it updates the Excel Object ThisWorkbook. All of the older spreadsheets (pre new file server) open with the updated XLA file but old server info in the ThisWorkbook object. So that one part of the older project refers to the old server and beyond changing every old job which would be an astronomical task, I tried searching that XLA file to see when it may put that info in there and can't seem to find it.

    The work around was to turn back on the share on the old server that references in the ThisWorkbook object could be accessed and reached.

    So it is kind of weird...and without someone seeing this entire code, it may be hard to figure this one out...



  • @garak0410 10 years ago I was doing Excel VBA stuff for a living, but I no longer recall a lot of it unless I look at the code itself.



  • How far away from turning off the old server are you? Once you turn it off (or can rename it) you can put in a CNAME in your DNS pointing to your new server to solve this problem.



  • @Dashrender said:

    How far away from turning off the old server are you? Once you turn it off (or can rename it) you can put in a CNAME in your DNS pointing to your new server to solve this problem.

    This was my plan "B" for sure...I guess I can start working on the remainder of the DC migration...



  • I've never written VBA. Did some VB6 in the 90s but that was the last time that I saw BASIC.



  • Man, I can completely identify with the problem you're having. We have an Estimating tool that is much the same. If you change the code in the master template, it's near impossible to propagate that change to every other spreadsheet that already exists. There may have been columns added, etc., and that can make things even more complicated.



  • @NetworkNerd said:

    Man, I can completely identify with the problem you're having. We have an Estimating tool that is much the same. If you change the code in the master template, it's near impossible to propagate that change to every other spreadsheet that already exists. There may have been columns added, etc., and that can make things even more complicated.

    I agree when I finally demote that old server and use a CNAME, the problem will be resolved. The new file server has an alias so it will make the next migration much easier.

    It still doesn't change the fact that our backbone to our panels, purlins and roofs are all determined by an extremely messy and complicated VBA project. For someone who isn't a developer and just doesn't have the time to study (believe me, I've tried), it will be a herculean effort to convert this to another format one day. I've been able to enhance it here and there but not by much.



  • What's holding up the decomm at this point? Are there still services relying on the old box?



  • @scottalanmiller said:

    What's holding up the decomm at this point? Are there still services relying on the old box?

    Just crazy busy with SOLO IT guy stuff...


Log in to reply