Problems with Excel Text File

[bpeirson]

In my code everything works well except for the third to last line which returns an "Application defined or Object defined" error.

The offending line is:

    ActiveWorkbook.Close SaveChanges:=True

I suspect that the problem lies in the fact that I am have saved the file in the text format but I am not sure. Any help is appreciated.

Here is the code I have written.

Sub Open_DXF()
    Application.ScreenUpdating = False
    Dim MainF As String, Mainb As String, f_t As String, r As Integer
    Dim FolderName As String
    FolderName = FuncGetFolderName("Select source folder for DXF file to be converted.")
 
"CODE DELETED FOR SPACE CONSERVATION"

OPEN_THIS_FILE:
     Workbooks.OpenText Filename:=FolderName & "\" & MainF, Origin:=437, StartRow:=1, DataType:=xlDelimited, TextQualifier:= _
        xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, Semicolon:=False, _
        Comma:=False, Space:=False, Other:=False, FieldInfo:=Array(1, 1), _
        TrailingMinusNumbers:=True
     ActiveWorkbook.Worksheets(1).Range("A:A").Copy Destination:=Workbooks("DXF Conversion1").Worksheets("Sheet1").Range("A:A")
     ActiveWindow.Close
    Windows("DXF conversion1").Activate
'Begin conversion of DXF to G-code
Run "Extract_Coordinates"
'G-code is ready to be saved.
  If MsgBox("Save the G-code in the same folder?", vbYesNo) = vbNo Then
    FolderName = FuncGetFolderName("Select destination folder")
  End If
  Cells(1, 1) = MainF
    ActiveWorkbook.SaveAs Filename:=FolderName & "\" & MainF & ".tap", FileFormat:=xlText
    MsgBox ("The new file """ & MainF & """ has been saved in folder """ & FolderName & """")
    Application.ScreenUpdating = True
    Application.ScreenUpdating = False
    ActiveWorkbook.Close SaveChanges:=True
999:
End Sub

 

[GlennUK]

If you've just saved the file, why are you specifying SaveChanges:=True?



Cheers, Glenn.

Did you hear about the literalist show-jumper? He broke his nose jumping against the clock.

 

[bpeirson]

If I dont add SaveChanges:=True then Excel asks if I want to save the file. I think it is because the screenupdating is considered a change to the file. I need the screenupdating to remove the MsgBox.

 

[GlennUK]

Add SaveChanges:=False if you don't want to save the file.

Cheers, Glenn.

Did you hear about the literalist show-jumper? He broke his nose jumping against the clock.

 

[bpeirson]

The issue I have is the "Application-defined" or "Object defined error" which comes up at the ActiveWorkbook.Close command.

The error remains regardless of True or False being supplied.

I would like to have the file close automatically because if other users are asked about saving the file and/or the changes they invariably need my assistance in answering the question.

(Where do I save it? I didn't change anything so why is it asking if I would like to save the changes? I thought you said this process was automated. Etc.)

 

[HughLerwill]

Try inserting

ActiveWorkbook.Saved = True

before

ActiveWorkbook.Close

Hugh

 

[bpeirson]

Same problem. There is no difference if I save or not before I close.

The code works well if I delete the Application.Close command and then close the file manually.

As I said at the beginning I think the problem is that this file is saves as an Excel text file.

Does anyone know of specific properties of the text file format which should be addressed while closing?

If I close manually Excel asks if I would like to save in the xls format. When I record that event as a macro it is returned simply as Activeworkbook.Save then Activeworkbook.Close even though dialogues open requesting information during the save and close.

 

[HughLerwill]

<<I think the problem is that this file is saves as an Excel text file>>

Have you tried saving it 'normally' as an Excel format file to see if the problem clears?

If it does you could then try the sequence of (psuedo code);

Save as text file
Save as Excel file
Close
Delete Excel file

Hugh,

 

[rmikesmith]

beiperson & Hugh,

I have simulated this setup as closely as I can with the information provided. Closing the workbook previously saved as a text file does not trigger an error for me. If I use Hugh's suggestion (ActiveWorkbook.Saved = True) or instead Application.DisplayAlerts = False then the workbook is closed without prompt. Same if I use ActiveWorkbook.Close SaveChanges:=False although if I set the SaveChanges parameter to True then the SaveAs dialog is displayed.

bpeirson, I am curious how it is you are able to reference the "DXF Conversion1" workbook without an extension?

Regards,
Mike

 

[docjohn52]

Hi there,

Just dropped in to add my $.02

try this

Application.DisplayAlerts = False
(do your save)
Application.DisplayAlerts = True
Make sure you turn 'em back on...

(do your close)

been there done it, didn't like it...

docjohn

 

[docjohn52]

On further review, it could be a file open flag
due to multiple users having opened and trying to save.
don't see any prevention code...

the doc

 

[GlennUK]

Same problem. There is no difference if I save or not before I close.

The code works well if I delete the Application.Close command and then close the file manually.

I take it that the above was a typo, and you actually have Activeworkbook.Close

anyway, you said this after my last post ...

If I dont add SaveChanges:=True then Excel asks if I want to save the file.

... so the Activeworkbook.Close doesn't trigger an error then, but does when you append SaveChanges:=False ? I find that very strange. I'll have a think about this and get back.

Cheers, Glenn.

Did you hear about the literalist show-jumper? He broke his nose jumping against the clock.

 

[bpeirson]

Hugh, I'll try that if a definitive answer isn't presented. It seems to me that there should be a "right" way to do this rather than tricking Excel into thinking all is normal.

Mike, the lack of file extension is because the workbook is opened as a template.

docjohn, I have inserted your suggestion and the error code is not displayed. I can live with that for now but I know that the error is still there and I would like to know why for future reference on other projects.

As for multiple users I am the only person here who knows this file exists as I am still testing it. I don't yet have the skill or knowledge to add in prevention code. Help with that would be appreciated.

Glenn you are right about the typo. But "Activeworkbook.Close" causes the error. I have tried with and without "SaveChanges". I have tried ":=True" and ":=False" when using SaveChanges. If you find an answer let me know please.

Thank you all for your time. Since I have a working solution I will move on to the next project but I will monitor this thread if someone finds the root cause.

 

[bpeirson]

The actual problem with this code was the variable MainF.

During code tweaking the same error was generated under a different set of circumstances. I now put the value of MainF into a cell on the spreadsheet and use a cell reference when I need that value.

I don't fully understand the problem with the use of the variable but the errors are now gone even without "Application.DisplayAlerts = False".