|
This is more of a VB thing because I am planning to execute it inside of the SQL 2000 DTS package but I am testing it in VB.Net app. It has been a while since I've worked with XML and I got stumbled with a simple one here!!
I just need to read Detail/DT and Detail/Accounts XML in the loop - I don't think my XPath is correct.
Somehow data that I read is not pasted into Excel document. What am I doing wrong. Please help.
Many Thanks!!!
<code>
<report xmlns="_x0031_000ImprAccount" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemalocation="_x0031_000ImprAccount " name="10Impr">
<table1>
<detail_collection>
<detail dt="2008-01-01T00:00:00" accounts="203" />
<detail dt="2008-01-02T00:00:00" accounts="219" />
<detail dt="2008-01-03T00:00:00" accounts="224" />
</detail_collection>
</table1>
</report>
</code>
xlapp = CreateObject("Excel.Application")
xlapp.Workbooks.Add()
xlapp.ScreenUpdating = False
DateMsg = "Generated on - " + FormatDateTime(Now(), 0)
' Load the xml file
strFileName = "C:\test.xml"
oDoc = CreateObject("MSXML2.DOMDocument")
oDoc.async = False
oDoc.validateOnParse = True
oDoc.Load(strFileName)
Detail = "Detail"
If oDoc.parseError.errorCode = 0 Then
R = 0
C = 0
xPath = "//Detail_Collection"
DetailCollection = oDoc.selectNodes(xPath)
Dim bookTitle, bookAuthor
For Each Detail In DetailCollection
DetailAccount = Detail.SelectSingleNode("Detail[@Accounts]").text ' Not sure if XPath is correct here..
xlapp.ActiveCell.Offset(R, C).Value = DetailAccount ' Copy to Excel
Next
End If
'Save Excel File
Dim filo
Dim Awkbk
Dim savefile
filo = "C:\testexcel.xls"
Awkbk = xlapp.ActiveWorkbook
savefile = Awkbk.SaveAs(filo, -4143, , , , , )
<code></code>
|
|
|
|
|
Although this may not be the answer you were looking for, let me tell you about the solution I use. I also needed to create excel reports on a regular basis, and I was on the edge of despair, because creating data directly into an Excel file just wouldn't work. I found three different methods suggested by Microsoft and many similar ones from coders all over the web, but could not get anything to work.
In the end someone here on the forum explained this method to me, that is slightly more clumsy to build at first, but will give you much more control over the output afterwards, is a lot less sensitive to (MS Excel) version problems, and can be easily adapted to work from any programming language:
First create an Excel file (in excel) that looks the way you want it to look. Next save this Excel file as xml. And last open this xml file with .NET Studio.
Finally (or alternatively) you can adapt the following function to your needs (the example is from one of my apps, so there is some stuff in there you may want to take out):
Public Function DatasetToExcel(ByVal ds As DataSet, ByVal ExcelPath As String)<br />
If File.Exists(ExcelPath) = True Then<br />
If MsgBox("Tiedosto on jo olemassa. Haluatko korvata sen?", MsgBoxStyle.YesNo) = MsgBoxResult.Yes Then<br />
File.Delete(ExcelPath)<br />
Else<br />
ExcelPath = InputBox("Muuta nimi: ", "Tiedosto olemassa", ExcelPath)<br />
End If<br />
End If<br />
Dim doc As New StreamWriter(ExcelPath) 'use .xls even though its an xml file<br />
Try<br />
Dim startExcelXML As String = ""<br />
startExcelXML &= "<xml version="">"<br />
startExcelXML &= vbNewLine & "<workbook xmlns="" urn:schemas-microsoft-com:office:spreadsheet="" hold=" /"> startExcelXML &= vbNewLine & Microsoft.VisualBasic.Chr(10) & "" + " xmlns:o=""urn:schemas-microsoft-com:office:office"""<br />
startExcelXML &= vbNewLine & "xmlns:x=""urn:schemas- microsoft-com:office:" + "excel"""<br />
startExcelXML &= vbNewLine & "xmlns:ss=""urn:schemas-microsoft-com:" + "office:spreadsheet"">"<br />
<br />
'write the styles tags that format the data and cells properly<br />
startExcelXML &= vbNewLine & "<styles>"<br />
<br />
startExcelXML &= vbNewLine & " <style ss:id="" default="" ss:name="" normal="">"<br />
startExcelXML &= vbNewLine & " <alignment ss:vertical="" bottom="" />"<br />
startExcelXML &= vbNewLine & " <borders />"<br />
startExcelXML &= vbNewLine & " <font />"<br />
startExcelXML &= vbNewLine & " <interior />"<br />
startExcelXML &= vbNewLine & " <numberformat />"<br />
startExcelXML &= vbNewLine & " <protection />"<br />
startExcelXML &= vbNewLine & " </style>"<br />
<br />
startExcelXML &= vbNewLine & "<style ss:id="" colheader="">"<br />
startExcelXML &= vbNewLine & "<alignment ss:horizontal="" center="" ss:vertical="" bottom="" />"<br />
startExcelXML &= vbNewLine & "<font x:family="" swiss="" ss:size="" 8="" ss:bold="" 1="" />"<br />
startExcelXML &= vbNewLine & "<interior ss:color="" #c0c0c0="" ss:pattern="" solid="" />"<br />
startExcelXML &= vbNewLine & "</style>"<br />
<br />
startExcelXML &= vbNewLine & " <style ss:id="" reg="">"<br />
startExcelXML &= vbNewLine & " <font x:family="" swiss="" ss:bold="" 0="" />"<br />
startExcelXML &= vbNewLine & " </style>"<br />
<br />
startExcelXML &= vbNewLine & "</styles>"<br />
'write the header to the file<br />
doc.WriteLine(startExcelXML)<br />
'write one sheet per table<br />
For Each tab As Data.DataTable In ds.Tables<br />
Dim sheetname As String = "<worksheet ss:name="<br mode=" hold=" /"> sheetname &= Microsoft.VisualBasic.Chr(34)<br />
sheetname &= tab.TableName.Trim<br />
sheetname &= Microsoft.VisualBasic.Chr(34)<br />
sheetname &= ">"<br />
doc.WriteLine(sheetname)<br />
doc.WriteLine("<table>")<br />
'write the column headers<br />
doc.WriteLine("<row>")<br />
For Each col As DataColumn In tab.Columns<br />
doc.Write("<cell ss:styleid="" colheader=""><data ss:type="" string="">")<br />
doc.Write(col.ColumnName)<br />
doc.WriteLine("</data></cell>")<br />
Next<br />
doc.WriteLine("</row>")<br />
'write the table<br />
For Each row As DataRow In tab.Rows<br />
doc.WriteLine("<row>")<br />
'Dim i As Integer<br />
For i As Integer = 0 To tab.Columns.Count - 1<br />
doc.Write("<cell ss:styleid="" reg=""><data ss:type="" string="">")<br />
doc.Write(row.Item(i))<br />
doc.WriteLine("</data></cell>")<br />
Next<br />
doc.WriteLine("</row>")<br />
Next<br />
doc.WriteLine("</table>")<br />
doc.WriteLine("</worksheet>")<br />
Next<br />
doc.WriteLine("</workbook>")<br />
doc.Close()<br />
Catch Ex As Exception<br />
doc.Close()<br />
EC(Ex, "RV, DatasetToExcel")<br />
End Try<br />
End Function</xml>
Hope this helps you on your way.
Johan
My advice is free, and you may get what you paid for.
|
|
|
|
|
Hi all,
I need to run dos commands like copy,del through my vb.net program.
can any one plz help me with the syntax..
Thanks in advance,
M.Saravana...
|
|
|
|
|
Use the System.Process.Start[^] method.
Paul Marfleet
"No, his mind is not for rent
To any God or government"
Tom Sawyer - Rush
|
|
|
|
|
vb.net/ .net framework 1.1
i am overriding an inherited datagrid's wndproc and want to catch the message for a change of grid's currentrowindex or currentcell.
does anybody know these messages' constants?
|
|
|
|
|
I have many VS 2003 and 2005 (VB) projects being used within our organization. These applications are windows forms based and not web. The applications are click-once based and when I publish updates to the server, the clients can get updated automatically.
This all works great until a few months later. When installing the applications the screens warn that the application is not a trusted publisher, but I continue to install past this. The application seems to setup a temporary key/certificate that expires in a few months. When I make a change after the expiration date, I can obtain another temporary certificate from the certificate store for a few more months, but all my click once clients crash because the identity has changed.
My boss and his boss say they can buy a certificate for a specific server, but to me that doesn't make sense. Wouldn't you buy a certificate for the company and any application we create can use that certificate no matter where our client is executing the program.
I'm hoping someone can point me in the right direction.
Confused and not trusted
Lost in the vast sea of .NET
|
|
|
|
|
It is possible for you to create your own internal certificate, dammed if I can find the link though, sorry. Note that you can set the key expiry to 2050
When starting a development step 1 is to include the key. As yours is already deployed you are giong to have to
create aand include the new key (set a long expiry date)
remove the existing key
uninstall ALL clients to get rid of the old version
deploy the new version
We had a lot of trouble changing keys on an existing app but persevered till the thing did as it was told - good luck
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
If you don't have the link, do you remember how you created the certificate. I'm trying to use the makecert.exe utility, but when I use the parameter to specify a store name to put the certificate in, I don't see it using Certmgr.exe or in Visual Studio. I used the syntax below to at least create a file on my system that I could find and try to pull into my application, but I'm having no success.
makecert.exe -n "CN=VBAppsCertificate" -e 01/01/2070 -b 01/29/2008 -pe testCert.cer
If you remember the process you took to create the certificate please let me know.
I appreciate your help. It really sounds like it is a solution to our problem. I'm just not understanding all the Makecert.exe parameters and once I create the certificate I can't seem to find it.
Thanks
Lost in the vast sea of .NET
|
|
|
|
|
This is from the dev in our group who did the work!
Method 2 : as requested by Mark
1. Create your own certificate and pvKey. using Make Cert
run in .NET command prompt
makecert -n "CN=SomeName" -pe -b 01/01/2007 -e 12/31/2039 -sv c:\SomeName.pvk c:\SomeName.cer
2. Generate pfx:
run in .NET command prompt
pvk2pfx.exe -pvk c:\SomeName.pvk -pi password -spc c:\SomeName.cer -pfx c:\SomeName.pfx -po password -f
I tested this and applied it to a new window application. need to test this out on actual application and deployment...
The advantage of this is we can make use of a single cert for ALL our applications, but we have to do a uninstall for all the existing client and install the app with the new certificate.
References :
Creating and publish own cert
Makecert
pvk2pfx Tool
I hopwe this helps!
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Thanks!!!
I followed the steps and created the certificate. I tested it out using a Visual Studio application and it works great!
Thanks again
Lost in the vast sea of .NET
|
|
|
|
|
im trying to use the system.thread.sleep function to display data in a gridview with a 3 second delay however results are not being displayed. It seems to be a problem with the system
===========================================================================================
Protected Sub Button1_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles Button1.
Dim ds As DataSet = New DataSet
ad.Fill(ds, "tblcustomers")
Dim dtab As DataTable = ds.Tables.Add(0)
Dim dtabclone As DataTable = dtab.Clone()
Dim drow As DataRow = dtab.Rows(0)
dtab.Clone.ImportRow(drow)
GridView1.DataSource = dtab.Clone
GridView1.DataBind()
System.Threading.Thread.Sleep(3000)
drow = dtab.Rows(1)
dtab.Clone.ImportRow(drow)
GridView1.DataSource = dtab.Clone
GridView1.DataBind()
<div class="ForumMod">modified on Monday, January 28, 2008 10:57:23 AM</div>
|
|
|
|
|
solarthur01 wrote: It seems to be a problem with the system
No. It is a problem with your code.
You are adding the rows on the UI thread. You then tell the UI thread to sleep, so while the thread is asleep it cannot do anything.
Add the data on a separate thread that you put to sleep so the UI thread has a chance to update itself.
|
|
|
|
|
i dont quite understand what i need to do.
|
|
|
|
|
Hi All,
I have a table (tblEmp) from which I am exporting data to a excel sheet.The table contains 5 fields(empid,empname,age,address,accountno)
Issue:
Now I want that when ever I export the data from tblEmp table , the accountno field should come as **** in the excel sheet
Please let me know how can I do this by vb.net.
Thanks in advance.
Pradip Kishore
|
|
|
|
|
AFIAK, there is no built in way to do that. You could do a for each on the string char array, and replace each char with an asterisk. Also, you could take a look at the getpass module. And there is always google, for such a simple problem.
I'm going to become rich when I create a device that allows me to punch people in the face over the internet.
"If an Indian asked a programming question in the forest, would it still be urgent?" - John Simmons / outlaw programmer
|
|
|
|
|
Sorry if this is the wrong place. I did not see a seperate VBA forum.
I am trying to launch a seperate process in an Outlook VBA script.
System.Diagnostic.Process is not recognised.
Any Ideas?
I would like to read the output from the launched process.
Thanks in Advance,
WT
|
|
|
|
|
Hi i'm looking for a way that can allow me to control the left button of the mouse .
for example : how to make the mouse to be pressed when i click on the button enter
Thank you
|
|
|
|
|
Your question is rather vague, so I'll do a little guess work: You have a windows application that does something when you click the left mouse button anywhere on the window or on a specific control. This application is run in an environment with a cat, which is not being fed enough. So on a regular basis you are forced to press the enter key on your keyboard instead of clicking the left mouse button.
Generally speaking the answer is quite simple: get rid of the cat. But seriously, put the code that you want to execute, when normally you press the left mouse button, in its own sub or function, and then instead of writing this same code directly under the mouse button click event, you only call the sub or function. Same thing with the KeyPress event, except that you may first have to check that the button pressed is actually the enter key (If e.KeyChar = Chr(13) Then ). As a result you only have to write the code once, and you can make pretty much any event execute it.
If this isn't at all what you meant, then I suggest being more specific, otherwise I hope it helps.
Johan
My advice is free, and you may get what you paid for.
|
|
|
|
|
Hi,
I'm building a data exchange app using sockets, using asynchronous sockets one app listens for incoming connections, and the other one connects to the first and sends some data to the other.
The code for the listener app:
Private Sub startListening()
Dim iphostinfo As IPHostEntry = Dns.GetHostEntry(Dns.GetHostName())
Dim localEP As New IPEndPoint(iphostinfo.AddressList(0), 8000)
listener = New Socket(localEP.Address.AddressFamily, SocketType.Stream, ProtocolType.Tcp)
Try
listener.Bind(localEP)
listener.Listen(5)
listener.BeginAccept(New AsyncCallback(AddressOf acceptCallback), listener) 'Pocni asinkrono primat nadolazece podatke
While True
If abort = True Then
cleanUp()
Application.Exit()
End If
Application.DoEvents()
End While
Catch ex As Exception
End Try
End Sub
Private Sub acceptCallback(ByVal ar As IAsyncResult)
Try
listener.EndAccept(ar)
RaiseEvent status("Konekcija uspostavljena")
Application.DoEvents()
timeout.Stop()
timeout.Enabled = False
'Velicina Buffera
Dim bytes(1024) As Byte
listener.BeginReceive(bytes, 0, bytes.Length, SocketFlags.None, AddressOf receiveCallback, bytes) ' HERE IT CRASHES : the error is written bellow
Catch ex As Exception
RaiseEvent status(ex.Message)
Application.DoEvents()
End Try
End Sub
Private Sub receiveCallback(ByVal ar As IAsyncResult)
Dim bytes() As Byte = CType(ar.AsyncState, Byte())
Dim numBytes As Int32 = listener.EndReceive(ar)
If numBytes = 0 Then 'Ako nema više podataka onda iskljuci socket i spremi dobiveni file
listener.Shutdown(SocketShutdown.Both)
listener.Close()
spremiFile(data)
Else
If prviPodatak = False Then
ReDim Preserve data(data.Length + 1024)
prviPodatak = False
End If
prviPodatak = False
Array.Copy(bytes, 0, data, data.Length, bytes.Length)
'--Ocisti buffer
Array.Clear(bytes, 0, bytes.Length)
'-- Opet pocni primat podatke
listener.BeginReceive(bytes, 0, bytes.Length, SocketFlags.None, AddressOf receiveCallback, bytes)
End If
End Sub
You will see a comment in the code where it crashes. The apps conenct to each other but when it's time to beginReceive (marked in the code) it crashes with the error:
"A request to send or receive data was disallowed because the socket is not connected and(when sending on a datagram socket using a sendTo call)no address was supplied.
Any clues?
The server part (the part that sends data)
Private Function salji(ByVal port As Integer, ByVal data() As Byte, ByVal ipAdresa As String) As Boolean
Dim addr As IPAddress = Dns.GetHostEntry(ipAdresa).AddressList(0)
If Not addr Is Nothing Then
Dim ep As New IPEndPoint(addr, port)
cSocket = New Socket(AddressFamily.InterNetwork, SocketType.Stream, ProtocolType.Tcp)
cSocket.BeginConnect(ep, AddressOf connectCallback, Nothing)
End If
Do
If spoj = stateConn.nijeSpojen Then
ElseIf spoj = stateConn.greska Then
Return False
Else
cSocket.Send(data)
Return True
End If
Loop
End Function
Private Sub connectCallback(ByVal ar As IAsyncResult)
Try
cSocket.EndConnect(ar) ' It passes this part and exits the subs like everything is OK . There is no error on this part only on the listener part (of course, when it tries to send data it can't bacause there is an error in the listening app)
spoj = stateConn.spojen
Catch ex As Exception
spoj = stateConn.greska
End Try
End Sub
Before this I tried Synchronous method. And it works. But for many reasons It must be asynchronous.
Any help greatly appreciated,
Thank you!
|
|
|
|
|
Plz anybody knows hot to store images in my.settings
i tried several settings types but nones takes the byte() or image data format
plz any help?
phatkin
|
|
|
|
|
Hi,
For what purpose will the images be used? You can store the images as part of an embedded resource (resx) file, or if you are dealing with a very small number of images, they can be added directly to the project and embedded and then accessed via reflection.
Clean code is the key to happiness.
|
|
|
|
|
I want to be able to change the images programmatically
The images are the company's logo and they can change it anytime
Thanks
phatkin
|
|
|
|
|
Why do you not store the path to the image file rather than the image itself?
Steve Jowett
-------------------------
Sometimes a man who deserves to be looked down upon because he is a fool, is only despised only because he is an 'I.T. Consultant'
|
|
|
|
|
I'm using ASP.Net 2.0. I need to show data from a single field (as a
hyperlink) but the column is to be repeated (RepeatColumns = 2). I have
this in a DataList control and it is working fine. There is an additional requirement now to add a pager control, but it seems like I would have to use a GridView instead of a DataList in order to allow paging. The GridView doesn't have the capability to repeat columns however.
|
|
|
|
|
I want to check whether folder exists or not in a particular path from my code.
If anyone has experience about how to check (eg . function ), pls tell me.
phoo pwint
flowerppk
|
|
|
|
|