Automatically saving a text file (created with a macro from csv) as Word.doc silently

[wittonlin]

I'm using a macro to create a CSV file from a source file then creating a TXT file and opening with Word.

I got this much to work. See Macro flow below. But I have to browse to last file name, edit it to new name and save it all manually, mainly because the filename of the text file is hard-coded to Delete.txt right now.

And now because of another process I'm adding this to, I need to be able to save the TXT file (with the same name as the initial macro uses) automatically as a Word file and silently close it.

Once the csv is created using the initial macro here how we save the text file and open it with Word.

 Sub FromCSVFileOnlyMakeWordFile() 
     
     'Select last row in worksheet.
    Selection.End(xlDown).Select 
     
    Do Until ActiveCell.Row = 1 
         'Insert blank row.
        ActiveCell.Resize(5).EntireRow.Insert Shift:=x1Down 
         'Move up one row.
        ActiveCell.Offset(-1, 0).Select 
    Loop 
     
    Call SaveAsTextFileFixedSilentClose 
    Application.DisplayAlerts = False 
    ActiveWorkbook.Close 
    Application.DisplayAlerts = True 
     
    Call OpenFixedTextFileInMSWord 
     
End Sub

 Sub SaveAsTextFileFixedSilentClose() 
     
    MyPath = "C:\ACTIVE\" 
    newfname = "Delete" 
    Set MySheets = ActiveWindow.SelectedSheets 
    Application.DisplayAlerts = False 
    For Each ws In MySheets 
        ws.Copy 
        Suffix = VBA.Right(ws.name, 3) 
        ActiveWorkbook.SaveAs Filename:=MyPath & newfname & ".txt", _ 
        FileFormat:=xlText, CreateBackup:=False 
        ActiveWorkbook.Close 
    Next ws 
    Application.DisplayAlerts = True 
     
End Sub

 Sub OpenFixedTextFileInMSWord() 
     
    Dim s As Variant 
    Dim i As Integer 
     
    s = Shell("WINWORD.EXE ""C:\ACTIVE\Delete.txt""", vbNormalNoFocus) 
     
End Sub

It appears it is possible!


This is close.
http://excel-macro.tutorialhorizon.c...word-document/

Function FnWriteToWordDoc()
   Dim objWord
   Dim objDoc
   Dim objSelection
   Set objWord = CreateObject("Word.Application")
   Set objDoc = objWord.Documents.Add   
   objWord.Visible = True
   Set objSelection = objWord.Selection
 
   objSelection.TypeText ("Saving this file after this text")
   objDoc.SaveAs ("D:\MyFirstSave")
 
End Function

This one has all kinds of issues it appears:
http://stackoverflow.com/questions/3...-doc-and-excel

Sub OpenManual()
  Dim objWord, doc
 
  'We need to continue through errors since if Word isn't
  'open the GetObject line will give an error
  On Error Resume Next
  Set objWord = GetObject(, "Word.Application")
 
  'We've tried to get Word but if it's nothing then it isn't open
  If objWord Is Nothing Then
     'No need to do anything
  Else
    'It's good practice to reset error warnings
    On Error GoTo 0
 
    'Open your document and ensure its visible and activate after openning
    objWord.Visible = True
    objWord.Activate
    For Each doc in objWord.Documents
      doc.SaveAs("C:\Temp\SavedDocuments\" & doc.Name)
      doc.Close
    Next
    Set doc = Nothing
    objWord.quit
  End If
 
  Set objWord = Nothing
End Sub

Also posted here yesterday: Automatically saving a text file (created with a macro from csv) as Word.doc silently

 

[Macropod]

Your filename is hard-coded, but that's only because you've coded it that way instead of getting the name from either the contents of a cell or via user input, for example. Furthermore, your loop in the SaveAsTextFileFixedSilentClose sub seems pointless, since it closes the active workbook immediately the first worksheet is saved.

If you could explain what you're actually trying to achieve, more progress could be made.

 

[wittonlin]

Your filename is hard-coded, but that's only because you've coded it that way instead of getting the name from either the contents of a cell or via user input, for example. Furthermore, your loop in the SaveAsTextFileFixedSilentClose sub seems pointless, since it closes the active workbook immediately the first worksheet is saved.

If you could explain what you're actually trying to achieve, more progress could be made.

Yeah this was just to get the idea across of what we're doing.

Basically, we just need to go from a source file, normally a csv or new workbook using about 20 rows of data up to column R or so; and silently save it as a Word file using internal code that determines the filename (not done yet but need this to work to test and is still password protected). So it's not a FIXED filename and depends on the recipient, a few cells in the data and the date.

The ONLY reason we create a TXT in the middle (then open it in Word) is for the exact two issues we've never figured out. How to save our data as a Word file from Excel using a coded filename, without any manual involvement AND how to avoid the excel rows running off the right edge of a normally set Word doc.

Does that help?

 

[Macropod]

Does that help?

Somewhat, but you still haven't given any indication of what the filename should be. Of course, if you output the range directly to a new Word document, there's no need for either a filename (unless you want to automatically save the document) or the text file circumlocution. Another way of approaching this would be to automate a Word mailmerge, which could mean you don't need to determine how many rows to output beforehand; it also gives complete control over which columns get output, the possibility of filtering the data, and how that output is formatted.

 

[wittonlin]

Somewhat, but you still haven't given any indication of what the filename should be. Of course, if you output the range directly to a new Word document, there's no need for either a filename (unless you want to automatically save the document) or the text file circumlocution. Another way of approaching this would be to automate a Word mailmerge, which could mean you don't need to determine how many rows to output beforehand; it also gives complete control over which columns get output, the possibility of filtering the data, and how that output is formatted.

Ok here's a better view. Keep in mind this is within a series of events in another macro and all the filtering of data getting the output data, etc. is done. The existing macro does all this then uses custom coding to create a unique filename and saves file as a CSV file already. I don't think I was clear on this.

Now before closing the CSV, like normal, we need to (Call NewMacro) that saves this CSV file as a TXT file with the exact same filename as the prior CSV file, then saves THIS file as a Word doc with the same layout, spacing and filename as the text file and just deletes it when done.

We use a TXT file in the middle here to deal with Excel to Word format issues like long rows running off the right edge of the page.

 

[Macropod]

You really do seem to be avoiding an adequate description of what you're trying to do.

All I can figure out is that you want to transfer some data to one or more Word documents whose name is to be determined 'somehow'. The code structures you've posted are illogical, including loops that for no apparent reason insert 5 blank rows between every data row on the active sheet then calls another macro with a loop that creates a text file then closes the active workbook without saving and, hence, exits on the first iteration, only to return processing to the calling macro with then closes whatever other workbook is now active (again without saving), before calling another macro that is apparently meant to open the text file in Word before saving it as a document.

Unless you need the text file for something else, its creation is quite superfluous, since the data can be written directly to Word. Consider, for example:

Sub Demo()
Application.ScreenUpdating = False
'Note: A reference to the Word library must be set, via Tools|References
Dim wdApp As New Word.Application, wdDoc As Word.Document, StrFlNm As String
Dim lRow As Long, lCol As Long, r As Long, c As Long, StrTmp As String
With ActiveSheet
  StrFlNm = ActiveWorkbook.Path & "\" & .Name & ".docx"
  With .UsedRange.Cells.SpecialCells(xlCellTypeLastCell)
    lRow = .Row
    lCol = .Column
  End With
  For r = 1 To lRow
    For c = 1 To lCol - 1
      StrTmp = StrTmp & .Cells(r, c).Text & ","
    Next
    StrTmp = StrTmp & .Cells(r, lCol).Text & vbCr
  Next
End With
Set wdDoc = wdApp.Documents.Add: wdApp.Visible = False
With wdDoc
  .Range.Text = StrTmp
  .SaveAs2 Filename:=StrFlNm, FileFormat:=wdFormatXMLDocument, AddToRecentFiles:=False
  .Close False
End With
wdApp.Quit
Set wdDoc = Nothing: Set wdApp = Nothing
Application.ScreenUpdating = True
End Sub

The above macro quietly creates a new Word document in the active workbook's folder, with the active sheet's name, and containing the comma-separated contents of all cells on that sheet.

Unless you give a more meaningful description of what you're trying to do, that's about the best you can expect.

 

[wittonlin]

Sorry for the delay!

I get a Compile error: User-defined type not defined...
here: wdApp As New Word.Application

I tried to define wdApp, but no luck.

As far as your description, it's near spot on!

The only difference is that we just need to use the current or ActiveWorkbook filename vs. the sheet name to create the new filename.

The reason we're inserting the TXT document is purely aesthetics of how it looks in word. It's just a little cleaner with column data separated by a tab vs. comma. But I have reconsidered this, primarily because I just realized that the formatting issue with long rows running off the right edge of the Word doc when converting xls to doc, doesn't apply when you come from a csv file, technically another TXT file.

 

[Macropod]

I get a Compile error: User-defined type not defined...
here: wdApp As New Word.Application

And I'll bet you haven't read the comment in the code...
'Note: A reference to the Word library must be set, via Tools|References

The reason we're inserting the TXT document is purely aesthetics of how it looks in word. It's just a little cleaner with column data separated by a tab vs. comma

In that case (once you've set the Word reference), change:
StrTmp = StrTmp & .Cells(r, c).Text & ","
to:
StrTmp = StrTmp & .Cells(r, c).Text & vbTab

 

[wittonlin]

Thank you!

And I'll bet you haven't read the comment in the code...
'Note: A reference to the Word library must be set, via Tools|References

In that case (once you've set the Word reference), change:
StrTmp = StrTmp & .Cells(r, c).Text & ","
to:
StrTmp = StrTmp & .Cells(r, c).Text & vbTab