VBA quit- save problem

[CADfunk MC]

Hi All

I have a VBA macro that reads in a textfile, re-arranges the content, and saves it as a .cvs for further use in a drawing.

All goes as desired up untill the moment I quit excel. I get 3 messages (translated from dutch):
1) Do you want to save the changes in MyFile.cvs file? (yes)
2) Sure? (Yes)
3) MyFile.cvs may contain functions that are not compatibel with CVS (seperated bij listmarkings). Do you want to preserve the content? (Yes)

I would like these prompts/msgboxes to get the "Yes" answer automtically. I 've tried some things but they didn 't work:



' ActiveWorkbook.Saved = True
' Ex.ActiveWorkbook.Save
' ActiveWorkbook.Close
' ThisWorkbook.Save
' ThisWorkbook.Saved = True
' Saved = True
' Application.DisplayAlerts = False


These things fool the program in to thinking the content has been saved properly, or they simply skip the saving option. They do not have the desired effect.

I found an option that answers questions from outlook automatically. It uses the MailItemActions collection to retrieve the corresponding action. Then you can call Action.Execute.


'Dim myItem As Outlook.MailItem
'Set myItem = objAppt.Actions("Yes").Execute
'myItem2.Send

I also thought perhaps the action could be retrieved with something I found on msdn, but had no luck:
Ex.Application.TransitionMenuKeyAction = xlLotusHelp

Is anybody acquainted enough with excel (and VBA) to be able to answer this question? I hope someone can help me out of this. I put it in this CAD forum because I think maybe many people use Excel to do some things CAD doesn 't.

Kind Regards, Me.Mike.Has Not Not Not Answer

[MikeJarosz]

You want the "display alerts" property. Look it up in VBA help. If you turn off alerts, Excel will automatically respond with the default response, which may not be what you want. I copied this from the help file:




Application.DisplayAlerts = False
Workbooks("BOOK1.XLS").Close
Application.DisplayAlerts = True

[B_Roche]

hmmm, cant say Ive ever run in to this one - but if its just a .csv file (not cvs), why dont you just write out the file as text, using VBA? check out the print #, (or WRITE #, better with commas)... you are aware that a .csv file is just a comma delimited file? Open it up in notepad ......

[CADfunk MC]

Yes, I meant to type .csv
This filetype is ideal to me because it is small and it uses cells, like Excel.
I have a different program that will read the values from the cells, and make a drawing.
It won 't read the values in the order of the commas, only in the order of cells.

If I choose to activeworksheet.save, then activeworksheet.save = true, and ignore display-alerts, I get no prompts. But I also get one line of values in the first cell on each row, and the values are seperated by commas. That is not what I want.

When I answer all the prompts, the values are nicely seperated into cells, the way they were in Excel. Perhaps I should work around the prompts by defining the cell values differently so the chance of incompatible functions being present is annihilated, but auto-answering the prompts seems the shortest way round (if anyone knows how).

[B_Roche]

errr, you *are* aware that a .csv file is a comma delimited file? that is exactly what excel is reading, and writing. Excel only *displays* it in a nice grid for the user.. but the data behind it is just a text file that may look something like this:

"Cell A1 value","CellB1 Value","Cell C1 value"
"Cell A2 value","CellB2 Value","Cell C2 value"

Perhaps the problem you are having is that you are trying to save something *other* than the above format, as a .CSV file?

[CADfunk MC]

Yes, you are right. First, I read a textfile into excel, and save the new file as excel. Then excel does all sorts of things to sort the data. When the data is properly arranged, I want the file to be saved as a .csv for reading into a drawing.
You can get the messages I mentioned quite easily by starting up excel and saving the file as .csv. These messages appear, even if you save a .csv for the second time.

If I do not answer the prompts with Yes (by working around the prompts in VBA) the file will not save properly. The values in excel will become distorted, and seperated by commas.
The data is read into CAD this way, so I need the values seperated in cells;


Set f1 = fs.OpenTextFile("H:\MyFile.csv", ForReading)

Do
s = f1.ReadLine
data = Split(s, ";")

Loop While Not f1.AtEndOfLine


I seem to have found the cause of this problem on the following website:
http://www.mvps.org/access/modules/mdl0046.htm
"However, Excel doesn't always wrap the cell values with quotes when saving to a CSV file (a number implies that it will be written without the quotes)"

Now for the solution.

[CADfunk MC]

OK, I found a way to work around the problem. I now export the excel file to a .txt file, seperated by the ; sign. That works perfect, no more promts.