1

I have a simple Excel sheet that I want to save as a CSV-file (and then send as an attachment using Outlook). When done, I want the original Excel sheet to be active and close the CSV-file.

This part works, except I can not seem to close the CSV-file. If I try, Excel terminates.

Sub Save()

Dim ActBook As Workbook
Dim CurrentFile As String
Dim NewFile As String

CurrentFile = ThisWorkbook.FullName
NewFile = Application.DefaultFilePath & "\new.csv"

Application.DisplayAlerts = False
ActiveWorkbook.SaveAs Filename:=NewFile, FileFormat:=xlTextWindows
Application.DisplayAlerts = True

Set ActBook = ActiveWorkbook
Workbooks.Open CurrentFile          ' Make original file active
'   ActBook.Close                   ' This does not work, Excel terminates

End Sub
Community
  • 1
  • 1
larjon
  • 21
  • 1
  • 4

1 Answers1

-2

Use the SaveCopyAs method: https://msdn.microsoft.com/en-us/vba/excel-vba/articles/workbook-savecopyas-method-excel

The SaveCopyAs method allows you to specify the full file path and therefore file type, so start with that.

When you're doing the Outlook bit and you use the attach file to mailitem method, you specify that same full file path and it will attach it to send.

In that fashion, the initial workbook remains the active workbook throughout and you don't need to re-open it.

In your current approach, the .Close will terminate Excel because the active workbook is the one that is open at that time...

JakeyG
  • 100
  • 11
  • That's completely false. Create a workbook and save it as .xlsx. Open the VB editor and paste this (replace with your username): Sub Test() ThisWorkbook.SaveCopyAs ("C:\Users\[username]\Desktop\Test.csv") End Sub Run it and see what happens... It will save it as a CSV – JakeyG Mar 07 '18 at 13:44
  • I know what you want to do, but with this method its not a real file conversion, just the extension is renamed, which leads to the file being corrupted. Excel is still able to open it, but that does not work with every fileformat and is not a good way of doing it. – Plagon Mar 07 '18 at 13:50
  • The question wasn't about a universal solution, it was explicitly about sending a "simple Excel sheet" as a CSV. So the solution does that, using the fact that within the Excel environment, this will work. – JakeyG Mar 07 '18 at 13:53
  • ThisWorkbook.SaveCopyAs ("C:\Users[username]\Desktop\Test.csv") does not work. It creates a copy, although the file extension is .csv, it is still an Excel file. It is a almost an identical copy of the original Excel file. I need it to be a comma separated file that can later be read by a non-Excel program. – larjon Mar 07 '18 at 21:18
  • JakeyG, your solution is basically suggesting an Excel file renamed to look like a CSV file ***is*** a CSV file..? – CLR Mar 08 '18 at 12:03
  • Later versions of Excel also really dislike files where the filetype and the filename suffix don't match. – CLR Mar 08 '18 at 12:04
  • @larjon as i already said, it just renames the file extension and kind of breaks it, look at this [link](https://stackoverflow.com/a/18901226/7938503) – Plagon Mar 09 '18 at 10:07