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) objExcel.ActiveWorkBook.refreshall for each objworksheet in Array(objworkbook.Worksheets("REPORT")) objWorksheet.Cells(2, 13) = "Jan-" objWorksheet.Cells(2, 14) = "Jun" objWorksheet.Cells(2, 15) = "2016" Next objExcel.ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, _ Filename:=" ORIGINFOLDER_EXCEL ", _ ActiveSheet.Range("J1").Value & ".pdf", _ OpenAfterPublish:=False objExcel.ActiveWorkbook.Save() objExcel.ActiveWorkbook.Close Next objFSO.MoveFile " ORIGINFOLDER_PDF " DESTINATIONFOLDER_PDF " Next 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" .Send End With MsgBox "Automatic delivery successfully finished", 64 Application.DisplayAlerts = False Set Mail_Object = Nothing End Sub