Excel VBA to Access VBA

ivandgreat

Registered User.
Local time
Today, 15:45
Joined
Sep 18, 2012
Messages
30
Hello,

I have the excel vba in my data and i wanted to convert the vba code in access.

below is my code that i'm working on from excel vba,

idA = Application.Index(wsPT.Range("B2:B" & wsPT.Cells(Rows.Count, "B").End(xlUp).Row), 0, 1)
idB = Application.Index(wsPT.Range("C2:C" & wsPT.Cells(Rows.Count, "C").End(xlUp).Row), 0, 1)


For k = LBound(idA) To UBound(idA)
For t = LBound(idB) To UBound(idB)
If idA(k, 1) = idB(t, 1) Then
.
.

End If
Next
Loop
any one could help me on this.

br,
 
Are you saying that you want to load an array in access with the data from an excel sheet to make it faster?
 
Are you saying that you want to load an array in access with the data from an excel sheet to make it faster?


Actually i wanted to know the size of the array per table field in the access table.
 
Why do you need the size each field in an access table? Are you doing something really advanced or are you just simply wanting to get access data on a sheet or in memory so you can work with it in excel?
 
Why do you need the size each field in an access table? Are you doing something really advanced or are you just simply wanting to get access data on a sheet or in memory so you can work with it in excel?

I just thought if it is the same as excel. I'm just trying to edit some codes in excel to be on access vba, but unable to do it. That's why maybe it wont work.

could you help me how should i work with the fields in the access?
 
Yes we can help you. I was hopeing to find something already created online that would be a good reference and will continue to search for a bit. And I'll post a short sample of something here that's cleaned up to try and get you started. Hope this helps I'll be posting in a little while.
 
In the Excel spread sheet you have 2 columns of data which you are comparing, and maybe more, you presumably have code to handle if they do not contain the same number of cells of data.

What do you have in ACCESS and what do you want to do?

Brian
 
Yes we can help you. I was hopeing to find something already created online that would be a good reference and will continue to search for a bit. And I'll post a short sample of something here that's cleaned up to try and get you started. Hope this helps I'll be posting in a little while.

Nice to hear that. thanks a lot.
 
In the Excel spread sheet you have 2 columns of data which you are comparing, and maybe more, you presumably have code to handle if they do not contain the same number of cells of data.

What do you have in ACCESS and what do you want to do?

Brian

I attached my access data.

I have 3 tables in it, i only want to compute an output based on table "Input", the output is in the table "Output".

the logic is this,
- in table "Input", i have two fields "NE" and "FE".
- there is no duplicate ID @ field "NE"
- @ field "NE" first lookup the records in field "FE", and record it, after search it will lookup the adjacent "NE" again and loop until nothing found. example. In item 1, 2100 will found one item in field "FE" which is in item 4, the adjacent "NE" is ID 2002, this ID will search the value @ field "FE" so it has two with "NE" 2001 and 2200, these two IDs will do the same until found nothing. All found ID will be recorded on different table which is called table "Ouput".
- The table "Output" is only the result of the logic mentioned above.
- Other field @ table "Output" is part of the loop that were computed like field "To2G Capacity" and "To3G Capacity" is only adding all field values @ "NE 2G" and "NE 3G" (@ table "Input") related to the IDs recorded @ table "Output" field "DepID_Current".

Hope you could help me on this.

br,
ivan
 

Attachments

You mentioned this:

"In item 1, 2100 will found one item in field "FE" which is in item 4"

But when looking at the database the only field that has item 4 in it is "NE 2G". Was the example wrong that you typed or is the data in the database sample incorrect?
 
You mentioned this:

"In item 1, 2100 will found one item in field "FE" which is in item 4"

But when looking at the database the only field that has item 4 in it is "NE 2G". Was the example wrong that you typed or is the data in the database sample incorrect?

It it correct.

The flow should be like this, first search the first item in the input table which is ID 2100 will be search @ FE field, (found @ item 4) the adjacent NE is 2002, then this 2002 will be used to search @ FE field (found @ item 3 and 5), this 2 item will be search also @ FE field, @ item 3 with adj NE of 2001, will be used to search @ FE field (found @ item 2) with NE field of 2000.. and so on to the other IDs. It will only stop when found nothing. all item found will be recorded @ output table with field name of "DepID_Current", with record of 5 IDs @ field "No of IDs".

Hope you could help me to code it.

br,
ivan
 
Before you are able to get any kind of integratino to work you need to make sure you have the MS Excel libraries referenced in the VBA environment. With older versions of MS Access you would go into the VBA editor and select TOOLS ==> REFERENCES.
 
What error do you get if you copy/paste the code and try to compile it? What version of Office are you using?
 
I'm not familliar with the Application.Index method, but suspect you may need to add a prefix to this method to make sure MS Access can find the correct MS Excel object. No MS Excel libraries will ever be found without first making a reference to them. MS Office 2000 uses the "Microsoft Excel 9.0 Object Library" which symbolically points to the EXCEL9.OLB file.

The rest of the MS Excel VBA appears to be visually sound and valid for use in MS Access VBA (without any VBA dialictical edits).
 

Users who are viewing this thread

Back
Top Bottom