CODEWITHSUNDEEP
excelvba
user12055387
user12055387

Reputation:

Call a VBA macro several times with different arguments

So I have written a a function that will open a draft word document, populate it, and save it as a PDF. The thing is, I need to be able to use this function to open different word documents and I don't want to have to copy and edit this function 20 times. Instead I would like to be able to just call the function and pass it the name of the doc it needs to open or something like that.

Currently my macro is something like this. It works fine, and if I copy it and change the details of the file, it works. But I don't want to copy and paste this function over and over. Is there a quicker or easier way I can do this for multiple other files?

Sub macro()
    Set wordapp = CreateObject("word.Application")
    wordapp.Documents.Open "C:\Users\Matt\Desktop\blah\blah\blah.docx"
    wordapp.Visible = True
    If wordapp.ActiveDocument.Tables.Count > 0 Then
        wordapp.ActiveDocument.Tables(1).Cell(1, 2).Select
        wordapp.ActiveDocument.Tables(1).Cell(1, 2).Range.Text = "No Movements"
        wordapp.ActiveDocument.Tables(1).Cell(1, 2).VerticalAlignment = 1
        
        wordapp.ActiveDocument.Tables(1).Cell(2, 2).Select
        Dim LDate As String
        LDate = Date
        wordapp.ActiveDocument.Tables(1).Cell(2, 2).Range.Text = LDate
        wordapp.ActiveDocument.Tables(1).Cell(2, 2).VerticalAlignment = 1
        
        
        wordapp.ActiveDocument.Tables(1).Cell(3, 2).Select
        Dim YDate As String
        If Weekday(Date, vbMonday) = 1 Then
        YDate = Date - 3
        Else
        YDate = Date - 1
        End If
        wordapp.ActiveDocument.Tables(1).Cell(3, 2).Range.Text = YDate
        wordapp.ActiveDocument.Tables(1).Cell(3, 2).VerticalAlignment = 1
        
        wordapp.ActiveDocument.Tables(1).Cell(4, 2).Select
        wordapp.ActiveDocument.Tables(1).Cell(4, 2).Range.Text = "N/A"
        wordapp.ActiveDocument.Tables(1).Cell(4, 2).VerticalAlignment = 1
        wordapp.ActiveDocument.Tables(1).Cell(5, 2).Select
        wordapp.ActiveDocument.Tables(1).Cell(5, 2).Range.Text = "N/A"
        wordapp.ActiveDocument.Tables(1).Cell(5, 2).VerticalAlignment = 1
        wordapp.ActiveDocument.Tables(1).Cell(6, 2).Select
        wordapp.ActiveDocument.Tables(1).Cell(6, 2).Range.Text = "N/A"
        wordapp.ActiveDocument.Tables(1).Cell(6, 2).VerticalAlignment = 1
        wordapp.ActiveDocument.Tables(1).Cell(7, 2).Select
        wordapp.ActiveDocument.Tables(1).Cell(7, 2).Range.Text = "N/A"
        wordapp.ActiveDocument.Tables(1).Cell(7, 2).VerticalAlignment = 1
        wordapp.ActiveDocument.Tables(1).Cell(8, 2).Select
        wordapp.ActiveDocument.Tables(1).Cell(8, 2).Range.Text = "N/A"
        wordapp.ActiveDocument.Tables(1).Cell(8, 2).VerticalAlignment = 1
    End If
    wordapp.ActiveDocument.SaveAs Filename:="C:\Users\Matt\Desktop\blah\blah\blah\blah_" & Format(Now(), "yymmdd")
    Call ConvertWordToPDF
End Sub

Please forgive me if this code is a bit rubbish but I'm still learning.

Upvotes: 1

Views: 209

Answers (1)

QHarr
QHarr

Reputation: 84465

Pull out the variable parts and pass ByVal as arguments within sub signature. Call you sub something more meaningful. You might want additional arguments e.g. for "No Movements".

Public Sub test()

    macro "C:\Users\Matt\Desktop\blah\blah\blah.docx", "C:\Users\Matt\Desktop\blah\blah\blah\blah_"


End Sub

Public Sub macro(ByVal openFile As String, ByVal outFile As String)
    Set wordapp = CreateObject("word.Application")
    wordapp.Documents.Open openFile
    wordapp.Visible = True
    If wordapp.ActiveDocument.Tables.Count > 0 Then
        wordapp.ActiveDocument.Tables(1).Cell(1, 2).Select
        wordapp.ActiveDocument.Tables(1).Cell(1, 2).Range.Text = "No Movements"
        wordapp.ActiveDocument.Tables(1).Cell(1, 2).VerticalAlignment = 1
        
        wordapp.ActiveDocument.Tables(1).Cell(2, 2).Select
        Dim LDate As String
        LDate = Date
        wordapp.ActiveDocument.Tables(1).Cell(2, 2).Range.Text = LDate
        wordapp.ActiveDocument.Tables(1).Cell(2, 2).VerticalAlignment = 1
        wordapp.ActiveDocument.Tables(1).Cell(3, 2).Select
        
        Dim YDate As String

        If Weekday(Date, vbMonday) = 1 Then
            YDate = Date - 3
        Else
            YDate = Date - 1
        End If

        wordapp.ActiveDocument.Tables(1).Cell(3, 2).Range.Text = YDate
        wordapp.ActiveDocument.Tables(1).Cell(3, 2).VerticalAlignment = 1
        
        wordapp.ActiveDocument.Tables(1).Cell(4, 2).Select
        wordapp.ActiveDocument.Tables(1).Cell(4, 2).Range.Text = "N/A"
        wordapp.ActiveDocument.Tables(1).Cell(4, 2).VerticalAlignment = 1
        wordapp.ActiveDocument.Tables(1).Cell(5, 2).Select
        wordapp.ActiveDocument.Tables(1).Cell(5, 2).Range.Text = "N/A"
        wordapp.ActiveDocument.Tables(1).Cell(5, 2).VerticalAlignment = 1
        wordapp.ActiveDocument.Tables(1).Cell(6, 2).Select
        wordapp.ActiveDocument.Tables(1).Cell(6, 2).Range.Text = "N/A"
        wordapp.ActiveDocument.Tables(1).Cell(6, 2).VerticalAlignment = 1
        wordapp.ActiveDocument.Tables(1).Cell(7, 2).Select
        wordapp.ActiveDocument.Tables(1).Cell(7, 2).Range.Text = "N/A"
        wordapp.ActiveDocument.Tables(1).Cell(7, 2).VerticalAlignment = 1
        wordapp.ActiveDocument.Tables(1).Cell(8, 2).Select
        wordapp.ActiveDocument.Tables(1).Cell(8, 2).Range.Text = "N/A"
        wordapp.ActiveDocument.Tables(1).Cell(8, 2).VerticalAlignment = 1
    End If
    
    wordapp.ActiveDocument.SaveAs Filename:=outFile & Format(Now(), "yymmdd")
    
    ConvertWordToPDF
End Sub

Upvotes: 1

Related Questions