Updating file properties with vb

Its just plain text and won’t do any fancy formatting. Why assign it to the t Active variable instead of just using it directly? Instead of having to remember what rng is pointing to, we can look at the variable name and know at a glance.

As you will see below it is very similar to the ‘Refresh’ option, but this time we are seeing if they want a new filename and if they want to add todays date to it.If they do, we prepare the new filename in advance, as we need to also check whether we can write to that file.' If the line says YES in the first column it will action it, otherwise it will ignore it. Value If t Active = "YES" Then ' We have a line marked as active, so make a note of everything else ' on the line using simple offsets from the first column. The Is File Open function at the end of the module takes care of that.' Lets set up a bunch of variables ahead of time so Excel knows how to best handle them Dim mastertable As Range, rng As Range Dim current Workbook As String, wb1 As Workbook, wb2 As Workbook Dim t Active As String, t Filename As String, t Bex Ao As String Dim t Action As String, t New Filename As String, t Add Date As String Dim t Extension As String, t Email Type As String, t Email Display Send As String Dim t Email Address As String, t Email Subject As String, t Email Body As String Dim final Filename As String ' Lets speed things up and make the screen not flicker turn_off_screen_updating ' Loop through the first column of our named table and check ' if its marked with a YES saying it is an active row Set mastertable = Range(mastertablename) For Each rng In mastertable. ' Then assign it to a more recognisable variable name to make things ' easier to understand later on in our code t Filename = rng. If someone is in the file, we won’t be able to save it after refreshing, so we shouldn’t waste time refreshing it in the first place.When we then call the Refresh_Workbook routine and supply extra fields containing the new filename and the new filetype.

' ***************** ' REFRESH & SAVE AS ' ***************** Case "Refresh & Save As" ' They want to refresh the file but save it as a new name/format ' Build our new filename first so we can check to see if it can be saved to ' If they want to add the current date to the new filename we handle that here too If t Add Date = "YES" Then final Filename = t New Filename & Format(Date, "_yyyy_mm_dd") & "." & t Extension Else final Filename = t New Filename & "." & t Extension End If ' First we need to make sure we can write to the NEW location, so check if its already opened by someone else.

'********************************************************* ' Lets define some constants that we can refer to later on '********************************************************* ' What table do we want to run through when processing the files Public Const mastertablename = "MASTERDATA" ' Set up our BW server details so we can use it with BEX and AO later on Public Const bw_client = "your bw client number" Public Const bw_user = "your bw username" Public Const bw_password = "your bw password" Public Const bw_language = "EN" Public Const bw_system = "your bw system number" Public Const bw_server = "your bw server address" ' Define the path to the BEXAnalyzer Addin for Excel Public Const BEXAnalyzer_Path = "C:\Program Files (x86)\Common Files\SAP Shared\BW\BEx Analyzer.xla" ' If you are wanting to email the files via Gmail (or Yahoo or pretty much any server), ' we make use of the free piece of software Swith Mail. ' https:// ' For outlook we assume the user already has Outlook installed on their machine Public Const swithmail_path = "C:\temp\Swith Mail.exe" ' To use Gmail (with Swith Mail) we need to supply the relevant credentials Public Const gmail_user = "[email protected]" Public Const gmail_pass = "yourgmailpassword" So that’s the global settings sorted out. ' The Is File Open function later on in this module does exactly that.

Next is the main routine that loops through our MASTERTABLE. This is what we are trying to achieve with the code : It may look slightly complex but it’s reasonably straight forward. If Is File Open(t Filename) = True Then Msg Box "The file : " & t Filename & " is already in use by someone else, so we can't update and save it." Else ' Make a note of the workbook we are currently in so we can come back to it later current Workbook = Active Workbook.

Ideally add your own module so you can export and reuse the code in other files. ' If the length is longer than 0, then it found the file.

We are calling the module BWRefresh, though you don’t really need to name it anything. The template at the end of this blog will have the full code, but the basics of it will also be explained below. If Len(Dir(t Filename)) 0 Then ' So we have a file to use that actually exists.

In my previous blog we used VBscript and VBA macros to update BW workbooks in BEX and Analysis for Office.