|
Solution concept: I am doing a recursive search through directories to find Excel files and then silently open them to find all the macro code, copy the code and then store it.
Problem: When I open some Excel documents, some of these documents have external data connections (ODBC and XML Sharepoint Lists). When the refresh occurs prompts for data source credentials pop-up. Excel tries to refresh the data on this line:
ExcelWb = ExcelApp.Workbooks.Open(FileName, , True, )
before I can get the chance to iterate through the connection collection and disable refresh.
My code snippet for opening the Excel workbook is:
Dim ExcelWb As Microsoft.Office.Interop.Excel.Workbook
Dim ExcelApp As Microsoft.Office.Interop.Excel.Application
ExcelApp = New Microsoft.Office.Interop.Excel.Application
ExcelApp.DisplayAlerts = False
ExcelApp.EnableEvents = False
ExcelWb = ExcelApp.Workbooks.Open(FileName, , True, )
which is fine and everything else in the solution works.
The .ConnectionsDisabled property of the .Workbook object is a read only property, and as I say the refresh occurs in the .Open method before going to the next line of code, not giving me the opportunity to disable conneciton objects in the collection.
I am using the Interop namespace rather than the tools namespace, but I don't think that makes much of a difference regarding object methods and properties.
Is there a better way to manage this to disable the connection refresh?
|
|
|
|
|
Found a solution. Not necessarily the right way, but it works.
adding these two lines before opening the workbook seems to fix the issue.
ExcelApp.AutomationSecurity = 3
ExcelApp.AskToUpdateLinks = True
The first line completely disables all macros, by default security is set to Low.
Second Line, the default is to autmatically update links. By forcing a prompt even when the UI is supressed, the automation component continues and no noisy dialog boxes appear.
|
|
|
|
|
Hi,
I have a DataSet with two tables that have a common field.
Table 1 contains order header data and table 2 order row data. Both tables contain data of multiple orders.
I want to handle each order separately, and my current method is a loop inside a loop and a "manual" check. For example something like this:
For Each rwH as DataRow in ds.Tables("H").Rows
For Each rwR as DataRow in ds.Tables("R").Rows
If rwH.Item("KeyField").ToString = rwR.Item("KeyField").ToString Then
End If
Next
Next
The question is, is there a way to avoid having to loop through the rows, and use this sort of crudish comparison ?
I was thinking maybe creating a foreign key constraint. However, although I know how to create the constraint, I do not know how to use it to my advantage with any loop (assuming it is possible at all) ?
Or can someone suggest an alternative (better) method ?
All input is appreciated.
Cheers,
Johan
My advice is free, and you may get what you paid for.
|
|
|
|
|
Why not have a query that joins the two tables and groups by KeyField or something of that nature?
""Real programmers just throw a bunch of 1s and 0s at the computer to see what sticks" - Pete O'Hanlon
|
|
|
|
|
Please see my answer to Dave's post.
My advice is free, and you may get what you paid for.
|
|
|
|
|
Soooo, you're going to look at the same set of records (inner loop) over and over again, until you match them up with the records in the outer loop?? How many records in the outer loop again?? That seems AWFULLY inefficient.
Why not just create a SQL query that matches everything up for you?
|
|
|
|
|
It is awfully inefficient, hence the question. 
In this scenario I need to create orders into an ERP system via a proprietary driver.
That means that I need to declare a new "order" object, and fill in all its properties. Some header data (customer name, and address, and such), and an unknown number of rows (product number, quantity, price, etc.). In addition I need to translate that same information into an email message.
So if I combine both header and row data into a single query, I would have to populate the header data of the order object over and over, for each row, or build in some kind of is-it-already-populated method. Somehow that does not seem very efficient (or elegant) either (I may very well be wrong of course).
For clarity, the header table will generally only contain between 1 and 10 records, the rows table however can contain anywhere between 1 and 150 records per header record. Eliminating the nested loop is therefore my main concern here.
My advice is free, and you may get what you paid for.
modified 6-Jun-12 3:03am.
|
|
|
|
|
You can at least eliminate the nested loop with a SELECT:
dim drRows() as DataRow = ds.Tables("R").Select("KeyField = '" & rwH.Item("KeyField").ToString & "'")
If drRows.Length > 0 Then
End If
|
|
|
|
|
Thanks, I didn't know about this one. It seems to be exactly what I need.
My advice is free, and you may get what you paid for.
|
|
|
|
|
Several weeks ago you folks helped me with a problem and I have another problem I hope you can help with.
I created, with your help, a small program that spun three will like a slot machine and randomly placed 2 values and a sign, in picture boxes to create a very basic math problem to be solved. I did this for my 6 year old granddaughter and she LOVES IT!.
In fact she took the program to school and the teacher loved it as well. After a little work I jazzed up the program with some flashy graphics and sound. She wants to add the program to her teaching library. Before I give her a copy I wanted to add some animation to the reels by adding short AVI files. Creating the files was no problem, getting them to play was.
Can anyone steer me to some actually complete code that will play a AVI file in a picture box, that IS DOCUMENTED with remark statements so I can see what they are doing in the code.
On the same note is there a format the AVI file must be in to play? I learned that to play a wave file using the Autoplaymode function the wave file had to be in PCM format. Is there some such requirement for AVI files?
Thanks for your help
|
|
|
|
|
How do I make an os in vb2010 help tanks
|
|
|
|
|
You probably don't. However, there are plenty of articles around on writing your own OS, take a look here[^], or try Google.
|
|
|
|
|
You don't.
You're welcome.
Bastard Programmer from Hell 
|
|
|
|
|
If you're going to call yourself "code master", you better know what you're doing and why this is a bad idea.
Can it be done? Yeah, but with extreme difficulty. Check out Cosmos[^], a (mostly) C# written OS.
|
|
|
|
|
A very tall order there. You probably can make an OS in VB, but why? In the development of an OS, there's too much that goes on to just post.
""Real programmers just throw a bunch of 1s and 0s at the computer to see what sticks" - Pete O'Hanlon
|
|
|
|
|
No you can't; all you can make in a higher-level language is a virtual OS, one that runs on top of a real one. For a real OS, one needs pre-emptive scheduling which requires access to all the CPU registers, so one can swap one process/task/thread for another one, saving/restoring the state in some data structures. C#, Java, VB and other HLL's don't provide access to (all relevant) CPU registers.
I've constructed a number of OSes using C; it takes one or two pages of assembly code at least to implement proper process/task/thread switching. Everything else could be implemented in the HLL, although optimal interrupt handling may beg for some assembly code too.
One could argue that in-line assembly could be used (probably not in VB), but I would not really consider that as the compiler will insist on adding some code I don't want, and performance should not be wasted in task switching or interrupt servicing.
|
|
|
|
|
Luc Pattyn wrote: all you can make in a higher-level language is a virtual OS
Very true.
""Real programmers just throw a bunch of 1s and 0s at the computer to see what sticks" - Pete O'Hanlon
|
|
|
|
|
In VB, that would be called a 1s. 0s are made in C#. 
|
|
|
|
|
I'm googled all day long and can't seem to find a good example of this. I'm trying to write a VB.NET Windows Form Application to submit a XML request to a web service via a POST to a URL. And get the response. I've found plenty for VB6 but these don't work for Visual Studio. Can someone point me in the right direction? Thanks
|
|
|
|
|
Member 7743805 wrote: found plenty for VB6 but these don't work for Visual Studio
Have you tried to make the appropriate changes to VB.Net if possible?
"The clue train passed his station without stopping." - John Simmons / outlaw programmer
"Real programmers just throw a bunch of 1s and 0s at the computer to see what sticks" - Pete O'Hanlon
"Not only do you continue to babble nonsense, you can't even correctly remember the nonsense you babbled just minutes ago." - Rob Graham
|
|
|
|
|
I've cut & Pasted some of the relevant code and had to make a few changes to obfuscate the server and other proprietary business logic.
I'm very new to the webservice arena so this might not be the best way to implement it, but it is working for me.
The idea is that a salesman can make a webService request for a Product price quote.
sURL = "http://myServer/myApp/TQWebService.asmx?op=CreateQuoteRequest"
WRequest = WebRequest.Create(sURL)
WRequest.Method = "POST"
WRequest.Credentials = System.Net.CredentialCache.DefaultCredentials
SOAPdata = BuildSoapTQRequest(Salesman, ProductCode)
WRequest.ContentType = "application/soap+xml; charset=utf-8"
WRequest.ContentLength = SOAPdata.Length
postStream = WRequest.GetRequestStream()
postStream.Write(SOAPdata, 0, SOAPdata.Length)
postStream.Close()
Try
WResponse = WRequest.GetResponse()
responseStream = WResponse.GetResponseStream
Dim sr As New StreamReader(responseStream)
xDoc.LoadXml(sr.ReadToEnd)
Private Function BuildSoapTQRequest(ByVal ReqID As String, ByVal ProductCode as String) As Byte()
Dim sb As New StringBuilder
sb.Append("<soap12:Envelope xmlns:xsi=" + Chr(34) + "http://www.w3.org/2001/XMLSchema-instance" + Chr(34) + " xmlns:xsd=" + Chr(34) + "http://www.w3.org/2001/XMLSchema" + Chr(34) + " xmlns:soap12=" + Chr(34) + "http://www.w3.org/2003/05/soap-envelope" + Chr(34) + "> ")
sb.Append(System.Environment.NewLine)
sb.Append("<soap12:Body> " + System.Environment.NewLine)
sb.Append("<CreateQuoteRequest xmlns=" + Chr(34) + "http://myServer/" + Chr(34) + "> " + System.Environment.NewLine)
sb.Append("<myRequestorID>" + ReqID + "</myRequestorID>" + System.Environment.NewLine)
sb.Append("<myProductCode>" + ProductCode + "</myProductCode>" + System.Environment.NewLine)
sb.Append("</CreateQuoteRequest> " + System.Environment.NewLine)
sb.Append("</soap12:Body> " + System.Environment.NewLine)
sb.Append("</soap12:Envelope> " + System.Environment.NewLine)
Return Encoding.ASCII.GetBytes(sb.ToString)
End Function
|
|
|
|
|
I'm moving from VB6 to VS 2008. And need help with a data
structure problem.
In a Public code module in my VS 2008 project, I defined a structure:
Public structure db_connection
dim connection_string as string
End structure
Then defined a variable as the structure type:
Public db_con as db_connection
When I pass the db_con variable to a sub:
sub db_create_cs(dbc as db_connection)
dbc.connection_string = "some values"
end sub
I expected the db_con.connection_string value
to equal "some values" after the call.
Regards.
|
|
|
|
|
Check to see if in your declaration,
sub db_create_cs(dbc as db_connection)
the parameter is ByRef as opposed to ByVal.
Also, you might want to re-structure your program to define db_create_cs as a function rather than a sub. (my 2 cents)
Hope this helps.
|
|
|
|
|
ByRef cured the problem. I was going down fast. thanks
In the actual program it is a function. I slimmed down the
code for my question. Thanks for the comment though, and help.
|
|
|
|
|
If you don't specify "ByRef" or "ByVal", then the parameter is "ByVal" by default.
You want:
Sub db_create_cs(ByRef dbc As db_connection)
David Anton
Convert between VB, C#, C++, & Java
www.tangiblesoftwaresolutions.com
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
