Excel file errors Visual basic?

In that case, STOP your bitching!

Sounds like a good plan.

For your information, the spreadsheet will never work with 2007. I tried it and the first bug is that it uses "Application.FileSearch" which microsoft kindly removed from excel 2007. Their response is basically:- "Application.FileSearch has been deprecated from Excel 2007. "

A google search will show how many people this crazy decision is affecting.

--
Geo

OpenOffice Calc didn't ask any passwords... Here is Save module (Everything commented out. I don't know why)

-ek

Rem Attribute VBA_ModuleType=VBAModule Sub Save Rem Option Explicit Rem Rem Public Sub SaveBook() Rem Application.ScreenUpdating = False Rem Dim fName Rem Dim NewBook Rem Dim j Rem Dim saveFile Rem fName = UserForm1.tChooseDir.Value & "\\" & UserForm1.tFileName.Value Rem If Dir(UserForm1.tChooseDir.Value, vbDirectory) = "" Or Dir(UserForm1.tChooseDir.Value, vbDirectory) = "." Then Rem MsgBox "Please choose an existing directory", vbCritical Rem saveFile = False Rem ElseIf UserForm1.tFileName.Value = "" Or UserForm1.tFileName = ".xls" Then Rem MsgBox "No file name specified", vbCritical Rem saveFile = False Rem ElseIf UserForm1.tFileName.Value = Application.ActiveWorkbook.Name Then Rem MsgBox "This file name can not be used", vbInformation Rem UserForm1.tFileName.Value = "" Rem saveFile = False Rem ElseIf UserForm1.CBoxFiles.ListCount > 0 Then Rem For j = 0 To UserForm1.CBoxFiles.ListCount - 1 Rem UserForm1.CBoxFiles.ListIndex = j Rem If UserForm1.tFileName.Value = UserForm1.CBoxFiles.Value Then Rem If MsgBox("This file already exist, do you want to overwrite it ?", vbOKCancel) = vbOK Then Rem saveFile = True Rem Else Rem saveFile = False Rem MsgBox "File not saved", vbInformation Rem End If Rem Exit For Rem Else Rem saveFile = True Rem End If Rem Next Rem Else Rem saveFile = True Rem End If Rem If saveFile Then Rem Set NewBook = Workbooks.Add Rem With NewBook Rem Call FormatBook Rem Application.DisplayAlerts = False Rem .SaveAs (fName) Rem .Close Rem Application.DisplayAlerts = True Rem End With Rem MsgBox "File saved !", vbInformation Rem Call ListBook(UserForm1.tChooseDir.Value) Rem End If Rem UserForm1.tFileName.Value = "" Rem Application.ScreenUpdating = True Rem End Sub Rem Rem Public Sub ReadBook() Rem Dim i Rem i = 1 Rem Do Rem SMPS.Vars.Cvars(ActiveCell.Offset(i, 0).Value).Value = ActiveCell.Offset(i, 1).Value Rem i = i + 1 Rem Loop Until (ActiveCell.Offset(i, 0).Value = "") Rem Call LoadDefault Rem UserForm1.MultiPage1.Value = 0 Rem UserForm1.MultiPage2.Value = 0 Rem UserForm1.MultiPage1(1).Enabled = False Rem UserForm1.MultiPage1(2).Enabled = False Rem UserForm1.MultiPage1(3).Enabled = False Rem UserForm1.MultiPage1(4).Enabled = False Rem UserForm1.MultiPage1(5).Enabled = False Rem UserForm1.MultiPage1(6).Enabled = False Rem End Sub Rem Rem Public Sub VerifBook(file) Rem Dim i Rem Dim tempsum Rem Application.ScreenUpdating = False Rem With Application.Workbooks.Open(file) Rem Rem i = 1 Rem Range("b1").Select Rem Do Rem tempsum = tempsum + sumstr(ActiveCell.Offset(i, 0).Value) Rem i = i + 1 Rem Loop Until (ActiveCell.Offset(i, 0).Value = "") Rem If Range("a1").Value = tempsum And IsNumeric(Range("a1").Value) And Range("a1").Value "" Then Rem Call ReadBook Rem InUseFile = Dir(file) Rem .Close Rem Else Rem .Close Rem MsgBox "This file is not a valid save file", vbExclamation Rem End If Rem End With Rem Application.ScreenUpdating = True Rem End Sub Rem Rem Public Sub ListBook(UsedDir) Rem Dim i, j Rem Dim str Rem Dim index Rem UserForm1.CBoxFiles.Clear Rem With Application.FileSearch Rem .LookIn = UsedDir Rem .FileType = msoFileTypeExcelWorkbooks Rem If .Execute() > 0 Then Rem For i = 1 To .FoundFiles.Count Rem str = Split(.FoundFiles(i), "\\") Rem If str(UBound(str)) Application.ActiveWorkbook.Name Then Rem UserForm1.CBoxFiles.AddItem str(UBound(str)) Rem End If Rem If InUseFile "" Then Rem For j = 0 To UserForm1.CBoxFiles.ListCount Rem If InUseFile = UserForm1.CBoxFiles.Value Then Rem UserForm1.CBoxFiles.ListIndex = j Rem End If Rem Next Rem Else Rem UserForm1.CBoxFiles.ListIndex = 0 Rem End If Rem Next i Rem UserForm1.lErrorLoading.Caption = "" Rem UserForm1.Frame25.Visible = True Rem Else Rem UserForm1.lErrorLoading.Caption = "Directory doesn't exist or no saved files inside" Rem UserForm1.Frame25.Visible = False Rem End If Rem End With Rem Rem End Sub Rem Rem Public Sub FormatBook() Rem Dim data As Object Rem Dim i Rem i = 1 Rem Range("a1").Value = 0 Rem Range("b1").Select Rem For Each data In SMPS.Vars.Cvars Rem ActiveCell.Offset(i, 0).Value = data.Name Rem ActiveCell.Offset(i, 1).Value = data.Value Rem Range("a1").Value = Range("a1").Value + sumstr(data.Name) Rem i = i + 1 Rem Next Rem End Sub Rem Rem Public Function sumstr(str) Rem Dim char Rem sumstr = 0 Rem For char = 1 To Len(str) Rem sumstr = sumstr + Asc(Mid(str, char, 1)) Rem Next Rem End Function Rem Rem End Sub

which, as I pointed out earlier, halts with an error when using their default "C:\\" as the directory - since you then get "C:\\\\"

--
Geo

You have no chance trying to get it to work under XL2007 unless you have source code access. XL2007 VBA is gratuitously incompatible with all previous versions breaking lots of XL based VBA based applications.

You can't be doing anything very demanding or complex then.

Hardy any non-trivial XL2003 spreadsheets will work on XL2007 without significant modifications if they use VBA FileSearching or graphics objects. Out of the box you get two ticks for 10^8 on log graphs and other ludicrous cosmetic errors. XL2007 SP1 charting works but is glacially slow. SP2 promises to be a bit faster.

XL2003 is the program of choice if you like to use a stable product.

Regards, Martin Brown

Thanks I'll have to remember this OO trick.

Cheers

At lest for on semi.

I wonder if the code works if it is inserted into excel 2k7 as new code from this pasted listing.

I wonder if the code can be debugged now. Probably have to paste in each worksheets as well.

The quest is to make an excel 2k7 fully functional equivalent.

Like in the show "Chopped"... You will have 30 minutes. :-)

itical

Name =3D

tion

, do you want to

bInformation

ue =3D

fset(i,

",

m str(UBound(str))

BoxFiles.ListCount

serForm1.CBoxFiles.Value

CBoxFiles.ListIndex =3D j

dex =3D 0

tory doesn't

ata.Name)

de

ch

- Hide quoted text -

CLEAN MY PROBE

I AM PROTEUS

on

"

ry

is their

I would say Open Office is more stable than even XL2003. Most of my stuff from earlier versions worked without a hitch on version 3. The few that didn't turned out to be because I had gotten away with things that were not really legal. Earlier versions muddled through the mistakes but the later version caught them.

onsemiflyback had written this in response to

: Hello all, i know that this article is quite old but i didn't know about it before. I'm working in OnSemiconductor and I designed the spreadsheet you speak about. there are some points I want to explain:

- currently, Office 2003 is our standard tool, so we can't go to Office

2007 until our IT team gives us the software

- the spreadsheet is password protected for copyright reasons

- we are looking for a full web application to avoid Office compatibility problems, but we have nothing functional.

So, based on previous comments, we investigate and found a solution. We are releasing a new version that solves some bugs but, to be functional, this spreadsheet requires the Microsoft Office Web Components (for office

2003 of course):

Here is the right way to launch this spreadsheet:

- download and install Microsoft Office Web Components (only once)

- download the spreadsheet and save it where you want It should be ok !

There is a link on the specification panel to send us a mail, don't hesitate to use it if you have any problem.

best regards

David Sabatie

##-----------------------------------------------## Delivered via

Electronics Enthusiasts' Community of the Net Web and RSS access to your favorite newsgroup - sci.electronics.design - 366368 messages and counting! ##-----------------------------------------------##

onsemiflyback had written this in response to

: Hello all, I'm working at OnSemiconductor and,particularly, in the team who developed the spreadsheet you are talking about. first of all, thank for using our tools ! Concerning the spreadsheet, we need to use Office 2003 as it is our standard tool; Office 2007 should be in use only at the end of this year. I know it's a bit limited but password security is used due to copyright concerns.

Unless the preceding problems, we investigate the issue you have and found a solution: first, you must install the Office Web Component from Microsoft as the spreadsheet requires it; once it's done you should download again the spreadsheet from our web site. We are also releasing a new version that resolves different bugs.

thank you for using our tools, best regards

David Sabatie

##-----------------------------------------------## Delivered via

Electronics Enthusiasts' Community of the Net Web and RSS access to your favorite newsgroup - sci.electronics.design - 366448 messages and counting! ##-----------------------------------------------##

Thank you for your response.

I have had nothing but problems with excel since upgrading to office

2007 so I went back to using office 2003.