[Solved] How to select specific column, apply Search&Replace

[luckywiseguy]

Hi All,

Good Day! I would like to seek assistance regarding the macro that I created using OpenOffice Basic and now, I don't know what to do

I'm new to programming, I was reading the guide using this link https://wiki.openoffice.org/w/images/c/ ... o3.2.0.pdf but I'm really having a hard time understanding all the logic there, sometimes I feel I'm crazy lol

What I want to do now is to include a script after For Next Loop and focus the selection from A2 until end of rows in A column.
For example(A2:A15000) and apply search and replace to that specific Column A.
column A starting A2 below has SKU 8-digit number but my problem is there is apostrophe ' at the beginning of each cell.

Code: Select all

REM  *****  BASIC  *****
Const SColumns = "D,E,F,G,H,I,J,K,M,N,O,P,Q,R,S,T,U,V,W" 'ascending

Sub clearBiArc

Dim Doc As Object
Dim MultiSheets As Object
Dim SingleSheet As Object
Dim oRows As Object
Dim aColumns As Object
Dim oColumn As Object

Doc = ThisComponent
MultiSheets = Doc.getSheets()
SingleSheet = MultiSheets.getByName("BIArcRawData")

oRows = SingleSheet.getRows()
oRows.removeByIndex(0, 17)
oRows.removeByIndex(1, 1)
   
aColumns = Split(SColumns,",")
'oSheet = ThisComponent.CurrentController.ActiveSheet

For i = uBound(aColumns) to 0 step -1
oColumn = SingleSheet.Columns.getbyname(aColumns(i))
oColumn.Columns().removebyindex(0,1)
Next i

Msgbox "Done"
   
End Sub

I tried to run a macro recorder to search ' and replace nothing and it worked fine, below is the code.

But the problem is it only works if I select the column A, if I don't it will say search key not found, is there a way to integrate below code after my for next loop above or can you suggest a better way to do it.

I have a button in first sheet that once I click it will run the Sub clearBiArc which is located in another spreadsheet on same document. I want to run this code from macro recorder also without having to select manually the column A in the sheet.

Code: Select all

sub removeapos
rem ----------------------------------------------------------------------
rem define variables
dim document   as object
dim dispatcher as object
rem ----------------------------------------------------------------------
rem get access to the document
document   = ThisComponent.CurrentController.Frame
dispatcher = createUnoService("com.sun.star.frame.DispatchHelper")

rem ----------------------------------------------------------------------
dim args1(17) as new com.sun.star.beans.PropertyValue

args1(0).Name = "SearchItem.StyleFamily"
args1(0).Value = 2
args1(1).Name = "SearchItem.CellType"
args1(1).Value = 0
args1(2).Name = "SearchItem.RowDirection"
args1(2).Value = true
args1(3).Name = "SearchItem.AllTables"
args1(3).Value = false
args1(4).Name = "SearchItem.Backward"
args1(4).Value = false
args1(5).Name = "SearchItem.Pattern"
args1(5).Value = false
args1(6).Name = "SearchItem.Content"
args1(6).Value = false
args1(7).Name = "SearchItem.AsianOptions"
args1(7).Value = false
args1(8).Name = "SearchItem.AlgorithmType"
args1(8).Value = 1
args1(9).Name = "SearchItem.SearchFlags"
args1(9).Value = 71680
args1(10).Name = "SearchItem.SearchString"
args1(10).Value = "^[0-9]"
args1(11).Name = "SearchItem.ReplaceString"
args1(11).Value = "&"
args1(12).Name = "SearchItem.Locale"
args1(12).Value = 255
args1(13).Name = "SearchItem.ChangedChars"
args1(13).Value = 2
args1(14).Name = "SearchItem.DeletedChars"
args1(14).Value = 2
args1(15).Name = "SearchItem.InsertedChars"
args1(15).Value = 2
args1(16).Name = "SearchItem.TransliterateFlags"
args1(16).Value = 1280
args1(17).Name = "SearchItem.Command"
args1(17).Value = 3

dispatcher.executeDispatch(document, ".uno:ExecuteSearch", "", 0, args1())


end sub

I'm sorry if I typed many words, I'm really sorry, can you also put comments so I can learn how you done it.

Hoping for someone kind response.

Thank you so much

[FJCC]

column A starting A2 below has SKU 8-digit number but my problem is there is apostrophe ' at the beginning of each cell

I want to focus on the above part of your post. The apostrophe is not actually part of the cell content. It is an indicator, displayed only in the formula bar, that the cell content is text despite looking like a number. This is almost certainly what you want for a SKU. Normally, one doesn't do any calculations with an SKU; it is just used as a label. Have you run into any problems using the SKUs?

[MrProgrammer]

Hi, and welcome to the forum.

column A starting A2 below has SKU 8-digit number but my problem is there is apostrophe ' at the beginning of each cell.

I agree with FJCC. The apostrophe is not part of the value. You can verify that by using the =LEN(A2) formula. It will show 8, not 9. If you don't want to see the apostrophe in the formula bar, format these cells as text, preferably using a style. Then the value will match the format and Calc won't need to show you its "Hey, this is text" indicator, the apostrophe, in the formula bar. SKU values are are identifiers, not numbers; it is meaningless to calculate the square root of a Stock Keeping Unit. The cells should be formatted as text to ensure that any values typed into them are stored correctly. For example, you'd want any leading zeros to be retained.

You don't need to spend any further time on your Search and Replace macro. Use that time to read the tutorial below, if you have not already.

If this solved your problem please go to your first post use the Edit button and add [Solved] to the start of the title. You can select the green checkmark icon at the same time.

[Tutorial] Ten concepts that every Calc user should know

[luckywiseguy]

Have you run into any problems using the SKUs?

Hi, I appreciate your explanation regarding the apostrophe ' and I'd like to thank you for that.

The reason why I wanted to remove those apostrophe ' is that I always get #NA when I performed vlookup into it but when I tried to remove those apostrophe ', vlookup gives the result.

I have another text file generated from another system, it gives me list of 8 digit SKU without apostrophe ', what I do often is compared the list of SKU from text file vs to that list of sku with apostrophe ' coming from another web based system, to get the on-hand qty of specific SKU.

The list of SKU coming from web based system has different dimensions/column headers that is not important to me, that's why I ran a macro to remove those columns and rows not important to my daily reporting.

I wanted to run macro on this part also so no need for me to do that part over and over again.

@MrProgrammer thank you for that very informative link, appreciate your help

[FJCC]

The failure of the VLOOKUP is not due to the apostrophe because VLOOKUP would not see the apostrophe. Can you post a small test file showing a failure of the VLOOKUP. It only needs to be a few rows of data. To upload a file, click Post Reply and look for the Upload Attachment tab just below the box where you type a response.

[luckywiseguy]

Hi FJCC,

Good Day! Kindly see attached file for your reference.

[Villeroy]

[Tutorial] Ten concepts that every Calc user should know (or any book on spreadsheets of the past 30 years)

[FJCC]

The SKUs on Sheet1 are numbers and the SKUs "with the apostrophe" are not, as explained earlier. I made a duplicate of the table on Sheet1, formatted A9:A12 as text and reentered the SKUs. Both VLOOKUPs work in that case. You can see what is text and what is a number by selecting the menu View -> Value HIghlighting. Text is black, numbers are blue and the result of a formula is green.
You probably want your SKUs to be text so that leading zeros are not dropped.

[luckywiseguy]

Hi Sir FJCC,

Good Day! I would like to inform you that I already found out the solution to this problem by calling the sub procedure of macro recorder and just adding few lines of code right after my existing macro.

I'm pretty sure that this is not the efficient way but still I managed to find a work around to what I wanted

Thank you so much for your help! Much appreciated everything

Code: Select all

REM  *****  BASIC  *****
Const SColumns = "D,E,F,G,H,I,J,K,M,N,O,P,Q,R,S,T,U,V,W" 'ascending

Sub clearBiArc

Dim Doc As Object
Dim MultiSheets As Object
Dim SingleSheet As Object
Dim oRows As Object
Dim aColumns As Object
Dim oColumn As Object

Doc = ThisComponent
MultiSheets = Doc.getSheets()
SingleSheet = MultiSheets.getByName("BIArcRawData")

oRows = SingleSheet.getRows()
oRows.removeByIndex(0, 17)
oRows.removeByIndex(1, 1)
'The first number is the row index. Row 1 has index 0, so row 5 has index 4
'The second number is the number of rows to remove.
   
aColumns = Split(SColumns,",")
'oSheet = ThisComponent.CurrentController.ActiveSheet

For i = uBound(aColumns) to 0 step -1
oColumn = SingleSheet.Columns.getbyname(aColumns(i))
oColumn.Columns().removebyindex(0,1)
Next i

removeap()

Msgbox "Done"
   
End Sub

sub removeap
rem ----------------------------------------------------------------------
rem define variables
dim document   as object
dim dispatcher as object
Dim Doc As Object
Dim MultiSheets As Object
Dim SingleSheet As Object

Doc = ThisComponent
MultiSheets = Doc.getSheets()
SingleSheet = MultiSheets.getByName("BIArcRawData") 
rem ----------------------------------------------------------------------
rem get access to the document
document   = Doc.CurrentController.Frame
Doc.CurrentController.Select(SingleSheet) 
dispatcher = createUnoService("com.sun.star.frame.DispatchHelper")

rem ----------------------------------------------------------------------
dim args1(1) as new com.sun.star.beans.PropertyValue
args1(0).Name = "By"
args1(0).Value = 1
args1(1).Name = "Sel"
args1(1).Value = true

dispatcher.executeDispatch(document, ".uno:GoDownToEndOfData", "", 0, args1())

rem ----------------------------------------------------------------------
dim args2(17) as new com.sun.star.beans.PropertyValue
args2(0).Name = "SearchItem.StyleFamily"
args2(0).Value = 2
args2(1).Name = "SearchItem.CellType"
args2(1).Value = 0
args2(2).Name = "SearchItem.RowDirection"
args2(2).Value = true
args2(3).Name = "SearchItem.AllTables"
args2(3).Value = false
args2(4).Name = "SearchItem.Backward"
args2(4).Value = false
args2(5).Name = "SearchItem.Pattern"
args2(5).Value = false
args2(6).Name = "SearchItem.Content"
args2(6).Value = false
args2(7).Name = "SearchItem.AsianOptions"
args2(7).Value = false
args2(8).Name = "SearchItem.AlgorithmType"
args2(8).Value = 1
args2(9).Name = "SearchItem.SearchFlags"
args2(9).Value = 71680
args2(10).Name = "SearchItem.SearchString"
args2(10).Value = "^[0-9]"
args2(11).Name = "SearchItem.ReplaceString"
args2(11).Value = "&"
args2(12).Name = "SearchItem.Locale"
args2(12).Value = 255
args2(13).Name = "SearchItem.ChangedChars"
args2(13).Value = 2
args2(14).Name = "SearchItem.DeletedChars"
args2(14).Value = 2
args2(15).Name = "SearchItem.InsertedChars"
args2(15).Value = 2
args2(16).Name = "SearchItem.TransliterateFlags"
args2(16).Value = 1280
args2(17).Name = "SearchItem.Command"
args2(17).Value = 3

dispatcher.executeDispatch(document, ".uno:ExecuteSearch", "", 0, args2())


end sub