|
|
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)
|
|
|
|
|
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  )
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!
|
|
|
|
|
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| LinkedInWaving? dave.m.auld[at]googlewave.com
modified on Thursday, July 15, 2010 6:26 PM
|
|
|
|
|
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()
Dim currentCell As Range
Set currentCell = ThisWorkbook.Sheets("Sheet1").Range("D5")
Dim cellValue As String
While Not IsEmpty(currentCell)
cellValue = Trim(CStr(currentCell.Value))
Dim newSheet As Worksheet
Set newSheet = ThisWorkbook.Sheets.Add(after:=Sheets.Item(Sheets.Count))
newSheet.Name = cellValue
Set currentCell = currentCell.offset(1, 0)
Wend
End Sub
|
|
|
|
|
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!
|
|
|
|
|
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..? 
Thanks
|
|
|
|
|
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.
Dim ds As New DataSet
ds.ReadXml("test.xml")
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>
|
|
|
|
|
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
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
|
|
|
|
|
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.
|
|
|
|
|
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| LinkedInWaving? dave.m.auld[at]googlewave.com
modified on Thursday, July 15, 2010 10:10 AM
|
|
|
|
|
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
|
|
|
|
|
yes, obviously.
|
|
|
|
|
i mean which onewill be better
modified on Wednesday, July 14, 2010 12:09 PM
|
|
|
|
|
that sounds like a functional question; I can't decide what functionality you need, you should know.
|
|
|
|
|
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.
|
|
|
|
|
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.
|
|
|
|
|
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 
|
|
|
|
|
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).
Option Explicit
Private Sub Command1_Click()
Winsock1.Connect Me.Text1, Me.Text2
End Sub
Private Sub Command2_Click()
Winsock1.LocalPort = Me.Text2
Winsock1.Listen
End Sub
Private Sub Command3_Click()
Winsock1.SendData Me.Text3.Text
Me.Caption = "Local Send" & Me.Text3.Text
End Sub
Private Sub Winsock1_DataArrival(ByVal bytesTotal As Long)
Dim AD As String
Winsock1.GetData AD
Me.Caption = "Remote: " & AD
End Sub
Private Sub Winsock1_ConnectionRequest(ByVal requestID As Long)
On Error Resume Next
Winsock1.Close
Winsock1.Accept requestID
Me.Caption = "Request accepted"
End Sub
Private Sub Winsock1_Close()
Me.Caption = "Closed"
End Sub
Private Sub Winsock1_Connect()
Me.Caption = "Chat Started at " & Time
End Sub
|
|
|
|
|
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
|
|
|
|
|
Normally this should work, even if your datatable (rows) is empty.
How did you work things out, exactly?
|
|
|
|
|
i have resolve. i need to check editing thanks
|
|
|
|
|
I am using the following borrowed code to insert an image into an RTF, as that aspect of RTF seemed way beyond me
[code]Public Sub InsertImage(ByVal _image As Image)
Dim _rtf As New StringBuilder()
' Append the RTF header
_rtf.Append(RTF_HEADER)
' Create the font table using the RichTextBox's current font and append
' it to the RTF string
_rtf.Append(GetFontTable(Me.Font))
' Create the image control string and append it to the RTF string
_rtf.Append(GetImagePrefix(_image))
' Create the Windows Metafile and append its bytes in HEX format
_rtf.Append(GetRtfImage(_image))
' Close the RTF image control string
_rtf.Append(RTF_IMAGE_POST)
Me.SelectedRtf = _rtf.ToString()
End Sub
Private Function GetImagePrefix(ByVal _image As Image) As String
Dim _rtf As New StringBuilder()
' Calculate the current width of the image in (0.01)mm
Dim picw As Integer = CType(Math.Round((_image.Width / xDpi) * HMM_PER_INCH), Integer)
' Calculate the current height of the image in (0.01)mm
Dim pich As Integer = CType(Math.Round((_image.Height / yDpi) * HMM_PER_INCH), Integer)
' Calculate the target width of the image in twips
Dim picwgoal As Integer = CType(Math.Round((_image.Width / xDpi) * TWIPS_PER_INCH), Integer)
' Calculate the target height of the image in twips
Dim pichgoal As Integer = CType(Math.Round((_image.Height / yDpi) * TWIPS_PER_INCH), Integer)
' Append values to RTF string
_rtf.Append("{\pict\wmetafile8")
_rtf.Append("\picw")
_rtf.Append(picw)
_rtf.Append("\pich")
_rtf.Append(pich)
_rtf.Append("\picwgoal")
_rtf.Append(picwgoal)
_rtf.Append("\pichgoal")
_rtf.Append(picw)
_rtf.Append(" ")
Return _rtf.ToString()
End Function
<DllImportAttribute("gdiplus.dll")> _
Private Shared Function GdipEmfToWmfBits(ByVal _hEmf As IntPtr, ByVal _bufferSize As UInt32, ByVal _buffer As Byte(), ByVal _mappingMode As Integer, ByVal _flags As EmfToWmfBitsFlags) As UInt32
End Function
''' <summary>
''' Wraps the image in an Enhanced Metafile by drawing the image onto the
''' graphics context, then converts the Enhanced Metafile to a Windows
''' Metafile, and finally appends the bits of the Windows Metafile in HEX
''' to a string and returns the string.
''' </summary>
''' <param name="_image"></param>
''' <returns>
''' A string containing the bits of a Windows Metafile in HEX
''' </returns>
Private Function GetRtfImage(ByVal _image As Image) As String
Dim _rtf As StringBuilder = Nothing
' Used to store the enhanced metafile
Dim _stream As MemoryStream = Nothing
' Used to create the metafile and draw the image
Dim _graphics As Graphics = Nothing
' The enhanced metafile
Dim _metaFile As Metafile = Nothing
' Handle to the device context used to create the metafile
Dim _hdc As IntPtr
Try
_rtf = New StringBuilder()
_stream = New MemoryStream()
' Get a graphics context from the RichTextBox
Using _graphics1 As Graphics = Me.CreateGraphics()
' Get the device context from the graphics context
_hdc = _graphics1.GetHdc()
' Create a new Enhanced Metafile from the device context
_metaFile = New Metafile(_stream, _hdc)
' Release the device context
_graphics1.ReleaseHdc(_hdc)
End Using
' Get a graphics context from the Enhanced Metafile
Using _graphics1 As Graphics = Graphics.FromImage(_metaFile)
' Draw the image on the Enhanced Metafile
_graphics1.DrawImage(_image, New Rectangle(0, 0, _image.Width, _image.Height))
End Using
' Get the handle of the Enhanced Metafile
Dim _hEmf As IntPtr = _metaFile.GetHenhmetafile()
' A call to EmfToWmfBits with a null buffer return the size of the
' buffer need to store the WMF bits. Use this to get the buffer
' size.
Dim _bufferSize As UInt32 = GdipEmfToWmfBits(_hEmf, 0, Nothing, MM_ANISOTROPIC, EmfToWmfBitsFlags.EmfToWmfBitsFlagsDefault)
' Create an array to hold the bits
Dim _buffer As Byte() = New Byte(CInt(_bufferSize)) {}
' A call to EmfToWmfBits with a valid buffer copies the bits into the
' buffer an returns the number of bits in the WMF.
Dim _convertedSize As UInt32 = GdipEmfToWmfBits(_hEmf, _bufferSize, _buffer, MM_ANISOTROPIC, EmfToWmfBitsFlags.EmfToWmfBitsFlagsDefault)
For i As Integer = 0 To _buffer.Length - 1
' Append the bits to the RTF string
_rtf.Append([String].Format("{0:X2}", _buffer(i)))
Next
Return _rtf.ToString()
Finally
If _graphics IsNot Nothing Then
_graphics.Dispose()
End If
If _metaFile IsNot Nothing Then
_metaFile.Dispose()
End If
If _stream IsNot Nothing Then
_stream.Close()
End If
End Try
End Function
[/code]
sorry for so much code, anyway if i use that, the image is huge, here is a sample untouched image
i tried inserting the image with wordpad and copying the picw, pich, pichgoal, and picwgoal values from the wordpad generated RTF, but then the image looked almost squeezed together. i did notice the image hex values seemed quite different, i can't simply copy as the image itself will be different each run, I am at a loss, any help is appreciated
|
|
|
|
|
Yuck.
Put your code in a code block and write in paragraphs.
Also, put the question at the top and refer to code below, it makes it much more likely people will help.
They will not want to wade through treacle to get to the question.
Anyways, hav you tried to shrink the image?
------------------------------------
I will never again mention that I was the poster of the One Millionth Lounge Post, nor that it was complete drivel. Dalek Dave
|
|
|
|
|
Formating the code would help but never mind. Looks like a typo.
djdrew wrote: Append values to RTF string
_rtf.Append("{\pict\wmetafile8")
_rtf.Append("\picw")
_rtf.Append(picw)
_rtf.Append("\pich")
_rtf.Append(pich)
_rtf.Append("\picwgoal")
_rtf.Append(picwgoal)
_rtf.Append("\pichgoal")
_rtf.Append(picw)
_rtf.Append(" ")
Should be :
_rtf.Append("{\pict\wmetafile8")
_rtf.Append("\picw")
_rtf.Append(picw)
_rtf.Append("\pich")
_rtf.Append(pich)
_rtf.Append("\picwgoal")
_rtf.Append(picwgoal)
_rtf.Append("\pichgoal")
_rtf.Append(pichgoal)
_rtf.Append(" ")
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
