Posts by vbmike73

    I have code that accesses a file that exists in SharePoint. I am referencing the path as a network path (ie. \\[email protected]\xxxxxx\sites\etc)


    On a fresh reboot of a system, a user will opening the workbook that runs the code will receive an access error, because the system thinks the path does not exist, or they don't have access to it. Even though it does exist and they do have access. Even if I do any kind of code to check if the file exists, it fails on that with the same type of error.


    When that error is received, if I manually go navigate to the path in another version of Excel or Windows Explorer, which works every time, and then I go back to continue running the code from the break point, it continues running with no issues. It's behaving as if I must go prove to Excel or my system that I can access that path, and then Excel will let the code run.


    Once I "prove myself worthy" the code will work every time, even if I close the workbook and re-open it. It will continue to work UNTIL I reboot my system, then it has to be "retrained".


    I really don't even know how to search for a solution on this because it really has nothing to do with DIR or really trying to test if the path exists.


    It's almost as if I need to do a full scan or something to test all accesses. Any I should point out, this is ONLY an issue with SharePoint paths. Never a normal network drive or anything else.


    Any ideas? Thanks!

    Ok, but I'm still not getting past the fact that I need to use GetOpenFilename to have the user select the files they want. There is no other way that I know of to ask a user to select files on their system. Given that, this action alone is what fails when one of the filenames is too long. I can't "build an array" of filenames if I don't know what files they want to choose, and the only way to get that information is to use GetOpenFilename.

    Thanks, but this looks like it will only loop through all files in a folder. I need the user to select a single file or multiple files, thus the need for GetOpenFilename. And that has to be a variant. Maybe I'm still missing something, but I don't see how this code applies to my situation.

    Trying to use the code below. If I select two files for example, and one of them is longer than 255 characters (including path), Excel will crash. It ignores my error handler and just crashes. I cannot figure out a way to get past this. Any ideas?


    Code
    Dim vSelectFiles As Variant
    On Error GoTo EndLabel
    vSelectFiles = Application.GetOpenFilename(MultiSelect:=True)
    Exit Sub
    EndLabel:
    MsgBox "Check file name lengths"

    Re: Code to select chart objects in Excel 2007 does not work correctly in Excel 2003


    Just wanted to post my own solution since I thought it was very odd.

    Apparently, Excel 2003 does not like the use of the # sign in a chart name when it comes to VBA. It works fine in Excel 2007, but when trying to select a chart with VBA in Excel 2003, it cannot do it with the # sign. I simply removed that, and all was good.

    No, it's not reverse psychology. I'm thinking it's just some bug in Excel, but maybe I'm wrong.


    I have 9 charts on a sheet in Excel 07. I gave each one a significant name. I have a macro that selects each chart and does something, using


    Code
    ActiveSheet.ChartObjects("Chart Name").Activate


    No issues there, it selects each one fine and does its thing.


    But I save it as a 2003 workbook, and try to run the same code with the file open in Excel 2003, and it doesn't recognize all charts as charts. It selects the first 3 charts fine using the code above, but then when it tries to select the 4th, it gives "Unable to get the ChartObjects property of the Worksheet class".


    However, in the immediate window, when I type Print ActiveSheet.ChartObjects.Count I get 9, which is correct.


    If I do the following code to try to select all charts, it only selects the first 3 charts:


    Code
    Dim myChart as ChartObject
    For Each myChart In Sheets("Sheet1").ChartObjects
    myChart.Activate
    Next myChart


    So even though I have renamed the charts in 07, and the same names show in 03, is there another place that it is keeping the original chart name possibly? Obviously, I'm very perplexed about this!