Visual Basic

Re: Split cells with comma and space

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

Re: Split cells with comma and space

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

datagridview that was bound to dataset

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

Macro Substitution en VB.Net

4-Feb-19 23:49

Thanks richard.
I will try it out.

Thanks
Manju

Member 141390043-Feb-19 17:00

3-Feb-19 17:00

In FoxPro you can use the ampersand character to indicate macro substitution. For example:

TableName = "Companies"
FieldName = "Location"
Use &TableName index &FieldName

FoxPro would see the '&' and substitute "Companies" in place of &TableName and "Location" in place of &FieldName.

Is there a way to do something similar in VB.NET?

Eddy Vluggen3-Feb-19 22:45

Eddy Vluggen

3-Feb-19 22:45

Nope. You can execute your query by giving it a string. You can manipulate this command-string to your hearts content.

Bastard Programmer from Hell Suspicious | :suss:

If you can't read my code, try converting it here[^]
"If you just follow the bacon Eddy, wherever it leads you, then you won't have to think about politics." -- Some Bell.

Member 141390049-Feb-19 13:24

9-Feb-19 13:24

Thanks eddy, very kind.

Member 141390043-Mar-19 6:18

3-Mar-19 6:18

Gracias...

Eddy Vluggen3-Mar-19 6:28

Eddy Vluggen

3-Mar-19 6:28

You're welcome Smile | :)

Bastard Programmer from Hell Suspicious | :suss:

If you can't read my code, try converting it here[^]
"If you just follow the bacon Eddy, wherever it leads you, then you won't have to think about politics." -- Some Bell.

Maciej Los5-Feb-19 2:38

Maciej Los

5-Feb-19 2:38

I agree with Eddy Vluggen that there's no FoxPro macro substitution in vb.net, but depending on data provider you can use:
1) sql variables
2) CommandType to define:
    - StoredProcedure - the name of a stored procedure,
    - TableDirect - the name of a table,
    - Text - any SQL command (text - Default).
and
3) Command.Parameters

For example:

Dim sConn As String = "properconnectionstring"
Dim oRdr As OleDbDataReader
Dim dt As DataTable = New DataTable()
Dim empIdToFind As Integer = 111

Using oConn As OleDbConnection = New OleDbConnection(sConn)
    oConn.Open()
    Using oComm AS OleDbCommand = New OleDbCommand()
        oComm.Connection = oConn
        oComm.CommandType = CommandType.Text
        oComm.CommandText =  "SELECT * FROM MyUsers WHERE EmpId=@empid;"
        oComm.Parameters.AddWithValue("@empid", empIdToFind)
        oComm.ExecuteNonQuery()
        oRdr = oComm.ExecuteReader()
        dt.Load(oRdr)
    End Using
    oConn.Close()
End Using

Member 141390049-Feb-19 13:25

9-Feb-19 13:25

thanks Maciej, extraordinary explanation.

Member 141390043-Mar-19 6:17

Compute Sum on a column based on criteria

3-Mar-19 6:17

Gracias...

Member 1412885424-Jan-19 6:54

Member 14128854

24-Jan-19 6:54

I want to calculate sum of Net_Amount based on TC_Group='food' using cheque_number as base criteria using VB code
Actual Sheet

CHEQUE_NUMBER	TC_GROUP	NET_AMOUNT
A001477920190111084600	FOOD	       1500
A001477920190111084600	FOOD	        1500
A001477920190111084600	TAX	       141.75
A001477920190111084600	TAX	       141.75
A001477920190111084600	TIPS	        75
A001478120190111103600	FOOD	       1500
A001478120190111103600	TAX	       141.75
A001478120190111103600	TAX	       141.75
A001478120190111103600	TIPS	        75



Desired Output	
	
CHEQUE_NUMBER	       TC_GROUP	 NET_AMOUNT
A001477920190111084600	FOOD	 3000
A001478120190111103600	FOOD	 1500

Richard Deeming24-Jan-19 8:41

Richard Deeming

24-Jan-19 8:41

Where is the data stored?

What have you tried?

Where are you stuck?

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

Member 1412885424-Jan-19 17:47

Member 14128854

24-Jan-19 17:47

Data is stored in excel sheet

Ralf Meier24-Jan-19 19:58

Ralf Meier

24-Jan-19 19:58

you want to do this inside Excel ... or inside a Visual Studio Application ?

Member 1412885424-Jan-19 21:07

Member 14128854

24-Jan-19 21:07

for current scenario excel only

Unable to load xlsm file with Office 2016

Ralf Meier24-Jan-19 21:22

Ralf Meier

24-Jan-19 21:22

You don't need VBA for this.
Inside your sheet you can use the function 'SUMIF' for this ...

manju 323-Jan-19 20:18

Re: Unable to load xlsm file with Office 2016

23-Jan-19 20:18

Hi Team,
I have a existing .xlsm file which I was able to open in older excel versions(like 2010) successfully.
But i am unable to open the same .xlsm file in newer office versions 2016.OPening as blank.

Tried changing the settings under Excel->File->Options->Advanced
Under "General"
->Unchecking the "DDE"

Under "Display :"
->Unchecked the "Disable hardware graphics acceleration"

->Turn off add-ins.

Anyone faced this issue ?
Any help will be really appreciated.

Thanks in advance.

Thanks
Manju

Richard MacCutchan23-Jan-19 22:40