Results 1 to 7 of 7

Thread: VBA quit- save problem

  1. #1
    100 Club
    Join Date
    2007-07
    Posts
    104
    Login to Give a bone
    0

    Unhappy VBA quit- save problem

    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:

    Code:
    ' 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.
    Code:
    '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
    Last edited by darfnuggi; 2008-07-24 at 10:14 AM.

  2. #2
    AUGI Addict MikeJarosz's Avatar
    Join Date
    2015-10
    Location
    New York NY
    Posts
    1,497
    Login to Give a bone
    0

    Default Re: VBA quit- save problem

    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:


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

  3. #3
    Active Member
    Join Date
    2007-12
    Posts
    68
    Login to Give a bone
    0

    Default Re: VBA quit- save problem

    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 ......

  4. #4
    100 Club
    Join Date
    2007-07
    Posts
    104
    Login to Give a bone
    0

    Default Re: VBA quit- save problem

    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).
    Last edited by darfnuggi; 2008-07-23 at 06:06 AM.

  5. #5
    Active Member
    Join Date
    2007-12
    Posts
    68
    Login to Give a bone
    0

    Default Re: VBA quit- save problem

    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?

  6. #6
    100 Club
    Join Date
    2007-07
    Posts
    104
    Login to Give a bone
    0

    Default Re: VBA quit- save problem

    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;
    Code:
    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.
    Last edited by darfnuggi; 2008-07-24 at 11:01 AM.

  7. #7
    100 Club
    Join Date
    2007-07
    Posts
    104
    Login to Give a bone
    0

    Default Re: VBA quit- save problem

    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.

Similar Threads

  1. View Save Problem?
    By CadDog in forum AutoCAD General
    Replies: 4
    Last Post: 2009-06-15, 10:18 PM
  2. File Save Problem
    By yogesh.mundhe in forum AutoCAD General
    Replies: 3
    Last Post: 2007-10-26, 06:02 PM
  3. X-ref won't quit plotting
    By T-Towntech in forum AutoCAD General
    Replies: 9
    Last Post: 2006-04-12, 05:37 PM
  4. Save to Central Problem
    By raddis in forum Revit - Worksharing/Worksets/Revit Server
    Replies: 7
    Last Post: 2006-04-03, 03:17 PM
  5. Problem with open/save dialog box
    By jclayton in forum Hardware
    Replies: 3
    Last Post: 2004-09-08, 08:38 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •