I’ve been combing the forum for this answer. I know I keep reading about “You can use the Visual Basic editor in Excel or Winword before code it to Lotus…”
I can, but when it gives me code and then I have no idea how to interpret it or tweak it, it’s not much help.
Is there a place that talks in depth about using code that can update an Excel Object?
Like one item I tried to do was pulled in an existing Excel file, added data where the blank rows were, saved it and then pushed it into a Notes Rich Text field.
When I needed to add a row on line 40 and push all the already written data down a line, I could not figure it out.
I went to the Excel Macro writer and it gave me this:
Rows(“40:40”).Select
Selection.Insert Shift:=xlDown
Application.Goto Reference:="test"
I tried to add that to my code to no avail. Including the
Selection.Insert Shift:= -4121
Where is a good resource for understanding how to code for Excel in Notes??? please help! Thanks in advance for any help!
Here is my current code
Sub Postopen(Source As Notesuidocument)
'Here if the document is new, I will prompt the user for the Year and location of the report.
If source.IsNewDoc Then
On Error Goto ErrHandler
Dim ws As New NotesUIWorkspace
Dim s As New NotesSession
Dim db As NotesDatabase
Set db = s.CurrentDatabase
Dim Excel As Variant, Excel2 As Variant, repid As String
Dim thisdoc As NotesDocument
Set thisdoc = source.Document
Dim year1 As String, store As Variant, cmd As Variant, storechoices As Variant, city As String, state As String, street As String
'Ask the creator for the Location and Year this report should be for so we can collect information
year1 = Inputbox$("What year should this report be for?","Year","2008")
'Ask for the Store/Location
repid = source.FieldGetText("ISO_RepID")
cmd = {@DbColumn("":"NoCache"; "} + repid + {"; "LByL"; 1)}
storechoices = Evaluate(cmd)
store = ws.Prompt( PROMPT_OKCANCELLIST, "Store/Location", "Choose the Store/Location for report", "", storechoices )
Call source.FieldSetText ( "Year", year1)
If store <> "" Then
Call source.FieldSetText ( "LocName", store)
Else
Exit Sub
End If
'Now go find the matching information for the City and State
Dim SysInfodb As New NotesDatabase( "", "" ), flag As Variant
flag = SysInfodb.OpenByReplicaID( db.server, Trim(Replace(source.FieldGetText("ISO_RepID"), ":", "", 1, 1, 0)))
If flag = False Then Exit Sub
Dim Sysdoc As NotesDocument
Set Sysview = SysInfodb.GetView( "LByL2" )
If store <> "" Then Set Sysdoc = Sysview.GetDocumentByKey (store )
If Not Sysdoc Is Nothing Then
Call source.FieldSetText ( "LocCity", Sysdoc.City(0))
City = Sysdoc.City(0)
Call source.FieldSetText ( "LocState", Sysdoc.State(0))
State = Sysdoc.State(0)
Street = Sysdoc.Address(0)
End If
'Grab the Profile document so we can use the proper Excel Template
Dim keywordview As NotesView
Set keywordview = db.GetView("KWs")
Dim Osha300doc As NotesDocument
Set Osha300doc = keywordview.GetDocumentByKey( "Excel_300" )
If Osha300doc Is Nothing Then Exit Sub
'Set up the Excel application
Dim xlApp As Variant
Dim xlSheet As Variant
Dim filenameM As Variant
Dim STRfilenameM As String
Dim object As NotesEmbeddedObject
'...get the embedded template...
Set object = Osha300doc.GetAttachment( "OSHA300blanckformversion1-1-04.xls" )
Dim fpath As String, fpathcheck As String
fpath = "C:\temp\Osha300e.xls"
fpathcheck = Dir$(fpath, 0)
If fpathcheck <> "" Then Kill fpath
Call object.extractfile(fpath)
Set Excel = CreateObject( "Excel.Application" )
Excel.DisplayAlerts = False
Excel.Visible = True'False '// Don't display the Excel window
Excel.Workbooks.Open fpath
If Dir$( fpath, 0 ) = "" Then Exit Sub
Set xlWorkbook = Excel.ActiveWorkbook
Set xlsheet = xlWorkbook.ActiveSheet
Excel.Sheets(1).Activate
'Write the Shared information, Year, Store, State, City
Excel.ActiveSheet.Cells(3,14).Value = year1
Dim estname As Variant
cmd = {@DbLookup("":"NoCache"; ""; "KWs"; "Establishment_Name";2)}
estname = Evaluate(cmd)
Excel.ActiveSheet.Cells(11,11).Value = estname
Excel.ActiveSheet.Cells(12,14).Value = State
Excel.ActiveSheet.Cells(12,10).Value = City
'Now go and get a collection of all documents for this report
Dim injcoll As NotesViewEntryCollection, injdoc As NotesDocument
Dim injview As NotesView
Dim entry As NotesViewEntry, entrytwo As NotesViewEntry
Set injview = db.GetView("AllByLoc")
'Set totals
Dim gt As Integer, ht As Integer, it As Integer, jt As Integer, kt As Integer, lt As Integer, m1t As Integer
Dim m2t As Integer, m3t As Integer, m4t As Integer, m5t As Integer, m6t As Integer
gt = ht=it=jt=kt=lt=m1t=m2t=m3t=m4t=m5t=m6t = 0
Set injcoll = injview.GetAllEntriesByKey(store, False)
If injcoll.count > 0 Then
'Set up the arrays
Dim x As Integer
x = 1
Dim A() As String, b() As String, c() As String, d() As String, e() As String, f() As String, g() As String, h() As String, i() As String
Dim j() As String, k() As String, l() As String, m() As String
Set entry = injcoll.GetFirstEntry()
If Not entry Is Nothing Then Set injdoc = entry.Document
Do While Not injdoc Is Nothing
'Check the year to see if it matches with the Report year
If Right(Cstr(Injdoc.IncEmpIncDat(0)), 4) = year1 Then
Print "Yep this matches"
'Write the amounts that we needs to store here
Redim Preserve A(x)
Redim Preserve B(x)
Redim Preserve C(x)
Redim Preserve D(x)
Redim Preserve E(x)
Redim Preserve F(x)
Redim Preserve G(x)
Redim Preserve H(x)
Redim Preserve I(x)
Redim Preserve J(x)
Redim Preserve K(x)
Redim Preserve L(x)
Redim Preserve M(x)
A(x) = Injdoc.IncAccRefNum(0)
B(x) = Injdoc.IncEmpName(0)
C(x) = Injdoc.IncEmpJobTitle(0)
D(x) = Injdoc.IncEmpIncDat(0)
E(x) = Injdoc.Acc1(0)
F(x) = Injdoc.Acc5(0)
If Injdoc.EplyMedAtt_1_2_1_1(0) = "Yes" Then
G(x) = "X"
gt = gt + 1
End If
If Injdoc.EplyMedAtt_1_2(0) = "Yes" Then
H(x) = "X"
ht = ht + 1
End If
If Injdoc.EplyMofduty(0) = "Yes" Then
I(x) = "X"
L(x) = Cstr(Injdoc.InjDays(0))
it = it + 1
lt = lt + Injdoc.InjDays(0)
Else
If Injdoc.EplyMedAtt_1_1(0) = "Yes" Then
J(x) = "X"
K(x) = Cstr(Injdoc.InjDays(0))
jt = jt + 1
kt = kt + Injdoc.InjDays(0)
End If
End If
M(x) = Injdoc.InjuryType(0)
If m(x) <> "" Then
Select Case Ucase(m(x))
Case "INJURY" : m1t = m1t + 1
Case "SKIN DISORDER" : m2t = m2t + 1
Case "RESPIRATORY CONDITION" : m3t = m3t + 1
Case "POISONING" : m4t = m4t + 1
Case "HEARING LOSS" : m5t = m5t + 1
Case "ALL OTHER ILLNESSES" : m6t = m6t + 1
End Select
End If
Else
Print "no match"
End If
Set entrytwo = injcoll.GetNextEntry(entry)
Set entry = entrytwo
If Not entry Is Nothing Then
Set injdoc = entry.Document
Else
Set injdoc = Nothing
End If
x = x + 1
Loop
End If
'Now we have all the information, write it to Excel
Dim y As Integer, y1 As Integer, z As Integer 'z will store the information on which line were on
Z = 25
y1 = 0
For y = 1 To x-1
If y > 14 Then
y1 = y1 + 1
'Excel.ActiveSheet.Rows("38:1").Select
'.Rows(38).Insert
'xlsheet.Range("a40:a49").copy
'xlsheet.Range("a41:a49").paste
'Insert new line to edit
'.Range("A" + Cstr(39)).Insert = -4121
End If
Excel.ActiveSheet.Cells(z,1).Value = A(y)
Excel.ActiveSheet.Cells(z,2).Value = B(y)
Excel.ActiveSheet.Cells(z,3).Value = C(y)
Excel.ActiveSheet.Cells(z,4).Value = D(y)
Excel.ActiveSheet.Cells(z,5).Value = E(y)
Excel.ActiveSheet.Cells(z,6).Value = F(y)
Excel.ActiveSheet.Cells(z,7).Value = G(y)
Excel.ActiveSheet.Cells(z,8).Value = H(y)
Excel.ActiveSheet.Cells(z,9).Value = I(y)
Excel.ActiveSheet.Cells(z,10).Value = J(y)
Excel.ActiveSheet.Cells(z,11).Value = K(y)
Excel.ActiveSheet.Cells(z,12).Value = L(y)
If m(Y) <> "" Then
Select Case Ucase(m(Y))
Case "INJURY" : Excel.ActiveSheet.Cells(z,13).Value = "X"
Case "SKIN DISORDER" : Excel.ActiveSheet.Cells(z,14).Value = "X"
Case "RESPIRATORY CONDITION" : Excel.ActiveSheet.Cells(z,15).Value = "X"
Case "POISONING" : Excel.ActiveSheet.Cells(z,16).Value = "X"
Case "HEARING LOSS" : Excel.ActiveSheet.Cells(z,17).Value = "X"
Case "ALL OTHER ILLNESSES" : Excel.ActiveSheet.Cells(z,18).Value = "X"
End Select
End If
z = z + 1
Next
Z = 40 + y1
'Now copy the totals all on row 40
Excel.ActiveSheet.Cells(z,7).Value = GT
Excel.ActiveSheet.Cells(z,8).Value = HT
Excel.ActiveSheet.Cells(z,9).Value = IT
Excel.ActiveSheet.Cells(z,10).Value = JT
Excel.ActiveSheet.Cells(z,11).Value = KT
Excel.ActiveSheet.Cells(z,12).Value = LT
Excel.ActiveSheet.Cells(z,13).Value = M1T
Excel.ActiveSheet.Cells(z,14).Value = M2T
Excel.ActiveSheet.Cells(z,15).Value = M3T
Excel.ActiveSheet.Cells(z,16).Value = M4T
Excel.ActiveSheet.Cells(z,17).Value = M5T
Excel.ActiveSheet.Cells(z,18).Value = M6T
'Copy the Totals back to the doc
Call source.FieldSetText ( "DeathTotal", Cstr(gt))
Call source.FieldSetText ( "DaysAwayTotal", Cstr(ht))
Call source.FieldSetText ( "JobTransTotal", Cstr(it))
Call source.FieldSetText ( "RemOthTotal", Cstr(jt))
Call source.FieldSetText ( "NumDaysAwayTotal", Cstr(kt))
Call source.FieldSetText ( "NumDaysTransTotal", Cstr(lt))
Call source.FieldSetText ( "InjuryTotal", Cstr(m1t))
Call source.FieldSetText ( "SkinTotal", Cstr(m2t))
Call source.FieldSetText ( "RespTotal", Cstr(m3t))
Call source.FieldSetText ( "PoisonTotal", Cstr(m4t))
Call source.FieldSetText ( "HearLossTotal", Cstr(m5t))
Call source.FieldSetText ( "OtherInjTotal", Cstr(m6t))
'Now Fill out the second sheet Osha Form 300A
Excel.Sheets(2).Activate
Excel.ActiveSheet.Cells(15,23).Value = estname
Excel.ActiveSheet.Cells(17,18).Value = Street
Excel.ActiveSheet.Cells(19,30).Value = State
Excel.ActiveSheet.Cells(19,18).Value = City
Excel.ActiveSheet.Cells(25,1).Value = gt 'Total number of deaths a25
Excel.ActiveSheet.Cells(25,3).Value = ht 'Total number of cases w/days away from work c25
Excel.ActiveSheet.Cells(25,5).Value = it 'Total number of cases w/restriction or transfer e25
Excel.ActiveSheet.Cells(25,7).Value = jt 'Total number of other recordable cases g25
Excel.ActiveSheet.Cells(34,1).Value = kt 'Total number of days away from work a34
Excel.ActiveSheet.Cells(34,5).Value = lt 'Total number of days on restriction/transfer e34
Excel.ActiveSheet.Cells(42,3).Value = m1t 'Total m1 c42
Excel.ActiveSheet.Cells(43,3).Value = m2t 'Total m2 c43
Excel.ActiveSheet.Cells(44,3).Value = m3t 'Total m3 c44
Excel.ActiveSheet.Cells(42,7).Value = m4t 'Total m4 g42
Excel.ActiveSheet.Cells(43,7).Value = m5t 'Total m5 g43
Excel.ActiveSheet.Cells(44,7).Value = m6t 'Total m6 g44
Excel.Sheets(1).Activate 'Set the focus back to Workbook 1
Call source.GoToField( "Report" )
Call source.CreateObject("Report", "", fpath)
Call source.Refresh(True)
xlWorkbook.Close True '// Close the Excel file with saving (we made changes)
excel.DisplayAlerts = True
Call Excel.Quit()
Set Excel = Nothing
Kill fpath
End If
Exit Sub
ErrHandler:
message = "Error on line: " & Erl() & " " & db.Server & db.FilePath & " " & Str(Err()) & " " & Error()
Print message
Call Excel.Quit()
Set Excel = Nothing
Kill fpath
Exit Sub
End Sub