ljent
First launch the Visual Basic editor in the template file you have created
by pressing the "Alt" key and the "F11" key at the same time. -Alt+F11
In the upper left hand corner you will see the VBAProject then below that
Microsoft Excel Objects. You should see one named "ThisWorkbook" double
click it.
Here are some assumptions I made:
1. The data in the style worksheet is a vertilce structure.
2. The data in the style worksheet starts in Row 2 of column A.
3. All the data to copy to the log file in column B (verticle structure)
4. The sheet tab is named Sheet1
5. The cell A1 in the file the user is saving is open to use.
So how it will work is when the workbook gets saved for the first time the
file "Spring2010.xlsx" will be written to, saved and then closed. The cell
in range A1 will display the word logged to show it has been logged and to
skip it each save after.
Make sure to change the path for the location of the log file. I noted in
the code where that needs to be done.
I hope this is not to confusing.
Paste the code that is between the symbols # below
'############################
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
Call Logit
End Sub
Function Logit()
Dim strData(6) As String
If Sheets("Sheet1").Range("A1").Value = "Logged" Then Exit Function 'exit
function if sheet has already been logged
Application.ScreenUpdating = False
'Capture information to log
strData(1) = Sheets("Sheet1").Range("B2").Value 'Style data
strData(2) = Sheets("Sheet1").Range("B3").Value 'Description data
strData(3) = Sheets("Sheet1").Range("B4").Value 'Fabric data
strData(4) = Sheets("Sheet1").Range("B5").Value 'Fabric Content data
strData(5) = Sheets("Sheet1").Range("B6").Value 'Factory data
strData(6) = ActiveWorkbook.FullName 'File name and path to it
'CHANGE THE PATH IN THE LINE BELOW TO MATCH WHERE YOUR LOG FILE IS ON YOUR
NETWORK.
Workbooks.Open ("C:\log\Spring2010.xlsx") ' path to log file
Range("a1").Select
'find last cell and put down the data
If ActiveCell.Offset(1, 0) = "" Then
ActiveCell.Offset(0, 0) = strData(6)
ActiveCell.Offset(0, 1) = strData(1)
ActiveCell.Offset(0, 2) = strData(2)
ActiveCell.Offset(0, 3) = strData(3)
ActiveCell.Offset(0, 4) = strData(4)
ActiveCell.Offset(0, 5) = strData(5)
Else
'paste the log data
Selection.End(xlDown).Select
ActiveCell.Offset(1, 0) = strData(6)
ActiveCell.Offset(1, 1) = strData(1)
ActiveCell.Offset(1, 2) = strData(2)
ActiveCell.Offset(1, 3) = strData(3)
ActiveCell.Offset(1, 4) = strData(4)
ActiveCell.Offset(1, 5) = strData(5)
End If
ActiveWorkbook.Save 'Save
ActiveWorkbook.Close 'Close log
Sheets("Sheet1").Range("A1") = "Logged" 'Note file has been logged
ActiveWorkbook.Save
Application.ScreenUpdating = True
End Function
'############################