|
I have an Access form that I created an Unbound OLE object frame on it. The OLE object was configured (by the Wizard) to accept Excel and I want to use it as a portal to import Excel worksheets into my Access DB (drag/drop filename and voila, my OLE object has an Excel sheet!)
My question is, once that the Excel worksheet is in the OLE object, how can use VBA code to go through the Excel object and extract field values and populate a given Access table?
Here are some defintions that I started to try but did not work:
<br />
Private Function ReadExcelObject()<br />
Dim bRetStatus As Boolean<br />
<br />
Dim oXLapp As Excel.Application<br />
Dim oXLwsh As Excel.Worksheet<br />
Dim oXLrng As Excel.Range<br />
bRetStatus = True<br />
<br />
'OLEUnboundExcel is an Unbounded Object Frame<br />
' Set oXLapp = OLEUnboundExcel.Application 'this produces a type-mismatch<br />
' Set oXLwsh = oXLapp.Worksheets(1)<br />
'OLEUnboundExcel<br />
'Set oXLapp = GetObject(, OLEUnboundExcel)<br />
' Set oXLapp = OLEUnboundExcel.Object<br />
' Set oXLwsh = oXLapp.Worksheets(1)<br />
<br />
'what do I do to make this work???<br />
<br />
ReadExcelObject = bRetStatus<br />
<br />
End Function
Thanks in advance!
Johnny
|
|
|
|
|
Oh, my configuration is as follows:
Access 2000
Excel 2000
VB 6.0, ver 8435
Win2000
Johnny
|
|
|
|
|
I'm trying to retrieve a value in the registry that is a Qword (64bit value). Anybody have any idea how I can accomplish this with .NET v 1.0/1.1? I've tried retrieveing it as binary and as a string with no prevail. I have also tried using RegOpenKey/RegQueryValue API with no luck (even retrieving normal registry values).
So basically anyone know how to accomplist this natively in .NET? I have tried several different declarations for the API and no luck, am I doing something wrong (.NET permissions possibly)?
I have a .reg file to add a qword to the registry if needed.
Thanks,
-Jerry
|
|
|
|
|
Can't be done with the .NET BCL. You have to use the Win32 API functions to read/write a REG_QWORD value. I don't have any example code to show you because, frankly, hardly anyone ever uses the obscure little value type. I'll see if I can whip something up when I get home.
RageInTheMachine9532
"...a pungent, ghastly, stinky piece of cheese!" -- The Roaming Gnome
|
|
|
|
|
Const KEY_QUERY_VALUE = &H1&
Const KEY_SET_VALUE = &H2&
Const KEY_CREATE_SUB_KEY = &H4&
Const KEY_ENUMERATE_SUB_KEYS = &H8&
Const KEY_NOTIFY = &H10&
Const KEY_CREATE_LINK = &H20&
Const READ_CONTROL = &H20000
Const WRITE_DAC = &H40000
Const WRITE_OWNER = &H80000
Const SYNCHRONIZE = &H100000
Const STANDARD_RIGHTS_REQUIRED = &HF0000
Const STANDARD_RIGHTS_READ = READ_CONTROL
Const STANDARD_RIGHTS_WRITE = READ_CONTROL
Const STANDARD_RIGHTS_EXECUTE = READ_CONTROL
Const KEY_READ = STANDARD_RIGHTS_READ Or KEY_QUERY_VALUE Or KEY_ENUMERATE_SUB_KEYS Or KEY_NOTIFY
Const KEY_WRITE = STANDARD_RIGHTS_WRITE Or KEY_SET_VALUE Or KEY_CREATE_SUB_KEY
Const KEY_EXECUTE = KEY_READ
Const REG_NONE = 0
Const REG_SZ = 1
Const REG_EXPAND_SZ = 2
Const REG_BINARY = 3
Const REG_DWORD = 4
Const REG_DWORD_LITTLE_ENDIAN = 4
Const REG_DWORD_BIG_ENDIAN = 5
Const REG_LINK = 6
Const REG_MULTI_SZ = 7
Const REG_RESOURCE_LIST = 8
Const REG_FULL_RESOURCE_DESCRIPTOR = 9
Const REG_RESOURCE_REQUIREMENTS_LIST = 10
Const REG_QWORD = 11
Const REG_QWORD_LITTLE_ENDIAN = 11
Public Enum RegKeys As Integer
HKEY_CLASSES_ROOT = &H80000000
HKEY_CURRENT_USER = &H80000001
HKEY_LOCAL_MACHINE = &H80000002
HKEY_USERS = &H80000003
HKEY_PERFORMANCE_DATA = &H80000004
HKEY_CURRENT_CONFIG = &H80000005
HKEY_DYN_DATA = &H80000006
End Enum
Declare Auto Function RegOpenKeyEx Lib "advapi32" ( _
ByVal hKey As Integer, _
ByVal subKey As String, _
ByVal options As Integer, _
ByVal sam As Integer, _
ByRef phkResult As Integer) As Integer
Declare Auto Function RegSetValueExQWORD Lib "advapi32" Alias "RegSetValueEx" ( _
ByVal hKey As Integer, _
ByVal lpValueName As String, _
ByVal Reserved As Integer, _
ByVal dwType As Integer, _
ByRef lpData As System.UInt64, _
ByVal cbData As Integer) As Integer
Declare Auto Function RegQueryValueExQWORD Lib "advapi32" Alias "RegQueryValueEx" ( _
ByVal hKey As Integer, _
ByVal lpValueName As String, _
ByVal dwReserved As Integer, _
ByRef dwType As Integer, _
ByRef lpData As System.UInt64, _
ByRef cbData As Integer) As Integer
Declare Auto Function RegCloseKey Lib "advapi32" (ByVal hKey As Integer) As Integer
'
' This code writes a REG_QWORD value:
'
Dim hKey As Integer
Dim rc As Integer
rc = RegOpenKeyEx(RegKeys.HKEY_LOCAL_MACHINE, "SOFTWARE", 0, KEY_READ Or KEY_WRITE, hKey)
Debug.Assert(rc = 0, "RegOpenKey Failed with error " & rc.ToString())
rc = RegSetValueExQWORD(hKey, "TestValue", 0, REG_QWORD, UInt64.Parse("5842365264844985"), 8)
Debug.Assert(rc = 0, "RegSetValueEx Failed with error " & rc.ToString())
rc = RegCloseKey(hKey)
'
' This code reads a REG_QWORD value:
'
Dim hKey As Integer
Dim rc As Integer
Dim data As UInt64
Dim dataType As Integer
Dim dataSize As Integer = 8
rc = RegOpenKeyEx(RegKeys.HKEY_LOCAL_MACHINE, "SOFTWARE", 0, KEY_READ Or KEY_WRITE, hKey)
Debug.Assert(rc = 0, "RegOpenKey Failed with error " & rc.ToString())
rc = RegQueryValueExQWORD(hKey, "TestValue", 0, dataType, data, dataSize)
Debug.Assert(rc = 0, "RegGetValue Failed with error " & rc.ToString())
rc = RegCloseKey(hKey)
MsgBox("The value (" & dataType.ToString() & ", size: " & dataSize.ToString() & ") returned is: " & data.ToString(), MsgBoxStyle.Information, "QWORD Return Value")
RageInTheMachine9532
"...a pungent, ghastly, stinky piece of cheese!" -- The Roaming Gnome
|
|
|
|
|
Thanks a lot Dave!
Yeah I got it to work with the API's. However I declared the data as a String in RegQueryValueEx and man did it take some jumping through hoops in order to convert it to a long. I tried the declaration with a long (int64) but I forgot the value is unsigned. Doh! Needless to say your code is a bit more efficient. 
For some reason the declaration I had wasn't working. After letting it sit for a bit and going back to it (and a reboot) the code worked fine. Since I'm relatively new to .NET, I thought it might be some type of framework security feature. It was really weird cause the return value was always a really high non zero for the RegQueryValueEx, which really stumped me.
Guess it was just one of those days of banging your head against the wall...
(plus i never got an e-mail that you responded.. oh well)
Thanks again,
-Jerry
|
|
|
|
|
For testing I have added in form1
Public WithEvents myEvent As New Array1(New Panel) and added something to do when event is triggerd
Public Sub myEvent_Clicked(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles myEvent.Clicked<br />
MessageBox.Show("Button pressed")<br />
End Sub
To test the event I have added a button who's fireing the event in Array1
Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click<br />
myEvent.OnOff_ClickHandler(sender, e)<br />
End Sub
In Array1 there is the routine
Public Sub OnOff_ClickHandler(ByVal sender As Object, ByVal e As System.EventArgs)<br />
MessageBox.Show("Event routine")<br />
RaiseEvent Clicked(sender, e)<br />
End Sub
When I click button1 the event RaiseEvent Clicked(sender, e) is fired but when I click a button from the array panel with AddHandler aButton.Click, AddressOf OnOff_ClickHandler then nothing happed. I see the first message with "Event routine" appear but the second not
What's the different between a fixed button and a button from an array control?
In my opinion nothing but who am I
|
|
|
|
|
hy everyone
I do want to realice a kind of permutation in a VBA programm
Lets say I do have the three integer pairs "100 - 120", "200 - 220" and "300 - 320".
Now I would like to let my programm select every possible permution of them, meaning the lowerone of the first, the lower one of the second and the lowerone of the third.
Let me describe with a set of two pairs.
Lets say I do have the pairs "1 - 2" and "3 - 4" so my proggy should allow to access every permution, either for output or for furter use.
Well I will have to get "1 3" "1 4" "2 3" and "2 4".
This should work for every amount of pairs available. Lets say 16 or 20 pairs with a lower and higher number.
I thought about an inner and outer loop but this way I was not able to get all the permutations.
Does anyone of you know how to realice that.
Thanks!
Stephan.
|
|
|
|
|
Well I realized I do not need every permutation, I do only need any combination of the min max values
lets have alook at the followin example
lets say I do have the intervals "A - B", "C - D" and "E - F"
now I do want to get any min max combination (because I do want to use this for an addfunction there is no different if lets say A is in first, second or third place. Meaning e.g. ACE = CEA = EAC etc.
Therefor I do only need ACE, ACF, ADE, ADF, BCE, BCF, BDE and BDF.
Every other combination is not needed because plus is commutativ (see above)
If you do have a look at this with it's index then you might realice there is every bitcombination in it. Because lets say the minimum is 0 and the maximum is 1 in indexing then you do get 000 up to 111.
And that's what I do want to realize for "length" 2 to 10 (or even higher).
Well I could use 2, 3 ... loops in loops but well in the beginning I do not know how many are needed. Well maybe I should try to think about a bitmap using an array doing something like
if most right position in array is 0 then set it to 1, if it is already 1 then set it to 0 and increase the next position. if this is already 1 then increase the next and set this to 0 etc. 
meaning 000 +1 => 001 001 + 1=> 010 .... 011 +1 => 100 ...
Does anyone of you know a better solution for this? If so please let me know!
Because in my example I have to check the bitarray and this task gets bigger for every combination, which is not a good idea concerning performance. 
Thanks!
Stephan.
|
|
|
|
|
in my client server project when I call Frm.show the form stops responding, the code is very basic, some data is sent from the server witch tells the client what form to load then In a sub I call
form1.hide
form2.show
|
|
|
|
|
Hi There
You have to create instance of the form and then call the methods. If the form is Form1 it should be some what like this
F1 as Form1
F1.Show
F1.Hide
|
|
|
|
|
Hy everyone!
I want to do some stringparsing, but well I seem to having done to much programming in C# and C/C++. So I guess I start mixing things.
So what I want to do is the following:
I do have an input in an Excel Cell which looks like "xxx-yyyy" or maybe even blanks between them like "xxx - yyy" etc. xxx and yyy represent integers. So now I do want to split this string to get xxx and yyy in seperate integer variables. Well I guess it is quit simple, but at the moment I seem to be mixing things. I know how to do this in C# etc (parsing the array of char) but well...
the info I collected from the sheet is stored in a variant variable, because the user should be allowed to either enter "xxx" or "xxx-yyy".
Thanks for your help.
Stephan.
|
|
|
|
|
I just found out myself. I have to use the Split(string) operation but is there any way to split the string without having blanks to seperate it?
If anyone of you knows how to split "123-234" into 123 and 234 (meaning the two integers) then please let me know. At the moment I am able to split "123 - 234" into "123" "-" and "234" but well, if there are no blanks inbetween my programm will crash. And I really would like to avoid this
Thanks.
Stephan.
|
|
|
|
|
Why use split? Just use SubString:
Dim a As Integer
Dim b As Integer
Dim s As String = "123 - 456"
Dim i As Integer = s.IndexOf("-")
If i < 0 Then
'must be in xxx form
a = Integer.Parse(s)
Else
'in xxx-yyyy form, so parse
a = Integer.Parse(s.Substring(0, i))
b = Integer.Parse(s.Substring(i + 1, s.Length - i - 1))
End If
This will work if spaces or no spaces. If you need the strings rather than the integers, just use stringa = s.Substring(0, i).Trim. The trim will remove leading or trailing spaces.
|
|
|
|
|
Scott Serl wrote:
a = Integer.Parse(s.Substring(0, i))
Well, seams as if VBA for Excel does not support "Integer.Parse"
Or does it? At the moment I am on Office 2000 but might soon be updating to Office 2003.
I had a look and the help says there is only a normal parse operation. The examples says parsing a telephonenumber and seperating it into two Cells (similar to my example). Maybe I could use this one ...
Or was your example supposed for .NET? Because actually I do not have this installed here and at the moment I do not have access to it. So I have to get along with what Office is offering 'til I will be able to use .NET
Stephan.
|
|
|
|
|
Sorry,
In Excel, it would be something like:
value = "123 - 456"
Pos = Instr(value,"-")
If Pos > 0 Then 'I think position starts at 1 in Excel
a = Val(Left(value, pos - 1))
b = Val(Right(value, Len(value) - Pos))
Else
a = val(value)
End If
I can't check at this computer because I don't have anything with old vb on it here.
|
|
|
|
|
now that i ponder on it a bit, when i use the split function in c# i simply do the following -
myString.split('-')[0] //gets xxx
myString.split('-')[1] //gets yyy
I wonder if you couldn't do the same for vb.net i.e.
myString.split('-')(0) //gets xxx
myString.split('-')(1) //gets yyy
|
|
|
|
|
You can but VB.net likes you to specify that the splitter character is of type char. Also the single quote is the comment character in VB.Net so you have to use double quotes.
myString.split("-"c)(0) 'gets xxx
myString.split("-"c)(1) 'gets yyy
If you have knowledge, let others light their candles at it.
Margaret Fuller (1810 - 1850)
|
|
|
|
|
Sorry for writing this Question to this part, but i write it in SQL part, i got no answer
I have a table named "DebitsAndCredits" which has fallowing columns
AccountNumber char(12)
DebitCredit bit
Amount money
I have the same table in two databases which are MB0001 and MB0002
I would like to join them in a one result set.I write
SELECT DISTINCT (AccountNumber ) ,
(SELECT SUM(Amount ) FROM DebitsAndCredits m
WHERE m.DebitCredit =0 AND m.AccountNumber =f.AccountNumber ) as Credit,,
(SELECT SUM(Amount ) FROM DebitsAndCredits m
WHERE m.DebitCredit =1 AND m.AccountNumber =f.AccountNumber ) as Debit
FROM DebitsAndCredits Order BY AccountNumber
UNION
SELECT DISTINCT (AccountNumber ) ,
(SELECT SUM(Amount ) FROM DebitsAndCredits m
WHERE m.DebitCredit =0 AND m.AccountNumber =f.AccountNumber ) as Credit,
(SELECT SUM(Amount ) FROM DebitsAndCredits m
WHERE m.DebitCredit =1 AND m.AccountNumber =f.AccountNumber ) as Debit
FROM DebitsAndCredits Order BY AccountNumber
i dont know why it is not working in my way. I expect a table has three columns (AccountNumber,Credit,Debit ) as a result.
What is Wrong?What do u offer for me?
|
|
|
|
|
you must specify the database names in the query eg.
Select ..... from MB0001..DebitsAndCredits m1 ......
Union
Select ..... from MB0002..DebitsAndCredits m2 ......
If you have knowledge, let others light their candles at it.
Margaret Fuller (1810 - 1850)
|
|
|
|
|
I have already done that. But i realized i forgot writing that when i asked
|
|
|
|
|
I have DOTNET project that connect to excel through RTD. After some time say 2 week the excel lose the connection to RTD component
I will be thankful if any body guide me how to solve this problem or how to fiend the reason of it
And thank of all any way
note:
the excel use interop dll to connect to the assembly dll
Aswanee
|
|
|
|
|
Well, there's nothing we can tell you since you haven't provided any useful information about the component RTD is connecting to. Is this a component that you wrote or a third party? What does this component do? We would also need to know about any error messages that are thrown.
RageInTheMachine9532
"...a pungent, ghastly, stinky piece of cheese!" -- The Roaming Gnome
|
|
|
|
|
I have an application written in VB6 that allow others to write plugins in the form of an ActiveX control. Now I want to allow dotnet usercontrols as plugins as well.
There are examples on the net of how to expose a windows forms usercontrol to COM as an ActiveX control. Although not actually supported by the framework, these seem to work if the usercontrol is loaded in a VB6 form. However, when the dotnet usercontrol is loaded inside a VB6 usercontrol, arrow keys stop working. Apparently they are interpreted as tabs (even if focus is in a textbox or listbox).
Now my question is, first, is there a way to solve this? Any pointers would be appreciated.
Second, since this is not really supported and I already have seen problems, I'm getting worried about more problems ahead. Is there anyone who has used dotnet usercontrols in VB6? Does it work well, or are there other problems that will appear later?
|
|
|
|
|
HI,
PLease let me know the provider or the connectionstring i should use to
read a .dbf file.
The oledb provider i am using works fine if there is no memo field in the
.dbf( i.e .fpt file)
But fails if .fpt is present
|
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
