I am currently working on a project to create multiple manifests that can have only certain values in particular cells (asset code and serial number). My manifest Template is completed and it contains a bunch of VBA coding to prevent people (engineers at my work in this case) from entering incorrect data.
The way I have done this is with a bit of inbuilt VBA based Data Validation rules and checks. There are also Macros running txt file outputs and Outlook Emails etc. The cells which contain asset codes and serial numbers are controlled by these DataValidation rules which compare the data to three lists in a second Sheet of the the workbook.
In total, there will be approximately 100 different workbooks, each with its own unique manifest on Sheet1, while Sheet2 is identical among the 100 or so workbooks... As mentioned, Sheet2 has three lists:
- a list of the possible Asset Codes in column A (about 1500 entries)
- a list of asset codes in the location in column F (about 26000 entries)
- a list of serial numbers in the location in Column G (about 26000 entries)
Every Manifest Workbook will be saved onto Sharepoint so that manifests can be edited by individuals without too much conflict with checking in and out. In addition, most access to these manifests will be performed by engineers working in an offshore environment with rather slow internet, hence the need to keep the files separate and as small as possible. Also, the Sheet2 which contains the Valid Asset Data will not have active links as alot of the time the Manifests will be accessed without internet connection at all (downloaded and handled offline).
The problem is that the Asset Codes, Assets in the location and Serial Numbers are regularly being updated and are changing (tracked in an external system that outputs txt/xls dump files). Lets say a new Asset comes in, this would need to be updated in each of the manifest workbook's Sheet2 so that they could potentially have that new asset in that manifest Workbook.
The way I plan to do this is by having one additional MasterWorkbook on Sharepoint that only has a Sheet2. What I want is for the Manifest Workbooks to update their Sheet2 to match that of the MasterWorkbook's Sheet2.
I plan for this to be done with a VBA Macro Button... i.e. "UPDATE Sheet2" ... Every once in-a-while, when the user has a good internet connection, they can open their respective Manifest for Editing and click this "Update Sheet2" Button so that they have all the correct lists.
When "Update Sheet2" Button is pressed on a Manifest Workbook, it reads the Sheet2 of the MasterWorkbook on Sharepoint (without opening it) and updates its own Sheet2 to match accordingly.
I have found the code to be able to do this when the Manifest Workbook and MasterWorkbook are both on a local drive, however I have not been able to find the way of referencing a Workbook on Sharepoint.
Essentially the only VBA coding I have learned is from the last few weeks by reading these forums and I have got this far, but now I am stuck. Below is what I have in mind, but it does not work (adapted from http://j-walk.com/ss/excel/tips/tip82.htm)... I keep getting 'Bad File Name or Number Error' at the following line: If Dir(FilePath & FileName) = Empty Then
Please let me know what you think is going wrong... Obviously it is not liking the address reference for some reason... I also looked at the DAO method and also a couple of other methods... But this seemed to be easiest and I would love for this to work... Currently I'm just usign MySite for checking, it will eventually be a Sharepoint site such as: "https://teamspace.slb.com/sites/INMWL/MWL/MH/Shared Documents/"
Full code in its own Module:
Option Explicit
Sub GetDataDemo()
Dim FilePath$, Row&, Column&, Address$, Address1$
'change constants & FilePath below to suit
'***************************************
Const FileName$ = "Book2.xlsm"
Const SheetName$ = "Sheet1"
Const NumRows& = 1500
Const NumColumns& = 7
Address1 = ActiveWorkbook.Path
Cells(3, 5) = Address1
FilePath = ActiveWorkbook.Path & "/"
'''' tried: https://mysite.slb.com/personalsite/vsidebottom/Book2/Shared Documents
'***************************************
''''
DoEvents
Application.ScreenUpdating = False
If Dir(FilePath & FileName) = Empty Then
MsgBox "The file " & FileName & " was not found", , "File Doesn't Exist"
Exit Sub
Else
MsgBox "File Found!"
End If
For Row = 1 To NumRows
For Column = 1 To NumColumns
Address = Cells(Row, Column).Address
Cells(Row, Column) = GetData(FilePath, FileName, SheetName, Address)
Columns.AutoFit
Next Column
Next Row
ActiveWindow.DisplayZeros = False
End Sub
Private Function GetData(Path, File, Sheet, Address)
Dim Data$
Data = "'" & Path & "[" & File & "]" & Sheet & "'!" & _
Range(Address).Range("A1").Address(, , xlR1C1)
GetData = ExecuteExcel4Macro(Data)
End Function
