Posts by darthrader

darthrader · Oct 9th 2011

Re: Application.filesearch not supported Excel 2010

Thanks All,

The Dir() function worked perfectly. My code actually runs faster now too.

Regards,

darthrader · Sep 29th 2011

Re: Application.filesearch not supported Excel 2010

Smallman,

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 workbooks.open method to open the text file using the full name returned by DIR(). I did not know Workbooks.open 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.

Regards,

darthrader · Sep 28th 2011

Hello,

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.

Code

With Application.FileSearch
.NewSearch
.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?

darthrader · Mar 18th 2008

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

I tried the name manager, it did not find any errors. I will keep working on this. I have a section that deletes the code once it executes. I also delete some of the shapes on the main page. I can try hiding them instead.

Regards,

darthrader · Feb 29th 2008

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.

darthrader · Feb 28th 2008

Re: One Or More Invalid References

Hi,

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.

darthrader · Feb 28th 2008

Hello,

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.

Thanks

darthrader · Jun 19th 2007

Re: Zoom Or Expand Line Chart X-axis

I do not know much about dynamic ranges, and I will look into this later this week.

Thanks for the feedback.

darthrader · Jun 17th 2007

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,

darthrader · Feb 16th 2007

Re: Lock Shape Position On Screen?

The freeze command worked. I copied the rectangle to row one in VB code and then froze row one. It worked!

Thanks

darthrader · Feb 14th 2007

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.

regards,

darthrader · Feb 14th 2007

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,

darthrader · Jul 22nd 2006

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?

darthrader · Jul 22nd 2006

Re: VBA and macro issues after upgrade to 2003

I forgot which board I was on.

I should have surrounded the code with

Code

correct?

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

Code

ActiveSheet.Shapes ("Rectangle 7") .Select

darthrader · Jul 21st 2006

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.

Code

ClrIdx = ActiveSheet.Cells(2, Count).Font.ColorIndex
    
    ActiveSheet.Shapes("rectangle 7").Select      'to put command buttons in
    Selection.Copy
    ActiveSheet.Cells(2, Count).Select
    ActiveSheet.Paste
    'Selection.Width = 34
    'Selection.Height = 14
    Selection.Width = ActiveSheet.Cells(2, Count).Width
    Selection.Height = ActiveSheet.Cells(2, Count).Height
    Selection.Characters.Text = LaserSN

Display More

darthrader · Jul 21st 2006

Re: VBA and macro issues after upgrade to 2003

Yes, this is the line the debugger takes me too. I will try to post some of the surrounding code.

darthrader · Jul 20th 2006

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.

Regards,