(Solved) – vba script with several steps – beginner problems with refresh, convert, publish in one step


I would like to learn about vba as I am trying to optimize my workpatterns. At the moment I am trying to write a script that can do the following:

  • Access folder with about 250 excel files (standardised but different customers in each)
  • refresh data in excel (as I also feed the cube behind I know when new information is loaded into the system and Excel can refresh)
  • Save Excel file
  • Convert Sheet one to pdf and keep name of Excel (e.g: Excel= MeCompany then pdf should be MeCompany.pdf)
  • transfer pdf to destinationfolder (not copy, but a real transfer)
  • rinse and repeat for all 250 reports
  • then write 1 email via Outlook to several people and inform them that the reports are now all available

I thought about using vba since about 3 months and started to look into it more 2 weeks ago. This is what I have now – and I am sure that I made a bunch of mistakes here….can you maybe give me a hint / suggestion where I made mistakes?
I am clearly a beginner and every comment would be a great help for me!!

    Sub Main()

    Set objFSO = CreateObject("Scripting.FileSystemObject")
    Set objFolder = objFSO.GetFolder("ORIGINFOLDER_EXCEL”)
    Set arrFiles = objFolder.Files
For Each strFolderName In arrFiles
Set objExcel = CreateObject("Excel.Application") 
Set objWorkbook = objExcel.Workbooks.Open(strFolderName)
for each objworksheet in Array(objworkbook.Worksheets("REPORT"))
    objWorksheet.Cells(2, 13) = "Jan-"
    objWorksheet.Cells(2, 14) = "Jun"
    objWorksheet.Cells(2, 15) = "2016"

objExcel.ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, _
    Filename:=" ORIGINFOLDER_EXCEL ", _
    ActiveSheet.Range("J1").Value & ".pdf", _


Set Mail_Object = CreateObject("Outlook.Application")
    With Mail_Object.CreateItem(o)
        .Subject = "Accountreporting data 062016"
        .To = "email1,email2,email3,…."
        .CC = " email1,email2,email3,….""
        .Body = "Dear recipient," & Chr(13) "data drive has been processed and finished. The reports are now available at:" & Chr(13) & Chr(13) & DESTINATIONFOLDER_PDF" & Chr(13) & Chr(13) & "Kind regards," & Chr(13) & "BST" & Chr(13) & "Departmentname" 
End With
    MsgBox "Automatic delivery successfully finished", 64
    Application.DisplayAlerts = False
Set Mail_Object = Nothing

End Sub

Leave a Reply

Your email address will not be published. Required fields are marked *