Posts by darthrader

    Re: Application.filesearch not supported Excel 2010


    Thank you very much.

    If I understand your code correctly, the dir() function searches the files based on my partial string and then returns the filename that matches.

    It then uses the method to open the text file using the full name returned by DIR(). I did not know could open a text file. Shows how much of a hack I am. I have not looked at this code in 2 years and I am just figuring out what I coded years ago.

    I can experiment with both suggestions this weekend.



    I wrote VBA code for an Excel program 3-4 years ago that is very complicated. I am not a true programmer and hacked my way through. It took many months. My company just upgraded to Excel 2010 and the code will not run. I have tracked it down to the Application.filesearch object that is no longer supported. Basically I had a sub routine that opened up a text file based on a Partial file name. The code is as follows.

    With Application.FileSearch
    .LookIn = pfa
    .SearchSubFolders = False
    .Filename = lasersn + "-" + download_date + "-"
    .MatchAllWordForms = False
    .FileType = msoFileTypeAllFiles

    As you can see the .Filename Method has a partial file name based on a string built from a serial number and date. It searched in a specific folder based on the variable pfa

    Can I do the same with the fso object.
    I can't seem to get clarification on the .FileExists Method to see is this works with a partial name. If not I may be able to open a file with the fso object based on a certain date.

    Any suggestions? Does the .FileExists method work with a partial name?

    Re: A Formula In This Workbook Contains One Or More Invalid References

    I appreciate the feedback, however I can't seem to find any formula errors.

    Am I doing this correctly?
    I select all sheets from the sheet TABS at the bottom of the workbook.
    I then click CNTRL-G and select special, then search for formula errors. Nothing is showing up as an error.

    When I close Excel it tells me I have an invalid reference though.

    Re: One Or More Invalid References


    The code is somewhat restricted.

    It basically opens up a bunch of data files and analyzes the data. It opens 22 extertal files and imports them to a summary sheets with graphs.

    It saves the output as a temp file. It is this temp file that shows the error when I close it. It has all of the functionality it is suppose to have. The error only happens the first time I save the file. If I open the file again later I do not get the message. It is mostly an annoyance. I think it started with the upgrade to Office 2003. I do not know if this helps.

    I thought someone may have a way to test all of the refences in the output file. I think it would take forever to test them individually. I saw post which said hit cntr-G and then search for formula errors. This did not find anything.

    I downloaded a file called findlink but it does not do anything.


    I have a rather complicated workbook with many VBA formulas. When I close it down a window displays "A formula in this workbook contains one or more invalid references". verify that your workbook contains a valid path, workbook, range name, and cell reference.

    This only happens when I close the workbook. I seem to have all functionality. How can I find the invalid reference.


    I have an Excel chart as a separate worksheet. It has 10,000 data points. Is there a way to Zoom in on the X-axis only. I am looking to expand or zoom the X-axis to take a closer look at the individual data points. Since I have 10,000, I need a way to take a closer look at an area defined by the mouse pointer.

    Can this be done with VB code? I am not looking to expand or zoom the "Y" axis, this can be done with the scale values. Can this be done with a mouse drag or double click on the area of the chart I want to expand.

    Thanks in advance,

    Re: Lock Shape Position On Screen?

    I do not believe this will work. I am trying to do this with VBA code. I copied this obeject in a procedure from another module and need to set its properties somehow with code.

    I have the xlfreefloating property set and this does not do the trick.


    This sounds simple but I cannot figure it out.
    I created a shape (rectangle). I assigned a macro to it, to activate another worksheet.

    Can I fix the position of the shape on the screen, near the top. I have 700 rows in the worksheet and I want this rectangle always visible, near the top.

    Thanks in advance,

    Re: VBA and macro issues after upgrade to 2003

    I believe the problem is unrelated to this line of code. If I remove it the program stop on another line with a similar error.
    Another error I get is that the range of cells on I am trying to access is protected. I do not get any of these errors with the same file on Excel 2000.

    Is there any special work that needs to be done to convert workbooks when going to Excel 2003?

    Re: VBA and macro issues after upgrade to 2003

    I forgot which board I was on.

    I should have surrounded the code with



    IF the debugger stops on a a line and highlights this line in yellow, this is the line that is causing problems, correct? This is what is happening with the line

    ActiveSheet.Shapes ("Rectangle 7") .Select

    Re: VBA and macro issues after upgrade to 2003

    This is as much code as I can paste. The debugger takes me to the second line.

    I have Excel worsheets that used to work with Excel 2000 and now cause errors since I upgraded to Excel 2003.

    I get the following error when I run the macro;

    Run-time error '-2147467259 (80004005)':
    Method 'Select' of object 'Shape' Failed

    From the following line of code;
    ActiveSheet.Shapes ("Rectangle 7") .Select

    Any suggestions?

    The macro works perfectly on Excel 2000 but not on Excel 2003. The Macro open data files and extracts data into a big table. Is there any issues with Excel 2003. Do I need to rewrite some of the code?

    To get it to run the first time I tried to use it, I had to unprotect the sheet and remove the VBA password. Then I was able to run the Macro but the code fails at the step noted above.