Remove whitespace in front of String element of an Array (vba, excel macro) - Visual Basic Discussion Boards

Re: Error 2023 in Range

DaveAuld16-Jul-10 3:56

16-Jul-10 3:56

What you are trying to do is set the rFoundCell_Value to an range object, i think that is why you get the error.

have you tried, rFoundCell_Value = rFoundCell.offset(0,1).value

Look at the code i provided to a similiar question earlier, it uses values, ranges, offsets

http://www.codeproject.com/Messages/3536297/Re-Remove-whitespace-in-front-of-String-element-of.aspx[^]

Dave

Don't forget to rate messages!
Find Me On: Web|Facebook|Twitter|LinkedIn
Waving? dave.m.auld[at]googlewave.com

Re: Error 2023 in Range

priyaahh17-Jul-10 7:08

priyaahh

17-Jul-10 7:08

Hi Dave,

Yes I am trying to set value to an range object..I have changed and working fine..

Thanks.

Data binding

waner michaud15-Jul-10 11:19

waner michaud

15-Jul-10 11:19

Hi,

Please help me this problem

I have these tables:

CommissariatTD(CommissariatID, comm_name,region, DeptID, Deptname)
DepartmentTD(DeptID, region, Deptname)

I have a form that binds to CommissariatTD_view where I do this:

a RegionCombobox with these items(North,south,east,west)

a DepartmentCombobox

CommissariatTD_view datagrid

Here is my problem:

let's say I select a region on the regioncombobox, it loads the record on the grid but on the departmentcombobox, nothing shows. I bind it with departmentbindingsource, selected value is from CommissariatTD_view--DeptID

it should load all departments for that region.

**Note we can have more than one department on a region......
if we have more than one dept. when select a dept. the grid should show only the commissariats for that department and region..

I am missing something now. Please guide me.

Thanks in advance

Best regard..

Re: Data binding

AnnieMacD15-Jul-10 12:16

AnnieMacD

15-Jul-10 12:16

I don't understand the layout of your two tables. The fields 'region' and 'Deptname' appear in both tables and I'm guessing that they are never populated in CommissariatDT. You only need to have the field 'DeptID' common to both tables and do the join on that.

What SELECT statement are you using to create your view?

It’s not because things are difficult that we do not dare, it’s because we do not dare that things are difficult. ~Seneca

vb script code

henkbongers15-Jul-10 10:57

henkbongers

15-Jul-10 10:57

I like to make a command button in a word-document to submit email

Re: vb script code

DaveAuld15-Jul-10 12:38

DaveAuld

15-Jul-10 12:38

result from Google;

http://word.mvps.org/faqs/interdev/sendmail.htm[^]

Dave

Don't forget to rate messages!
Find Me On: Web|Facebook|Twitter|LinkedIn
Waving? dave.m.auld[at]googlewave.com

Re: vb script code

Smithers-Jones16-Jul-10 1:07

Smithers-Jones

16-Jul-10 1:07

henkbongers wrote:
I like to make a command button in a word-document to submit email

Nobody's gonna stop you.

"I love deadlines. I like the whooshing sound they make as they fly by." (DNA)

Remove whitespace in front of String element of an Array (vba, excel macro)

TabascoSauce15-Jul-10 6:02

TabascoSauce

15-Jul-10 6:02

I created a macro that gets a list of elements (strings), puts them into an array, then creates an appropriate number of worksheets each named after an element in the array.

Everything works fine. However, occationally when dealing with this list of elements, they are arranged in a sort of list-sublist-evenmoresublist pattern, meaning that there is some variable number of whitespace in front of some of the elements.

I know this is fairly simple, I am not entirely new to the programming world, but I am new to vba in Excel and from prior experiences working with macros, there is a ton of built in functions that I am unaware of and they makes tasks like these pretty simple. (excuse the run-on Big Grin | :-D

)

Here is the block that handles my count elements/load into array (feel free to correct it if there is a simpler route):

'Count Elements, dimension element array to appropriate size
    n = 7
    eCount = 0
    Do While Worksheets("Costs Incurred").Cells(n, 2) <> ""
        If Worksheets("Costs Incurred").Cells(n, 2) <> "" Then
            eCount = eCount + 1
        End If
        n = n + 1
    Loop
    ReDim elements(1 To eCount)
    
'Load Element names into Element Array
    n = 1
    Do While n <= eCount
        elements(n) = Worksheets("Costs Incurred").Cells(n + 6, 2)
        n = n + 1
    Loop

Thanks for any help!

Re: Remove whitespace in front of String element of an Array (vba, excel macro) [modified]

DaveAuld15-Jul-10 6:07

DaveAuld

15-Jul-10 6:07

Why create an array to store the elements and then create the worksheets for the elements, do the whole lot at once.

use the ~~isBlank()~~ isEmpty() to test the cells contain no value and use that as a stop for the routine.
use the cell.value to get the text of the cell
use trim() to remove whitespace from the value before creating the worksheet with the name.

I am just away to footy, or i would knock up a routine, but have a go yourself first, and i should be back in 3ish hrs if you still stuck.

Cheers the noo,

[edit: changed isBlank to isEmpty]

Dave

Don't forget to rate messages!
Find Me On: Web|Facebook|Twitter|LinkedIn
Waving? dave.m.auld[at]googlewave.com

modified on Thursday, July 15, 2010 6:26 PM

Re: Remove whitespace in front of String element of an Array (vba, excel macro)

DaveAuld15-Jul-10 12:25

DaveAuld

15-Jul-10 12:25

Ok, here is a sub that does wht you want;

Stick a button on the original form, and attach the code to it. Set the starting cell of the list of sheets you want to create.

Public Sub generateSheets()

    'Set the Starting cell
    Dim currentCell As Range
    Set currentCell = ThisWorkbook.Sheets("Sheet1").Range("D5")
        
    'The value of the cell to use as the sheet name
    Dim cellValue As String
    
    'Check the currentcell is not a blankcell
    While Not IsEmpty(currentCell)
        'get the value of the cell as a string to use as a sheet name
        cellValue = Trim(CStr(currentCell.Value))
        
        'Create a new worksheet and name it
        Dim newSheet As Worksheet
        Set newSheet = ThisWorkbook.Sheets.Add(after:=Sheets.Item(Sheets.Count))
        newSheet.Name = cellValue
        
        'Move to next row
        Set currentCell = currentCell.offset(1, 0)
    Wend
    
End Sub

Dave

Don't forget to rate messages!
Find Me On: Web|Facebook|Twitter|LinkedIn
Waving? dave.m.auld[at]googlewave.com

Re: Remove whitespace in front of String element of an Array (vba, excel macro)

Neville Nazerane20-Jul-10 3:00

Neville Nazerane

20-Jul-10 3:00

hey... this may not be an answer to your question but I just noticed that the if statement that you have used is totally unnecessary! The program will work in the exactly same way without it!

How to Maintain Copy of SortDescriptionCollection After DataGrid Rebind

D.Dubya15-Jul-10 3:36

D.Dubya

15-Jul-10 3:36

Hi all,

I'm using a DataGrid from the WPFToolkit v3.5 and I'm trying to reapply a user-defined sort following a refresh of the DataGrid. To do this, I'm trying to store the DataGrid's SortDescriptionCollection in a local variable and then re-add the SortDescriptions once the DataGrid has been rebound.

My problem is that the local variable seems to be more of a pointer to the dtg.Items.SortDescriptions collection rather than a copy and is reset back to zero once the DataGrid is rebound.

Here's the code:

1. Dim complexSortDesc = Me.dtgComplexQueue.Items.SortDescriptions
2. Dim numComplex As Integer = PopulateComplex().Count
3. ApplyCustomSort(dtgComplexQueue, complexSortDesc)
4. UpdateQueueTitle(numComplex)

When line #1 is completed, I have a count of 2 SortDescription objects in complexSortDesc. After line #2 has run, complexSortDesc has a count of zero.

Here's the PopulateComplex fn:

Private Function PopulateComplex() As Requests
  Dim complexRequests = New Requests(QueueType.Complex)
  complexRequests.Sort(New DefaultSortByImportDateAndSurname)
  dtgComplexQueue.ItemsSource = New TriageMenuDataMembers(complexRequests)
  Return complexRequests
End Function

How can I get a copy of the SortDescriptionCollection so that the collection exists after the DataGrid has been rebound? I don't seem to have this problem in other areas of my app so I'm thinking it has something to do with the PopulateComplex fn..? Confused | :confused:

Thanks

How to execute SQL Query inside DataSet?

Аslam Iqbal14-Jul-10 10:10

Аslam Iqbal

14-Jul-10 10:10

I want to execute SQL Query within a Dataset. I don't know how it should be done.
I just have some sample Query & sample data.

'test.xml file Contains 3 columns:
        'ID, Name, Location
        'Sample Values:
        ''ID, Name, Location
        ''1,  abc,  loca
        ''2,  ijk,  locb
        ''3,  xyz,  locc
        ''4,  uvw,  locd
        Dim ds As New DataSet
        ds.ReadXml("test.xml")

        'Sql Query is: "Select Name fom ??? where ID=1"
        'Is this possible to to retrive the output of that Sql query from test.xml using DataSet? 
        'If DataSet does not support this type of operation then how can I execute that or any other query?

Here Is the test.xml File:

<?xml version="1.0" standalone="yes"?>
<DataSetSchema>
  <Tab1>
    <ID>1</ID>
    <Name>abc</Name>
    <Location>loca</Location>
  </Tab1>
  <Tab1>
    <ID>2</ID>
    <Name>ijk</Name>
    <Location>locb</Location>
  </Tab1>
  <Tab1>
    <ID>3</ID>
    <Name>xyz</Name>
    <Location>locc</Location>
  </Tab1>
  <Tab1>
    <ID>4</ID>
    <Name>uvw</Name>
    <Location>locd</Location>
  </Tab1>
</DataSetSchema>

Re: How to execute SQL Query inside DataSet?

Wayne Gaylard14-Jul-10 20:57

Wayne Gaylard

14-Jul-10 20:57

A dataset returns a collection of DataTables, in this case it would only contain one Table. I don't think it is possible to query DataSets using SQL. You would need to iterate through the table until you found an ID matching 1 and retrieve the name. Personally I prefer to use XML literals and LinQ to retrieve data from xml files like this

VB.NET

Dim datatest As XDocument = XDocument.Load("Test.xml")
        For Each student In From element In datatest...<Tab1>
            If student...<ID>.Value = 1 Then
                name = student...<Name>.Value
                Exit For
            End If
        Next
        MsgBox(Name)

That makes everything very concise and readable.
Hope this helps.
Happy Coding

Re: How to execute SQL Query inside DataSet?

Аslam Iqbal14-Jul-10 21:53

Аslam Iqbal

14-Jul-10 21:53

Is that only one way to do quering a dataset?
I tried Dataset.Select("ID=1") but it is not enought for my requirement.
anyway thanks.

Re: How to execute SQL Query inside DataSet? [modified]

DaveAuld15-Jul-10 4:04

DaveAuld

15-Jul-10 4:04

You could always bind the data to a bunch of controls e.g. bindingsource, bindingnavigator and datagridview and then use the FILTER property of the BindingSource, as it acts just like a 'where' clause in SQL.

Dave

Don't forget to rate messages!
Find Me On: Web|Facebook|Twitter|LinkedIn
Waving? dave.m.auld[at]googlewave.com

modified on Thursday, July 15, 2010 10:10 AM

Archive or backup

Ebube14-Jul-10 5:15

Ebube

14-Jul-10 5:15

i have an examination system that will be run twice a year. some of the tables are setup some are transactional. should i archive or backup at end of each processing

Re: Archive or backup

Luc Pattyn14-Jul-10 5:17

Luc Pattyn

14-Jul-10 5:17

yes, obviously.

Smile | :)

Luc Pattyn [Forum Guidelines] [Why QA sucks] [My Articles] Nil Volentibus Arduum

Please use <PRE> tags for code snippets, they preserve indentation, and improve readability.

Re: Archive or backup [modified]

Ebube14-Jul-10 6:00

Ebube

14-Jul-10 6:00

i mean which onewill be better

modified on Wednesday, July 14, 2010 12:09 PM

Re: Archive or backup

Luc Pattyn14-Jul-10 6:39

Luc Pattyn

14-Jul-10 6:39

that sounds like a functional question; I can't decide what functionality you need, you should know.

Smile | :)

Luc Pattyn [Forum Guidelines] [Why QA sucks] [My Articles] Nil Volentibus Arduum

Please use <PRE> tags for code snippets, they preserve indentation, and improve readability.

Re: Archive or backup

DaveAuld15-Jul-10 4:10

DaveAuld

15-Jul-10 4:10

Surely that depends on couple of things;

1) is the old data still required to be accesssed regularly
2) is the storage system running out of space
there are probably other factors........

If the data is no longer required to be routinely accessed and/or you are short of space, then archive out to another medium.

As for backup, you should be running routine incremental or full backup whenever you feel it is appropriate given the value of the data and business impact in the event of a data interruption.

Dave

Don't forget to rate messages!
Find Me On: Web|Facebook|Twitter|LinkedIn
Waving? dave.m.auld[at]googlewave.com

how to use winsock to exchange data in 2 diferent exe

zhiyuan1613-Jul-10 18:39

zhiyuan16

13-Jul-10 18:39

dear all

this is first time for me to use winsock in vb6, i don't how to exchange data in 2 different exe, although i searched online, but i still didn't get what i expect. anyone can tell me this point--step by step. thanks a lot.

Re: how to use winsock to exchange data in 2 diferent exe

Eddy Vluggen14-Jul-10 3:07

Eddy Vluggen

14-Jul-10 3:07

zhiyuan16 wrote:
this is first time for me to use winsock in vb6, i don't how to exchange data in 2 different exe,

There's a basic example here[^]. Best way to go would be to analyze examples, and try to build something similar.

zhiyuan16 wrote:
although i searched online, but i still didn't get what i expect. anyone can tell me this point--step by step.

That begs the question on what you're expecting, and what the requirements for the communications are.

I are Troll Suspicious | :suss:

Re: how to use winsock to exchange data in 2 diferent exe

Аslam Iqbal14-Jul-10 9:01

Аslam Iqbal

14-Jul-10 9:01

Hi,
Following sample shows the use of Winsock in vb6. Just copy, paste & Test.
To Test in local mode (if currently u don't have two machines connected)
Make "yourproject.exe" & run in double instance then:
in 1 instance click Listen(Command2) And another click Connect(Command2).
Then Type text in Text3 and click on Send(Command3).

'Create a new project: Add 3 TextBox, 3 CommandButton And 1 Winsock. Do not change any of them's Name.
'Paste this whole string inside your form's code window
Option Explicit
'Text1= Remote IP/MachineName; for local 127.0.0.1
'Text2= Port Number; I used 1971; you should be sure that the given port is not in use

'Command1=Connect
Private Sub Command1_Click()
Winsock1.Connect Me.Text1, Me.Text2
End Sub

'Command2=Start Listening
Private Sub Command2_Click()
Winsock1.LocalPort = Me.Text2
Winsock1.Listen
End Sub

'Command3=Send Text
Private Sub Command3_Click()
'Text3= what you want to send
    Winsock1.SendData Me.Text3.Text
    'Me.Caption is used to display status
  Me.Caption = "Local Send" & Me.Text3.Text
End Sub

'Received text
Private Sub Winsock1_DataArrival(ByVal bytesTotal As Long)
 Dim AD As String
 Winsock1.GetData AD
 Me.Caption = "Remote: " & AD
 End Sub

'ConnectionRequest
Private Sub Winsock1_ConnectionRequest(ByVal requestID As Long)
    On Error Resume Next ' Just in case there's an error, continue with next command.
     Winsock1.Close ' Close any open socket (just in case).
     'Must accept the request to continue
    Winsock1.Accept requestID
 Me.Caption = "Request accepted"
 End Sub
 
'Chat session closed
Private Sub Winsock1_Close()
Me.Caption = "Closed"
End Sub

'Connected
Private Sub Winsock1_Connect()
 Me.Caption = "Chat Started at " & Time
End Sub

columntype checkbox does not chech

Ebube13-Jul-10 5:42

Ebube

13-Jul-10 5:42

i bind a datagridview(window form) to a datable. but i added extra checkbox column from design . i notice it does not check whn i run it

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.