Convert rtf to csv/excel?

[Immatoity]

Hi

Just wondered if there was a way to do this using vba??

The rtf files will all have unique names.

thanks

PS if in wrong forum sorry!

[Nelviticus]

Yes, just save them as text files with a ".csv" extension.

[Immatoity]

Yes, just save them as text files with a ".csv" extension.

Hi the users concerned aren't really up to this.. I know I know...

What will happen is User A sends by email an rtf file to User B

User B needs to convert the rtf to csv...

Is there a way of doing this in VB/VBA so User B can click a button

[Nelviticus]

Sort of - you could write code to just save it as a text file with a csv extension, but if the document content isn't actual csv-compatible text then it won't be a meaningful csv file. Writing code to check whether it's the right format, or to convert Word tables (for example) into csv-compatible text, is a whole lot more complicated.

I should also ask - why not just email it as a csv file in the first place?

If you just want something to pop up a file-chooser dialog box then save the doc as a text file with a csv extension then the following inelegant code should do it:

Public Sub SaveAsCSV()
Dim fdSave As FileDialog
Dim fileName As String
fileName = ActiveDocument.Name
Set fdSave = Application.FileDialog(msoFileDialogSaveAs)
With fdSave
.AllowMultiSelect = False
.Title = "Choose a location"
.InitialFileName = fileName
If .Show = -1 Then
fileName = .SelectedItems(.SelectedItems.Count)
fileName = ChangeExtension(fileName, "csv")
ActiveDocument.SaveAs _
fileName:=fileName, _
FileFormat:=wdFormatText, _
LineEnding:=wdCRLF
End If
End With
Set fdSave = Nothing
End Sub

Private Function ChangeExtension(fileName As String, newExt As String) As String
Dim newName As String
Dim dotPos As Long
dotPos = InStrRev(fileName, ".")
If dotPos > 1 Then fileName = Left(fileName, dotPos - 1)
fileName = fileName + "." + newExt
ChangeExtension = fileName
End Function

[Immatoity]

Sort of - you could write code to just save it as a text file with a csv extension, but if the document content isn't actual csv-compatible text then it won't be a meaningful csv file. Writing code to check whether it's the right format, or to convert Word tables (for example) into csv-compatible text, is a whole lot more complicated.

I should also ask - why not just email it as a csv file in the first place?

If you just want something to pop up a file-chooser dialog box then save the doc as a text file with a csv extension then the following inelegant code should do it:

Public Sub SaveAsCSV()
Dim fdSave As FileDialog
Dim fileName As String
fileName = ActiveDocument.Name
Set fdSave = Application.FileDialog(msoFileDialogSaveAs)
With fdSave
.AllowMultiSelect = False
.Title = "Choose a location"
.InitialFileName = fileName
If .Show = -1 Then
fileName = .SelectedItems(.SelectedItems.Count)
fileName = ChangeExtension(fileName, "csv")
ActiveDocument.SaveAs _
fileName:=fileName, _
FileFormat:=wdFormatText, _
LineEnding:=wdCRLF
End If
End With
Set fdSave = Nothing
End Sub

Private Function ChangeExtension(fileName As String, newExt As String) As String
Dim newName As String
Dim dotPos As Long
dotPos = InStrRev(fileName, ".")
If dotPos > 1 Then fileName = Left(fileName, dotPos - 1)
fileName = fileName + "." + newExt
ChangeExtension = fileName
End Function

thanks for that..Noddy question time... where do I put that Sub and when will it be run in relation to the emails the user receives?

I have worked out that another manual solution is to open the rtf file, save it as a webpage, open that webpage in excel and then save as an excel file. That seems to work ok..any way I can automate that?

PS They cant email it as csv as the specific bit of bespoke software only outputs as rtf file... and the users who create the rtf will not be willing to change anything..they want to simply email and forget

[Nelviticus]

You put that a) somewhere in their 'normal' template and add a toolbar button for them to run it, or b) put it in another template that gets loaded when Word starts, and put a toolbar + button in that template.

In either case, it will be run when they have the rtf file open in Word and they click the toolbar button that runs the code.

You could automate your webpage method but it would be more complicated than the above - you'd still need to put code somewhere, but it would need to have code for controlling both Word (for saving as a web page) and Excel (for opening the web page and saving it as an Excel file). Google 'microsoft office automation' if you want to look into that.

[Immatoity]

You put that a) somewhere in their 'normal' template and add a toolbar button for them to run it, or b) put it in another template that gets loaded when Word starts, and put a toolbar + button in that template.

In either case, it will be run when they have the rtf file open in Word and they click the toolbar button that runs the code.

You could automate your webpage method but it would be more complicated than the above - you'd still need to put code somewhere, but it would need to have code for controlling both Word (for saving as a web page) and Excel (for opening the web page and saving it as an Excel file). Google 'microsoft office automation' if you want to look into that.

Hi

went into normal template and entered the module..now have a button at the top..

opened up a rtf file, clicked button, it asks me to save the file but doesnt give csv as an option... i save it as an rtf again, that works and it does create a csv file thanks , but the format of the csv is nothing like the rtf file... it puts nearly all the data in column A including headers etc etc..guess I need to think again..

example rtf file attached pre conversion..can someone else try and convert to see what output they get? EDIT Cant upload rtf files and server here doesnt allow winzip download so will have to upload later

[Nelviticus]

i save it as an rtf again, that works and it does create a csv file thanks , but the format of the csv is nothing like the rtf file... it puts nearly all the data in column A including headers etc etc.

Yes, I mentioned at the beginning that it won't actually convert the contents into nice tabulated data for you if they're not in that format already - it's simple code to save a file as CSV, not artificial intelligence. The content of the document needs to be something like this:

1,2,3
4,5,6
7,8,9

If you want something that intelligently guesses what columns things should be in that's a much, much bigger job.