Home > database >  unable to open the text file using VBA
unable to open the text file using VBA

Time:04-22

This is my excel vba macro. I want to convert the list of text files into pdf for a particular folder. For that, I have written logic to open a single text file from word document to be saved in a pdf format. but it is not saved as pdf file. Can anybody guide me the logic

Dim file As Variant
inp_dir = "C:\Users\HP\OneDrive\Desktop\vbatest\pdfconv\"
inp_file_name = Dir(inp_dir & "*.txt") 'txt path
inp_file = inp_dir & inp_file_name

Dim wdApp As New Word.Application, wdDoc As Word.Document


MsgBox (inp_file)

    ' Set wdDoc = Documents.Open(inp_file)
     Set wdDoc = Documents.Open(Filename:=inp_file, ReadOnly:=True, _
    AddToRecentFiles:=False, Format:=wdOpenFormatAuto, Visible:=False)
  
  wdDoc.SaveAs2 Filename:="inp_file" & Replace(inp_file, ".txt", ".pdf"), _
    FileFormat:=wdFormatPDF, AddToRecentFiles:=False
  wdDoc.Close False
  

CodePudding user response:

Your code is very close to running. Basically you need to open Word using your wdApp object. This example shows how...

Option Explicit

Sub TxtToPDF()
    Dim inp_dir As String
    Dim inp_file_name As String
    Dim inp_file As String
    inp_dir = "C:\Temp\"
    inp_file_name = Dir(inp_dir & "*.txt")       'txt path
    
    Dim wordWasRunning As Boolean
    wordWasRunning = IsMSWordRunning
    
    Dim wdApp As Word.Application
    Set wdApp = AttachToMSWordApplication
    
    Do While Len(inp_file_name) > 0
        inp_file = inp_dir & inp_file_name
        Debug.Print "currently opening " & inp_file

        Dim wdDoc As Word.Document
        Set wdDoc = wdApp.Documents.Open(Filename:=inp_file, _
                                         ReadOnly:=True, _
                                         AddToRecentFiles:=False, _
                                         Format:=wdOpenFormatAuto, _
                                         Visible:=False)
        Dim out_file As String
        out_file = Replace(inp_file, ".txt", ".pdf")
        Debug.Print "saving as " & out_file
        wdDoc.SaveAs2 Filename:=out_file, _
                      FileFormat:=wdFormatPDF, AddToRecentFiles:=False
        wdDoc.Close False
        
        '--- get the next txt file
        inp_file_name = Dir
    Loop
    
    If Not wordWasRunning Then
        wdApp.Quit
    End If
End Sub

Put this code in another module to use (from my personal library).

Option Explicit

Public Function IsMSWordRunning() As Boolean
    '--- quick check to see if an instance of MS Word is running
    Dim msApp As Object
    On Error Resume Next
    Set msApp = GetObject(, "Word.Application")
    If Err > 0 Then
        '--- not running
        IsMSWordRunning = False
    Else
        '--- running
        IsMSWordRunning = True
    End If
End Function

Public Function AttachToMSWordApplication() As Word.Application
    '--- finds an existing and running instance of MS Word, or starts
    '    the application if one is not already running
    Dim msApp As Word.Application
    On Error Resume Next
    Set msApp = GetObject(, "Word.Application")
    If Err > 0 Then
        '--- we have to start one
        '    an exception will be raised if the application is not installed
        Set msApp = CreateObject("Word.Application")
    End If
    Set AttachToMSWordApplication = msApp
End Function

CodePudding user response:

I guess you are close - you just have a small mistake in your destination file name: You write Filename:="inp_file" & Replace(inp_file, ".txt", ".pdf"), but the fixed string "inp_file" makes no sense and invalidates the filename.

I always advice to use intermediate variables, with that it gets much easier to debug and to find errors.

Some more small things:

  • You should use Option Explicit and declare all variables.
  • You have a variable file declared that is never used.
  • declare your path as Constant

Your code could look like:

Const inp_dir = "C:\Users\HP\OneDrive\Desktop\vbatest\pdfconv\"

Dim inp_file_name As String, inp_full_name As String

inp_file_name = Dir(inp_dir & "*.txt") 'txt path
inp_full_name = inp_dir & inp_file_name

Dim wdApp As New Word.Application, wdDoc As Word.Document
Set wdDoc = wdApp.Documents.Open(Filename:=inp_full_name, ReadOnly:=True, _
    AddToRecentFiles:=False, Format:=wdOpenFormatAuto, Visible:=False)
  
Dim pdf_Filename As String
pdf_Filename = Replace(inp_full_name, ".txt", ".pdf")
Debug.Print pdf_Filename 
wdDoc.SaveAs2 Filename:=pdf_Filename, FileFormat:=wdFormatPDF, AddToRecentFiles:=False
wdDoc.Close False

wdApp.Quit
  • Related