· 8 years ago · Dec 06, 2017, 02:06 PM
1sub sync_catalog
2
3rem define variables
4dim document as object
5dim dispatcher as object
6
7rem get access to the document
8document = ThisComponent.CurrentController.Frame
9dispatcher = createUnoService("com.sun.star.frame.DispatchHelper")
10If ThisComponent.Sheets().hasByName( "temp" ) Then
11 Msgbox "A sheet temp already exists. Please delete it and repeat!"
12Else
13 ThisComponent.Sheets().insertNewByName( "temp" , 2 )
14
15rem ----------------------------------------------------------------------
16dim args0(0) as new com.sun.star.beans.PropertyValue
17args0(0).Name = "Nr"
18args0(0).Value = 2
19dispatcher.executeDispatch(document, ".uno:JumpToTable", "", 0, args0())
20Dim oDoc As Object
21Dim oSelection
22Dim oAddress As new com.sun.star.table.CellAddress
23Dim url as String
24url= "http://growit.gr/wp-content/plugins/printer.php"
25oDoc = ThisComponent
26oSelection = oDoc.getCurrentController().getSelection()
27if oSelection.supportsService("com.sun.star.sheet.SheetCell") then
28 oAddress = oSelection.getCellAddress()
29 oDoc.AreaLinks.insertAtPosition(oAddress,url,"HTML_all","calc_HTML_WebQuery","0 0")
30else
31 Msgbox "Import from url Error"
32end If
33 xSheet = ThisComponent.Sheets(1)
34oCell = xSheet.getCellByPosition(0,0)
35if( oCell.Formula = "" ) then
36 Msgbox "No data exported. Please run SQL-Executioner first"
37 ThisComponent.getSheets().removeByName("temp")
38else if ( oCell.string = "Empty" ) then
39 Msgbox "Error"
40 ThisComponent.getSheets().removeByName("temp")
41else
42
43rem REPLACE 4a68 with / and . with , and 67a5 with ,
44dim args1(17) as new com.sun.star.beans.PropertyValue
45args1(0).Name = "SearchItem.StyleFamily"
46args1(0).Value = 2
47args1(1).Name = "SearchItem.CellType"
48args1(1).Value = 0
49args1(2).Name = "SearchItem.RowDirection"
50args1(2).Value = true
51args1(3).Name = "SearchItem.AllTables"
52args1(3).Value = false
53args1(4).Name = "SearchItem.Backward"
54args1(4).Value = false
55args1(5).Name = "SearchItem.Pattern"
56args1(5).Value = false
57args1(6).Name = "SearchItem.Content"
58args1(6).Value = false
59args1(7).Name = "SearchItem.AsianOptions"
60args1(7).Value = false
61args1(8).Name = "SearchItem.AlgorithmType"
62args1(8).Value = 0
63args1(9).Name = "SearchItem.SearchFlags"
64args1(9).Value = 65536
65args1(10).Name = "SearchItem.SearchString"
66args1(10).Value = "4a68"
67args1(11).Name = "SearchItem.ReplaceString"
68args1(11).Value = "/"
69args1(12).Name = "SearchItem.Locale"
70args1(12).Value = 255
71args1(13).Name = "SearchItem.ChangedChars"
72args1(13).Value = 2
73args1(14).Name = "SearchItem.DeletedChars"
74args1(14).Value = 2
75args1(15).Name = "SearchItem.InsertedChars"
76args1(15).Value = 2
77args1(16).Name = "SearchItem.TransliterateFlags"
78args1(16).Value = 1280
79args1(17).Name = "SearchItem.Command"
80args1(17).Value = 3
81dispatcher.executeDispatch(document, ".uno:ExecuteSearch", "", 0, args1())
82args1(10).Value = "."
83args1(11).Value = ","
84dispatcher.executeDispatch(document, ".uno:ExecuteSearch", "", 0, args1())
85args1(10).Value = "67a5"
86args1(11).Value = ","
87rem dispatcher.executeDispatch(document, ".uno:ExecuteSearch", "", 0, args1())
88
89rem jump to first table to copy sorted ids and create temporary product category names' backup to repaste later
90args0(0).Value = 1
91dispatcher.executeDispatch(document, ".uno:JumpToTable", "", 0, args0())
92dim args2(0) as new com.sun.star.beans.PropertyValue
93args2(0).Name = "ToPoint"
94args2(0).Value = "$V$2"
95dispatcher.executeDispatch(document, ".uno:GoToCell", "", 0, args2())
96dim args3(0) as new com.sun.star.beans.PropertyValue
97args3(0).Name = "StringName"
98args3(0).Value = "=IF(B2;"" "";D2)"
99dispatcher.executeDispatch(document, ".uno:EnterString", "", 0, args3())
100dim args4(0) as new com.sun.star.beans.PropertyValue
101args4(0).Name = "EndCell"
102args4(0).Value = "$V$1500"
103dispatcher.executeDispatch(document, ".uno:AutoFill", "", 0, args4())
104args2(0).Value = "$V$2:$V$1500"
105dispatcher.executeDispatch(document, ".uno:GoToCell", "", 0, args2())
106dispatcher.executeDispatch(document, ".uno:Copy", "", 0, Array())
107dim args5(5) as new com.sun.star.beans.PropertyValue
108args5(0).Name = "Flags"
109args5(0).Value = "SVD"
110args5(1).Name = "FormulaCommand"
111args5(1).Value = 0
112args5(2).Name = "SkipEmptyCells"
113args5(2).Value = false
114args5(3).Name = "Transpose"
115args5(3).Value = false
116args5(4).Name = "AsLink"
117args5(4).Value = false
118args5(5).Name = "MoveMode"
119args5(5).Value = 4
120dispatcher.executeDispatch(document, ".uno:InsertContents", "", 0, args5())
121args2(0).Value = "$B$2:$B$1500"
122dispatcher.executeDispatch(document, ".uno:GoToCell", "", 0, args2())
123dispatcher.executeDispatch(document, ".uno:Copy", "", 0, Array())
124
125rem back to temp sheet to paste
126args0(0).Value = 2
127dispatcher.executeDispatch(document, ".uno:JumpToTable", "", 0, args0())
128args2(0).Value = "$I$2:$I$1500"
129dispatcher.executeDispatch(document, ".uno:GoToCell", "", 0, args2())
130dispatcher.executeDispatch(document, ".uno:Paste", "", 0, Array())
131
132rem fetch categories names from catalog, and merge them in names column
133args0(0).Value = 1
134dispatcher.executeDispatch(document, ".uno:JumpToTable", "", 0, args0())
135args2(0).Value = "$V$2:$V$1500"
136dispatcher.executeDispatch(document, ".uno:GoToCell", "", 0, args2())
137dispatcher.executeDispatch(document, ".uno:Cut", "", 0, Array())
138args0(0).Value = 2
139dispatcher.executeDispatch(document, ".uno:JumpToTable", "", 0, args0())
140args2(0).Value = "$P$2:$P$1500"
141dispatcher.executeDispatch(document, ".uno:GoToCell", "", 0, args2())
142dispatcher.executeDispatch(document, ".uno:Paste", "", 0, Array())
143
144rem COPY VAT
145
146args2(0).Value = "$H$2:$H$1500"
147dispatcher.executeDispatch(document, ".uno:GoToCell", "", 0, args2())
148dispatcher.executeDispatch(document, ".uno:Cut", "", 0, Array())
149args2(0).Value = "$Z$2:$Z$1500"
150dispatcher.executeDispatch(document, ".uno:GoToCell", "", 0, args2())
151dispatcher.executeDispatch(document, ".uno:Paste", "", 0, Array())
152
153
154rem check for deleted ids
155
156args2(0).Value = "$H$2"
157dispatcher.executeDispatch(document, ".uno:GoToCell", "", 0, args2())
158args3(0).Value = "=COUNTIF($A$2:$A$2000;""="" & $I2"
159dispatcher.executeDispatch(document, ".uno:EnterString", "", 0, args3())
160args4(0).Value = "$H$1500"
161dispatcher.executeDispatch(document, ".uno:AutoFill", "", 0, args4())
162
163dim temp as integer
164dim firstid As Integer
165dim secondid As Integer
166dim deletedidexists as Integer
167Dim iBox as Integer
168dim userwantstodelete As Integer
169Dim forprint As String
170
171deletedidexists=0
172xSheet = ThisComponent.Sheets(1)
173vSheet = ThisComponent.Sheets(0)
174For temp = 1 To 1500
175 oCell = xSheet.getCellByPosition(8,temp)
176 firstid= CInt(oCell.string)
177 oCell = xSheet.getCellByPosition(7,temp)
178 secondid= CInt(oCell.string)
179 if ((secondid = 0 ) AND (firstid>0)) then
180 forprint = forprint & firstid & CHR(9) & vSheet.getCellByPosition(2,temp).string & CHR(9) & vSheet.getCellByPosition(3,temp).string & CHR(13)
181 deletedidexists=1
182
183 end if
184Next
185if deletedidexists=1 then
186 forprint = forprint & CHR(13) & "These products are not available in site and they will be deleted now!" & CHR(13) & "Choose no if you want to abort update"
187 iBox = MB_YESNO + MB_DEFBUTTON2 + MB_ICONQUESTION
188 If MsgBox (forprint, iBox) = IDYES then
189 userwantstodelete=1
190 end if
191end if
192if (deletedidexists=1 AND userwantstodelete=0) then
193 msgbox "Update aborted"
194 ThisComponent.getSheets().removeByName("temp")
195else
196 if(deletedidexists=1 AND userwantstodelete=1) then
197 For temp = 1 To 1500
198 oCell = xSheet.getCellByPosition(8,temp)
199 firstid= CInt(oCell.string)
200 oCell = xSheet.getCellByPosition(7,temp)
201 secondid= CInt(oCell.string)
202 if (( secondid = 0 ) AND (firstid>0)) then
203 args2(0).Value = "$H$"+ (temp+2) +":"+"$W$"+ (temp+1502)
204 dispatcher.executeDispatch(document, ".uno:GoToCell", "", 0, args2())
205 dispatcher.executeDispatch(document, ".uno:Cut", "", 0, Array())
206 args2(0).Value = "$H$"+ (temp+1) +":"+"$W$"+ (temp+1501)
207 dispatcher.executeDispatch(document, ".uno:GoToCell", "", 0, args2())
208 dispatcher.executeDispatch(document, ".uno:Paste", "", 0, Array())
209 args0(0).Value = 1
210 dispatcher.executeDispatch(document, ".uno:JumpToTable", "", 0, args0())
211 args2(0).Value = "$G$"+ (temp+2) +":"+"$G$"+ (temp+1502)
212 dispatcher.executeDispatch(document, ".uno:GoToCell", "", 0, args2())
213 dispatcher.executeDispatch(document, ".uno:Cut", "", 0, Array())
214 args2(0).Value = "$G$"+ (temp+1) +":"+"$G$"+ (temp+1501)
215 dispatcher.executeDispatch(document, ".uno:GoToCell", "", 0, args2())
216 dispatcher.executeDispatch(document, ".uno:Paste", "", 0, Array())
217 args2(0).Value = "$J$"+ (temp+2) +":"+"$J$"+ (temp+1502)
218 dispatcher.executeDispatch(document, ".uno:GoToCell", "", 0, args2())
219 dispatcher.executeDispatch(document, ".uno:Cut", "", 0, Array())
220 args2(0).Value = "$J$"+ (temp+1) +":"+"$J$"+ (temp+1501)
221 dispatcher.executeDispatch(document, ".uno:GoToCell", "", 0, args2())
222 dispatcher.executeDispatch(document, ".uno:Paste", "", 0, Array())
223 args2(0).Value = "$U$"+ (temp+2) +":"+"$U$"+ (temp+1502)
224 dispatcher.executeDispatch(document, ".uno:GoToCell", "", 0, args2())
225 dispatcher.executeDispatch(document, ".uno:Cut", "", 0, Array())
226 args2(0).Value = "$U$"+ (temp+1) +":"+"$U$"+ (temp+1501)
227 dispatcher.executeDispatch(document, ".uno:GoToCell", "", 0, args2())
228 dispatcher.executeDispatch(document, ".uno:Paste", "", 0, Array())
229 args0(0).Value = 2
230 dispatcher.executeDispatch(document, ".uno:JumpToTable", "", 0, args0())
231 end if
232 Next
233 endif
234
235rem sort ids to find non existing ones and if users wants insert them in catalog
236args2(0).Value = "$A$2:$A$1500"
237dispatcher.executeDispatch(document, ".uno:GoToCell", "", 0, args2())
238dispatcher.executeDispatch(document, ".uno:Copy", "", 0, Array())
239args2(0).Value = "$R$2:$R$1500"
240dispatcher.executeDispatch(document, ".uno:GoToCell", "", 0, args2())
241dispatcher.executeDispatch(document, ".uno:Paste", "", 0, Array())
242
243args2(0).Value = "$S$2"
244dispatcher.executeDispatch(document, ".uno:GoToCell", "", 0, args2())
245args3(0).Value = "=COUNTIF($I$2:$I$2000;""="" & $R2"
246dispatcher.executeDispatch(document, ".uno:EnterString", "", 0, args3())
247args4(0).Value = "$S$1500"
248dispatcher.executeDispatch(document, ".uno:AutoFill", "", 0, args4())
249
250dim i As Integer
251dim newidexists As Integer
252dim userwantstoinsert As Integer
253Dim numofproducts As Integer
254numofproducts = 0
255forprint = ""
256xSheet = ThisComponent.Sheets(1)
257newidexists=0
258userwantstoinsert = 0
259For i = 1 To 1499
260 oCell = xSheet.getCellByPosition(17,i)
261 firstid= CInt(oCell.string)
262 oCell = xSheet.getCellByPosition(18,i)
263 secondid= CInt(oCell.string)
264 if ((secondid = 0 ) AND (firstid>0)) then
265 forprint = forprint & firstid & CHR(9) & xSheet.getCellByPosition(1,i).string & CHR(9) & xSheet.getCellByPosition(2,i).string & CHR(13)
266 numofproducts = numofproducts + 1
267 newidexists=1
268 end if
269Next
270if newidexists=1 then
271 forprint = forprint & CHR(13) & "These products are available in site but don't exist in catalog." & CHR(13) & "Do you want to start importing them now?"
272 Dim count as Integer
273 Dim inputpos as Integer
274 iBox = MB_YESNO + MB_DEFBUTTON2 + MB_ICONQUESTION
275 If MsgBox (forprint, iBox) = IDYES then
276 userwantstoinsert =1
277 else
278 MsgBox "Syncing will continue, but only products that already exist in catalog will be updated!"
279 end if
280end if
281
282if(userwantstoinsert=1) then
283 InputVal = InputBox("Enter 1 for entering first product only, or 2 for entering all of them at once:", "Product importing", "1")
284
285end if
286
287
288if (newidexists=1 AND userwantstoinsert=1 ) then
289 if ( InputVal= "1") then
290 rem count how many rows are populated
291 count=1499
292 oCell = xSheet.getCellByPosition(8,count)
293 if( oCell.Formula = "" ) then
294 Do While ( oCell.Formula = "" )
295 count=count-1
296 oCell = xSheet.getCellByPosition(8,count)
297 Loop
298 end if
299 count=count+1
300 For i = 1 To 1499
301 oCell = xSheet.getCellByPosition(17,i)
302 firstid= CInt(oCell.string)
303 oCell = xSheet.getCellByPosition(18,i)
304 secondid= CInt(oCell.string)
305 if ((secondid = 0 ) AND (firstid>0)) then
306 sText = InputBox (firstid & " " & xSheet.getCellByPosition(1,i).string & " " & xSheet.getCellByPosition(2,i).string & CHR(13) & "Give the row in which you want this product to be inserted")
307 inputpos = CInt(sText)
308 args2(0).Value = "$I$"+ (inputpos-1) +":"+"$Q$"+ (count)
309 dispatcher.executeDispatch(document, ".uno:GoToCell", "", 0, args2())
310 dispatcher.executeDispatch(document, ".uno:Copy", "", 0, Array())
311 args2(0).Value = "$I$"+ (inputpos) +":"+"$Q$"+ (count+1)
312 dispatcher.executeDispatch(document, ".uno:GoToCell", "", 0, args2())
313 dispatcher.executeDispatch(document, ".uno:Paste", "", 0, Array())
314 oCell = xSheet.getCellByPosition(8,inputpos-1)
315 oCell.value= xSheet.getCellByPosition(0,i).value
316 i=1499
317 end if
318 Next
319 elseif ( InputVal= "2") then
320
321 count=1499
322 oCell = xSheet.getCellByPosition(8,count)
323 if( oCell.Formula = "" ) then
324 Do While ( oCell.Formula = "" )
325 count=count-1
326 oCell = xSheet.getCellByPosition(8,count)
327 Loop
328 end if
329 count=count+1
330
331 sText = InputBox ("Give the row in which you want all the products to be inserted")
332 inputpos = CInt(sText)
333 args2(0).Value = "$I$"+ (inputpos) +":"+"$Q$"+ (count)
334 dispatcher.executeDispatch(document, ".uno:GoToCell", "", 0, args2())
335 dispatcher.executeDispatch(document, ".uno:Cut", "", 0, Array())
336 args2(0).Value = "$I$"+ (inputpos+numofproducts) +":"+"$Q$"+ (count+numofproducts)
337 dispatcher.executeDispatch(document, ".uno:GoToCell", "", 0, args2())
338 dispatcher.executeDispatch(document, ".uno:Paste", "", 0, Array())
339 j = 0
340 For i = 1 To 1499
341 oCell = xSheet.getCellByPosition(17,i)
342 firstid= CInt(oCell.string)
343 oCell = xSheet.getCellByPosition(18,i)
344 secondid= CInt(oCell.string)
345 if ((secondid = 0 ) AND (firstid>0)) then
346 xSheet.getCellByPosition(8,inputpos+j-1).value = firstid
347 j = j+1
348 end if
349 Next
350 else
351 msgbox "Input Error! Please close without saving and run again!"
352 end if
353end if
354 rem sort imported data like the copied ids from catalog
355args2(0).Value = "$J$2"
356dispatcher.executeDispatch(document, ".uno:GoToCell", "", 0, args2())
357args3(0).Value = "=INDEX($A$2:$G$1500;(MATCH($I2;$A$2:$A$15002;-1));2)"
358dispatcher.executeDispatch(document, ".uno:EnterString", "", 0, args3())
359args2(0).Value = "$K$2"
360dispatcher.executeDispatch(document, ".uno:GoToCell", "", 0, args2())
361args3(0).Value = "=INDEX($A$2:$G$1500;(MATCH($I2;$A$2:$A$1500;-1));3)"
362dispatcher.executeDispatch(document, ".uno:EnterString", "", 0, args3())
363args2(0).Value = "$L$2"
364dispatcher.executeDispatch(document, ".uno:GoToCell", "", 0, args2())
365args3(0).Value = "=INDEX($A$2:$G$1500;(MATCH($I2;$A$2:$A$1500;-1));4)"
366dispatcher.executeDispatch(document, ".uno:EnterString", "", 0, args3())
367args2(0).Value = "$M$2"
368dispatcher.executeDispatch(document, ".uno:GoToCell", "", 0, args2())
369args3(0).Value = "=INDEX($A$2:$G$1500;(MATCH($I2;$A$2:$A$1500;-1));5)"
370dispatcher.executeDispatch(document, ".uno:EnterString", "", 0, args3())
371args2(0).Value = "$N$2"
372dispatcher.executeDispatch(document, ".uno:GoToCell", "", 0, args2())
373args3(0).Value = "=INDEX($A$2:$G$1500;(MATCH($I2;$A$2:$A$1500;-1));6)"
374dispatcher.executeDispatch(document, ".uno:EnterString", "", 0, args3())
375args2(0).Value = "$O$2"
376dispatcher.executeDispatch(document, ".uno:GoToCell", "", 0, args2())
377args3(0).Value = "=INDEX($A$2:$G$1500;(MATCH($I2;$A$2:$A$1500;-1));7)"
378dispatcher.executeDispatch(document, ".uno:EnterString", "", 0, args3())
379args2(0).Value = "$J$2:$O$2"
380dispatcher.executeDispatch(document, ".uno:GoToCell", "", 0, args2())
381args4(0).Value = "$O$1500"
382dispatcher.executeDispatch(document, ".uno:AutoFill", "", 0, args4())
383
384
385rem replace N/A with blank, copy and repaste as normal text to remove functions and update product column
386dispatcher.executeDispatch(document, ".uno:Copy", "", 0, Array())
387dispatcher.executeDispatch(document, ".uno:InsertContents", "", 0, args5())
388
389
390rem SORT VAT
391args2(0).Value = "$AA$2"
392 dispatcher.executeDispatch(document, ".uno:GoToCell", "", 0, args2())
393args3(0).Value = "=INDEX($A$2:$Z$1500;(MATCH($I2;$A$2:$A$1500;-1));26)"
394 dispatcher.executeDispatch(document, ".uno:EnterString", "", 0, args3())
395args4(0).Value = "$AA$1500"
396dispatcher.executeDispatch(document, ".uno:AutoFill", "", 0, args4())
397args2(0).Value = "$AA$2:$AA$1500"
398 dispatcher.executeDispatch(document, ".uno:GoToCell", "", 0, args2())
399 dispatcher.executeDispatch(document, ".uno:Cut", "", 0, Array())
400 dispatcher.executeDispatch(document, ".uno:InsertContents", "", 0, args5())
401
402
403xSheet = ThisComponent.Sheets(1)
404 for i=1 to 1500
405 oCell2 = xSheet.getCellByPosition(8,i)
406 oCell = xSheet.getCellByPosition(26,i)
407 if((oCell2.Value > 0 ) AND (oCell.Value = 0 )) then
408 oCell.Value = 24
409 end if
410 next
411
412
413args1(10).Value = "#N/A"
414args1(11).Value = ""
415dispatcher.executeDispatch(document, ".uno:ExecuteSearch", "", 0, args1())
416rem args2(0).Value = "$J$2:$O$2"
417rem dispatcher.executeDispatch(document, ".uno:GoToCell", "", 0, args2())
418args2(0).Value = "$Q$2"
419dispatcher.executeDispatch(document, ".uno:GoToCell", "", 0, args2())
420args3(0).Value = "=IF(I2;K2;P2)"
421dispatcher.executeDispatch(document, ".uno:EnterString", "", 0, args3())
422args4(0).Value = "$Q$1500"
423dispatcher.executeDispatch(document, ".uno:AutoFill", "", 0, args4())
424args2(0).Value = "$Q$2:$Q$1500"
425dispatcher.executeDispatch(document, ".uno:GoToCell", "", 0, args2())
426dispatcher.executeDispatch(document, ".uno:Copy", "", 0, Array())
427args2(0).Value = "$K$2:$K$1500"
428dispatcher.executeDispatch(document, ".uno:GoToCell", "", 0, args2())
429dispatcher.executeDispatch(document, ".uno:InsertContents", "", 0, args5())
430args2(0).Value = "$P$2:$Q$1500"
431dispatcher.executeDispatch(document, ".uno:GoToCell", "", 0, args2())
432dim args8(0) as new com.sun.star.beans.PropertyValue
433args8(0).Name = "Flags"
434args8(0).Value = "A"
435dispatcher.executeDispatch(document, ".uno:Delete", "", 0, args8())
436
437rem copy and insert updated values on catalog
438args2(0).Value = "$I$2:$M$1500"
439dispatcher.executeDispatch(document, ".uno:GoToCell", "", 0, args2())
440dispatcher.executeDispatch(document, ".uno:Copy", "", 0, Array())
441args0(0).Value = 1
442dispatcher.executeDispatch(document, ".uno:JumpToTable", "", 0, args0())
443args2(0).Value = "$B$2:$F$1500"
444dispatcher.executeDispatch(document, ".uno:GoToCell", "", 0, args2())
445dispatcher.executeDispatch(document, ".uno:InsertContents", "", 0, args5())
446
447if (userwantstoinsert=1) then
448 if ( InputVal= "1") then
449 args2(0).Value = "$G$" + inputpos + ":$G$1499"
450 dispatcher.executeDispatch(document, ".uno:GoToCell", "", 0, args2())
451 dispatcher.executeDispatch(document, ".uno:Copy", "", 0, Array())
452 args2(0).Value = "$G$" + (inputpos+1) + ":$G$1500"
453 dispatcher.executeDispatch(document, ".uno:GoToCell", "", 0, args2())
454 dispatcher.executeDispatch(document, ".uno:InsertContents", "", 0, args5())
455 xSheet = ThisComponent.Sheets(0)
456 oCell = xSheet.getCellByPosition(6,inputpos-1)
457 oCell.formula = ""
458 args2(0).Value = "$J$" + inputpos + ":$J$1499"
459 dispatcher.executeDispatch(document, ".uno:GoToCell", "", 0, args2())
460 dispatcher.executeDispatch(document, ".uno:Copy", "", 0, Array())
461 args2(0).Value = "$J$" + (inputpos+1) + ":$J$1500"
462 dispatcher.executeDispatch(document, ".uno:GoToCell", "", 0, args2())
463 dispatcher.executeDispatch(document, ".uno:InsertContents", "", 0, args5())
464 oCell = xSheet.getCellByPosition(9,inputpos-1)
465 oCell.formula = ""
466 elseif ( InputVal= "2") then
467 args2(0).Value = "$G$" + inputpos + ":$G$1499"
468 dispatcher.executeDispatch(document, ".uno:GoToCell", "", 0, args2())
469 dispatcher.executeDispatch(document, ".uno:Copy", "", 0, Array())
470 args2(0).Value = "$G$" + (inputpos+numofproducts) + ":$G$" + (1499+numofproducts)
471 dispatcher.executeDispatch(document, ".uno:GoToCell", "", 0, args2())
472 dispatcher.executeDispatch(document, ".uno:InsertContents", "", 0, args5())
473 args2(0).Value = "$J$" + inputpos + ":$J$1499"
474 dispatcher.executeDispatch(document, ".uno:GoToCell", "", 0, args2())
475 dispatcher.executeDispatch(document, ".uno:Copy", "", 0, Array())
476 args2(0).Value = "$J$" + (inputpos+numofproducts) + ":$J$" + (1499+numofproducts)
477 dispatcher.executeDispatch(document, ".uno:GoToCell", "", 0, args2())
478 dispatcher.executeDispatch(document, ".uno:InsertContents", "", 0, args5())
479 xSheet = ThisComponent.Sheets(0)
480 for j=0 to (numofproducts-1)
481 xSheet.getCellByPosition(6,inputpos-1+j).formula = ""
482 xSheet.getCellByPosition(9,inputpos-1+j).formula = ""
483 next
484 end if
485end if
486
487args0(0).Value = 2
488dispatcher.executeDispatch(document, ".uno:JumpToTable", "", 0, args0())
489args2(0).Value = "$N$2:$O$1500"
490dispatcher.executeDispatch(document, ".uno:GoToCell", "", 0, args2())
491dispatcher.executeDispatch(document, ".uno:Copy", "", 0, Array())
492args0(0).Value = 1
493dispatcher.executeDispatch(document, ".uno:JumpToTable", "", 0, args0())
494args2(0).Value = "$H$2:$I$1500"
495dispatcher.executeDispatch(document, ".uno:GoToCell", "", 0, args2())
496dispatcher.executeDispatch(document, ".uno:InsertContents", "", 0, args5())
497args0(0).Value = 2
498dispatcher.executeDispatch(document, ".uno:JumpToTable", "", 0, args0())
499args2(0).Value = "$AA$2:$AA$1500"
500dispatcher.executeDispatch(document, ".uno:GoToCell", "", 0, args2())
501dispatcher.executeDispatch(document, ".uno:Copy", "", 0, Array())
502args0(0).Value = 1
503dispatcher.executeDispatch(document, ".uno:JumpToTable", "", 0, args0())
504args2(0).Value = "$N$2:$N$1500"
505dispatcher.executeDispatch(document, ".uno:GoToCell", "", 0, args2())
506dispatcher.executeDispatch(document, ".uno:InsertContents", "", 0, args5())
507ThisComponent.getSheets().removeByName("temp")
508
509
510rem fix for K/L/M /O/P columns after insert
511args2(0).Value = "$K$2:$M$2"
512dispatcher.executeDispatch(document, ".uno:GoToCell", "", 0, args2())
513dispatcher.executeDispatch(document, ".uno:Copy", "", 0, Array())
514args2(0).Value = "$K$3:$M$1500"
515dispatcher.executeDispatch(document, ".uno:GoToCell", "", 0, args2())
516dispatcher.executeDispatch(document, ".uno:Paste", "", 0, args5())
517args2(0).Value = "$O$2:$P$2"
518dispatcher.executeDispatch(document, ".uno:GoToCell", "", 0, args2())
519dispatcher.executeDispatch(document, ".uno:Copy", "", 0, Array())
520args2(0).Value = "$O$3:$P$1500"
521dispatcher.executeDispatch(document, ".uno:GoToCell", "", 0, args2())
522dispatcher.executeDispatch(document, ".uno:Paste", "", 0, args5())
523
524Msgbox "catalog updated succesfully"
525
526
527endif
528 end if
529 end if
530 end if
531end sub