Visual Basic

Trying to set caret at correct offset: CLOSED

7-Mar-19 1:52

1. See System.Data.SqlClient Namespace | Microsoft Docs[^] .
2. See TextBox Class (System.Windows.Forms) | Microsoft Docs[^].

mo149228-Feb-19 7:58

mo1492

28-Feb-19 7:58

This solved my problem:
Dim szF As SizeF = gr.MeasureString(Me.Text.Substring(0, iIndex), Me.Font, New SizeF(999, 999), StringFormat.GenericTypographic)

I have a control derived from UserControl in which I am trying to implement like a TextBox
and I am having trouble setting the caret in the correct text column position. Below is the
code to calculate the x offset and move the caret.

Simple test:
With the text "aBc", when I try to set the caret between 'a' and 'B' (iIndex=1), the caret is displayed
almost at the end of the 'B'.

MeasureDisplayStringWidth() is test code I found on the internet and
does not make any difference than using gr.MeasureString().

SetCaretPos() is the standared Windows API for setting the caret.

And for those who wonder why I don't just use a TextBox, this control has all kinds of custom
stuff including rotation.

I would appreciate any help.
Thank you

Protected Overrides Sub OnPaint(ByVal e As System.Windows.Forms.PaintEventArgs)
Dim sb As SolidBrush = New SolidBrush(Color.FromArgb(255, 0, 0, 255))
' Display text
e.Graphics.DrawString(Me.Text, me.Font, sb, 0, 0)
End Sub

Private Sub MoveCaret(ByVal iIndex As Integer)
Dim gr As Graphics = Me.CreateGraphics()
' Both of these give the same result.
'Dim szF As SizeF = gr.MeasureString(Me.Text.Substring(0, iIndex), Me.Font)
Dim i As Integer = MeasureDisplayStringWidth(gr, Me.Text.Substring(0, iIndex), Me.Font)

gr.Dispose()
gr = Nothing

' Windows api call
MyUtilsLib.WindowsAPI.SetCaretPos(i, 1)
End Sub

Public Function MeasureDisplayStringWidth(ByVal gr As Graphics, ByVal text As String, ByVal fnt As Font) As Integer
If text.Length < 1 Then Return 0

Dim format As System.Drawing.StringFormat = New System.Drawing.StringFormat()
Dim rect As System.Drawing.RectangleF = New System.Drawing.RectangleF(0, 0, 1000, 1000)
Dim ranges As System.Drawing.CharacterRange() = {New System.Drawing.CharacterRange(0, text.Length)}
Dim regions As System.Drawing.Region() = {New System.Drawing.Region()}

format.SetMeasurableCharacterRanges(ranges)
regions = gr.MeasureCharacterRanges(text, fnt, rect, format)
rect = regions(0).GetBounds(gr)

Return CInt(rect.Right + 1.0F)
End Function

-- modified 28-Feb-19 15:15pm.

System.Data.OLEDB, my OLEDB connection stopped working in Win10 X64

jkirkerx26-Feb-19 7:47

jkirkerx

26-Feb-19 7:47

Not sure if this is the right place to post this.

My VB Winform app works on Win 7 32 bit just fine, and in VS 2017 when developing on my Win10 X64 machine. But when I go to install the app on my machine, it won't access or read the database files. There's no error or exception thrown. It worked before, up until perhaps last December.

Some research suggested that I need the Access Database Engine. So I did download that and installed but no difference. Not really sure which direction to go here. Perhaps I just need to add a file to my Wix installer program. I'm thinking it's the System.Data file that contains OLEDB and I can just add that to the install.

I do have my Wix installer set to X86, because the customer target machines are Win 7 32 bit. But I'm will be selling the new computers that are Win10 X64 so I need to fix this.

Any help or knowledge would be appreciated.

If it ain't broke don't fix it
Discover my world at jkirkerx.com

Tried some more things

jkirkerx26-Feb-19 8:27

jkirkerx

26-Feb-19 8:27

I changed the compile to X86, and choose Debug-Net4.0 and that version works
Tried Release-Net4.0 and that version didn't work

So the debug version works, but not the release
In release mode I get this error - The 'Microsoft.Jet.OLEDB.4.0' provider is not registered on the local machine

Oh, I spent many hours working with nLog and integrated it into my windows apps. Just dawned on me to use my logging system to check for errors, Homer Simpson Duh!!!!!

I think I got this now ...

If it ain't broke don't fix it
Discover my world at jkirkerx.com

modified 26-Feb-19 14:47pm.

Re: System.Data.OLEDB, my OLEDB connection stopped working in Win10 X64

Maciej Los26-Feb-19 8:46

Maciej Los

26-Feb-19 8:46

Please, read these:
Solved - The Microsoft.ACE.OLEDB.12.0 provider is not registered on the local machine
Jet for Access, Excel and Txt on 64 bit systems - ConnectionStrings.com
The 'Microsoft.ACE.OLEDB.12.0' provider is not registered on the local machine - ConnectionStrings.com

It might help to resolve your issue.

Message Closed

11-Feb-19 1:03

Re: How can i make sure UDF Add-in to work on different machine

11-Feb-19 1:03

Message Closed

modified 11-Feb-19 12:17pm.

Dave Kreskowiak11-Feb-19 3:07

Dave Kreskowiak

11-Feb-19 3:07

Doing so has huge problems. First, if the network connection to where you store the XLAM file goes down, the extension no longer works.

Next, if you screw up the code in the XLAM and put it in the stored location, you break it for EVERYONE.

And, if your code uses COM-based components, you have to install the thing the components anyway because you have to write the registry information used to tell the machine where to find the components and how to run them.

Do yourself, and your customers, a favor and just copy it locally, a.k.a.: install it.

As someone who repackages and deploys software as my primary job, running code from a network share is ROYAL PAIN IN THE ASS!

Asking questions is a skill
CodeProject Forum Guidelines
Google: C# How to debug code
Seriously, go read these articles.

Dave Kreskowiak

Re: How can i make sure UDF Add-in to work on different machine

manju 311-Feb-19 6:21

Split cells with comma and space

11-Feb-19 6:21

Hi Dave,

Thanks for your reply.
Sorry did not noticed your reply so deleted the question posted.
Sorry for deleting the post added.

Thanks
Manju

manju 34-Feb-19 21:34

4-Feb-19 21:34

Hi Team,

I am trying to split the cells if comma and space .
I tried below code.Its spliting for space now.

How can i change it so that it will check for multiple(both space and comma)

Dim splitvals As Variant
Dim totalVals As Long

Set sh1 = ThisWorkbook.Sheets(1)
Set sh2 = ThisWorkbook.Sheets(2)

sh2.Cells.Clear
lrow1 = sh1.Range("A65356").End(xlUp).Row

For j = 2 To lrow1
    splitvals = Split(sh1.Cells(j, 2), " ")    
    For i = LBound(splitvals) To UBound(splitvals)
        lrow2 = sh2.Range("B65356").End(xlUp).Row
        lrow3 = sh2.Range("A65356").End(xlUp).Row
        sh2.Cells(lrow3 + 1, 1) = sh1.Cells(j, 1)
        sh2.Cells(lrow3 + 1, 2) = splitvals(i)
    Next i   
     
    
Next j

Even i tried:

splitvals = Split(sh1.Cells(j, 2), ", " " ")

Please suggest me .

Thanks
Manju

Richard MacCutchan4-Feb-19 22:11

4-Feb-19 22:11

Most method calls operate on objects, but your Split call does not appear to be connected to any object. Where is the documentation for that method?

manju 34-Feb-19 23:47

4-Feb-19 23:47

Dim splitvals As Variant
Dim totalVals As Long
Dim myData As Workbook
Set myData = Workbooks.Open("D:\Test.xlsx")  

Set sh1 = myData.Sheets(1)
Set sh2 = myData.Sheets(2)
sh2.Cells.Clear
lrow1 = sh1.Range("A65356").End(xlUp).Row

For j = 2 To lrow1   
   splitvals = Split(sh1.Cells(j, 2), ",")
    
    For i = LBound(splitvals) To UBound(splitvals)
        lrow2 = sh2.Range("B65356").End(xlUp).Row
        lrow3 = sh2.Range("A65356").End(xlUp).Row
        sh2.Cells(lrow3 + 1, 1) = sh1.Cells(j, 1)
        sh2.Cells(lrow3 + 1, 2) = splitvals(i)
    Next i   
     
    
Next j

Thanks
Sharan

Richard MacCutchan4-Feb-19 23:56

4-Feb-19 23:56

That is just a repeat of your question. I asked you where the Split method is defined.

Richard Deeming5-Feb-19 1:17

Richard Deeming

5-Feb-19 1:17

It's a built-in VBA method:
Split function (Visual Basic for Applications) | Microsoft Docs[^]

"These people looked deep within my soul and assigned me a number based on the order in which I joined."
- Homer

datagridview that was bound to dataset

Maciej Los5-Feb-19 1:41

Maciej Los

5-Feb-19 1:41

Quote:
How can i change it so that it will check for multiple(both space and comma)

You can't pass multiple delimiters into VBA Split function.

You have to call Split function again:

For j = 2 To lrow1
    splitvals = Split(sh1.Cells(j, 2), " ")    
    For i = LBound(splitvals) To UBound(splitvals)
        lrow2 = sh2.Range("B65356").End(xlUp).Row
        lrow3 = sh2.Range("A65356").End(xlUp).Row
        sh2.Cells(lrow3 + 1, 1) = sh1.Cells(j, 1)
        sh2.Cells(lrow3 + 1, 2) = splitvals(i)
    Next i   

    splitvals = Split(sh1.Cells(j, 2), ",")    
    For i = LBound(splitvals) To UBound(splitvals)
        lrow2 = sh2.Range("B65356").End(xlUp).Row
        lrow3 = sh2.Range("A65356").End(xlUp).Row
        sh2.Cells(lrow3 + 1, 1) = sh1.Cells(j, 1)
        sh2.Cells(lrow3 + 1, 2) = splitvals(i)
    Next i   

Next j

ClintSoft4-Feb-19 15:38

ClintSoft

4-Feb-19 15:38

Guys need some help here i was trying to delete both datagridview and dataset at the same time
but i seems hard to find some result in what i'am doing so may some one can help me please.... Frown | :(

here is my code in retrieving data from database :

Try
           OpenDatabase()
           cqCnn.Open()
           Using cmd As New SqlCommand("Select * from baseMergeView where  department = '" & tscmddept.Text & "' or fullname Like '%" & tssearch.Text & "%'", cqCnn)
               dtmerge = New virualMerge.MergeTableDataTable
               Dim adapter As New SqlDataAdapter(cmd)
               adapter.Fill(dtmerge)
           End Using
           DataGridView1.AutoGenerateColumns = False
           DataGridView1.DataSource = dtmerge
       Catch ex As Exception
           MessageBox.Show(ex.Message, "Data", MessageBoxButtons.OK, MessageBoxIcon.Warning)
       Finally
           CloseDatabase()
           cqCnn.Close()
       End Try

and here is my code when iam trying to delete the rows at the same time :

tried this one

Dim i As Integer = 0
       Do While i < DataGridView1.Rows.Count
           If DataGridView1.Rows(i).Cells(0).Value = True Then
               Dim item As virualMerge.MergeTableRow = DirectCast(DirectCast(DataGridView1.CurrentRow.DataBoundItem, DataRowView).Row, virualMerge.MergeTableRow)
               item.Delete()
               DataGridView1.SelectedRows(i).DataBoundItem.Delete()
           End If
           If i = DataGridView1.Rows.Count Then
               Exit Do
           End If
           i = i + 1
       Loop

and this one to :

For x As Integer = 0 To DataGridView1.Rows.Count - 1
           If DataGridView1.Rows(i).Cells(0).Value = True Then
               Dim item As virualMerge.MergeTableRow = DirectCast(DirectCast(DataGridView1.CurrentRow.DataBoundItem, DataRowView).Row, virualMerge.MergeTableRow)
               item.Delete()
               DataGridView1.SelectedRows(i).DataBoundItem.Delete()
           End If

       Next

it seems iam going nowhere with this... Please Help

Load xlsx filr from folder and write to new workbook

manju 33-Feb-19 20:25

Re: Load xlsx filr from folder and write to new workbook

3-Feb-19 20:25

Hi all,

I need to load an Test.xlxs file form "D" drive and write the same content to new workbook in sheet1.
Add sheet2 in the workbook and filter the workbook and write the filtered into sheet2 to on workbook.

I have tried the below code but its writing to the same input file with sheet2 but not in the new workbook.

Input file will contain

DrinkID	Reciepe_Dat
2	Absolute,Enter,Test
3	Decupper,Enter,Test
4	"Absolute
"

Output is having

DrinkID	Reciepe_Dat
2	Absolute
2	Enter
2	Test
3	Decupper
3	Enter
3	Test
4	"Absolute
"

but writing to the same input file.

here is my code:

Sub splitbycells()
Dim splitvals As Variant
Dim totalVals As Long

Set sh1 = ThisWorkbook.Sheets(1)
Set sh2 = ThisWorkbook.Sheets(2)

sh2.Cells.Clear
lrow1 = sh1.Range("A65356").End(xlUp).Row

For j = 2 To lrow1
    'splitvals = Split(sh1.Cells(j, 2), Chr(10))
    splitvals = Split(sh1.Cells(j, 2), ",")
    
    For i = LBound(splitvals) To UBound(splitvals)
        lrow2 = sh2.Range("B65356").End(xlUp).Row
        lrow3 = sh2.Range("A65356").End(xlUp).Row
        sh2.Cells(lrow3 + 1, 1) = sh1.Cells(j, 1)
        sh2.Cells(lrow3 + 1, 2) = splitvals(i)
    Next i
Next j
sh2.Range("A1") = "DrinkID"
sh2.Range("B1") = "Reciepe_Dat"
        

End Sub

Thanks
Sharan

Richard MacCutchan3-Feb-19 22:23

Re: Load xlsx filr from folder and write to new workbook

3-Feb-19 22:23

Where is the code to write the new workbook?

manju 34-Feb-19 21:39

Re: Load xlsx filr from folder and write to new workbook

4-Feb-19 21:39

Hi,

I have updated the code where it loads the file from some path.
And setting it to myData.

Set myData = Workbooks.Open("D:\B_Test.xlsx")
Set sh1 = myData.Sheets(1)
Set sh2 = myData.Sheets(2)

Now how can i change the code so that it writes to a new workbook and and set that to myData.

Set myData = "New WorkBook"

Thanks
Sharan

Richard MacCutchan4-Feb-19 22:00

Re: Load xlsx filr from folder and write to new workbook

4-Feb-19 22:00

You need to create a new Workbook, add some Worksheets and fill in their content, then save the new Workbook. Something like the following (Yes, I know it's C# but the principle is the same):

//Create an Excel workbook instance and open it
Excel.Workbook excelWorkBook = excelApp.Workbooks.Add(Type.Missing);
Excel.Worksheet excelWorkSheet = excelWorkBook.Sheets.Add(Type.Missing);
excelWorkSheet.Name = "Sheet1";

int column = 1;
foreach (DataGridViewColumn dgc in dgvVolunteers.Columns)
{
    // row 1 contains column headers
    excelWorkSheet.Cells[1, column] = dgc.HeaderCell.Value;// copy headers from my DataGridView
    ++column;
}
column--;   // last Excel column is one less than the grid

int row = 2;    // data in Excel starts at A2
// copy from all the rows and columns in the DataGridView
// to the corresponding cells in the Worksheet
foreach (DataGridViewRow dgr in dgvVolunteers.Rows)
{
    for (column = 0; column < dgr.Cells.Count; ++column)
    {
        excelWorkSheet.Cells[row, column + 1] = dgr.Cells[column].Value;
    }
    row++;
}
// Save the new Workbook
excelWorkBook.SaveAs("MyFileName.xlsx");
excelWorkBook.Close();

manju 34-Feb-19 23:49