Solved: Writing multiple selection values to database

[hometech]

Hello Guys, i am currently working on a macro that stores the values of the current selected or highlighted text in a word document into an access database, the code works very well without errorrs


Sub insertValuesToDB()
Dim valueRead As String
Application.Selection.Expand wdLine
valueRead = Application.Selection.Text
Dim adoConn As ADODB.Connection
Dim adoCmd As ADODB.Command
Set adoConn = New ADODB.Connection
With adoConn
.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\CPMS\M&CAutomation.mdb"
.Open
End With
Set adoCmd = New ADODB.Command
With adoCmd
.ActiveConnection = adoConn
.CommandText = "INSERT INTO Comments (cmtComment) VALUES ('" & (valueRead) & "')"
End With
adoCmd.Execute
adoConn.Close

Set adoConn = Nothing
MsgBox "Value was added to your database table."

End Sub


okay so now i want to do is to be able to have multiple selection in my document and write the different selected text to different fields within the database using the first selection as a criteria to write into the data base
so this is what i mean

i have lines of text like this in my table

1 bla, bla, bla.....
2 bla, bla, bla.....
3 bla, bla, bla.....
4 bla, bla, bla.....

i want to be able to select item 2 and select the sentence that follows it without the space (which is very possible in ms word) and now assign the sentence to valueread which was done in the code above and also assign the number (2) as another selection so that i would be able to refer to them differently programatically as selection(1).text and selection(2).text as we can refer to tables, shapes etc in vba so that i can do something like this

"INSERT INTO Comments (cmtComment) VALUES ('" & (valueRead) & "' where ID = '" & (valueRead2) & "')"

valueread2 refers to the list number (1,2, 3 or 4)

please any ideas, appreciate your suggestions
Thanks

[fumei]

First of all VBA can NOT do multiple (non-contiguous) selections. You can do multiple non-contiguous selections in the GUI, but VBA ignores everything except the first one. So in VBA you can not do "another selection".

You can extend the selection...but that still makes it ONE selection.

You could make the other paragraphs (I assume they are paragraphs) ranges. Like this:Dim rngSelection As Range
Dim rngNext As Range
Dim rngPrevious As Range

Set rngSelection = Selection.Range
Set rngNext = rngSelection.Next.Paragraphs(1).Range
Set rngPrevious = rngSelection.Previous.Paragraphs(1).Range
MsgBox rngNext & " " & rngPrevious
If you selected

2. bla, bla, bla....

then the messagebox would display

3. bla, bla, bla.... (rngNext)
1. bla, bla, bla.... (rngPrevious)

Does that help? It would get you the values for other ranges, based on the Selection.

[fumei]

And of course if you just need the values temporarily (so to speak), in order to pass a string to your instruction, you do not actually need other range objects. You can use the relative range values directly.Dim rngSelection As Range

Set rngSelection = Selection.Range

MsgBox rngSelection.Next.Paragraphs(1).Range & " " _
& rngSelection.Previous.Paragraphs(1).Range
Also returns the same messagebox.

[hometech]

Well fumei, thank you very much, i appreciate your help.

i really thought i could work with the multiple selection which i have seen is not possibly. i actually wanted the use the number in the line as a criteria for inputting the sentence into the database. actually the sentence being passed to the database represents a field in the database and also the line numbers also represents another field in the database. i actually want to edit the contents of the database where the value of the ID field corresponds to that of the line number.

Anyway thanks all the same.

[hometech]

hey fumei this line in my macro selects the whole line of text

Application.Selection.Expand wdLine

which i dont want to select the whole line, i just want to selection a portion of the sentence one that line, how do i go about that

2. bla, bla, bla....

i just want to select bla, bla, with the 2, and the last bla... how do i go about it

thanks

[fumei]

If things are consistent, you could simply strip off the first three characters (2. ) - 2, dot, space. Additionally, you can GRAB the number and use it for whatever logical purposes you need.
Dim rngSelection As Range
Dim strNextNum As String
Dim strNextText As String
Dim strPreviousNum As String
Dim strPreviousText As String

Set rngSelection = Selection.Range
' the variables for the NEXT paragraph - number & text
strNextNum = Left(rngSelection.Next.Paragraphs(1).Range.Text, 1)
strNextText = Mid(rngSelection.Next.Paragraphs(1).Range.Text, 4)

' the variables for the PREVIOUS paragraph - number & text

strPreviousNum = Left(rngSelection.Next.Paragraphs(1).Range.Text, 1)
strPreviousText = Mid(rngSelection.Next.Paragraphs(1).Range.Text, 4)
' whatever it is you are testing the next paragraph for
If strNextText = "whatever" Then

"INSERT INTO Comments (cmtComment) VALUES ('" & _
(Selection.Text) & "' where ID = '" & (strNextText) & "')"

Are you following this?


which i dont want to select the whole line, i just want to selection a portion of the sentence one that line, how do i go about thatThat is basic string manipulation. Look up Left, Right, Mid, Replace etc. in VBA help.

[hometech]

Yes fumei, thank you very much, i appreciate.
Much thanks.
:bow:

[fumei]

You are welcome.

[hometech]

Hey guy, didn't think i was going to be making any post here after marking this thread solved. Anyway working with your suggestions, i came up with this and i think it does what i want, here it is.


Dim valueRead As String
Dim strNum As String
Dim strText As String
valueRead = Application.Selection.Text
strNum = Val(Left(valueRead, InStr(valueRead, vbTab) - 1))
strText = Mid(valueRead, InStr(valueRead, vbTab))

for example

24 bla, bla, bla.....

The strNum holds the line number and the strText holds the associated text.



The sentence is tab delimited that is why i used the vbTab or else i wold have used the empty quote (" ") to represent a space.

And based on that i can now write the values to the database.

Once again, much thanks Fumei.