Reducing Repetition of Code

[rajltd]

The following code: It looks up in a particular access table and if the subcategory has any particular value then it will transfer the particular table(corresponding to the subcategory_ID) to excel worksheet in a particular workbook. After that it deletes the first row and again looks for another number. I have to write this code as many times as there are subcategory IDs. Is there any way in which I can look into the table and do the necessary actions, without deleting the rows again and again. As I keep on writing the same code hundred times as I have 100 types of subcategory.


Function TableEmail2zzz()

Dim varX As Variant
Dim varY As Variant
Dim varZ As Variant


varX = DLookup("[SUBCATEGORY_ID]", "Show SubCats per manufacturer for module&quot
varY = DCount("[Model Code]", "[ABC]&quot

If varX = 1 Then
If varY > 0 Then
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "ABC", "My Document\Verification" & "\XYZ.xls", , "ABC"
End If
Call Delete
End If

End Function

Sub Delete()

DoCmd.OpenTable "Show SubCats per manufacturer for module", acNormal
DoCmd.RunCommand acCmdDeleteRecord
DoCmd.SetWarnings False
DoCmd.Close acQuery, "Show SubCats per manufacturer for module", acSaveYes

End Sub


Any help would be appreciated.

Thanks

Raj

 

[SkipVought]

Hi rajltd,

Let me ask you a question.

Could this be done FROM EXCEL with a simple query?

Seems like you're getting data out of your table row by row.

It's alot easier to PULL data than PUSH.

Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com

 

[rajltd]

Hi Skip,

Thanks for your reply. This was in regards to my earlier question as to whether there is any conflict when running macros in access and excel. I think for my software, I will be able to extract data in access only. And you are correct, that it gets data out of table row by row,

if var x = 1 then,
and then if var x = 2 etc.

I know its pain and duplication of data. Because var x = 1, has a specific Model_Name which is var y. But then I have to just look into excel part. And other person looks into the access part and exports data to me. He has more access to software database and he just exports MS access stuff to me to work on MS excel stuff.

Sorry about all the trouble. But i have to work in this constrain.

Thanks

Raj

 

[SkipVought]

rajltd,

If you can do a query in Access and then send that data to Excel...

THEN

you ought to be able to do EXACTLY the same thing in Excel by querying Access from Excel MUCH SIMPLER and some times WITH NO CODING!

Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com

 

[rajltd]

There are many other access tables developed around the software application. And it wont be possible for me to change this particular table into excel worksheet.

I dont know if the access code can be reduced or not, but thats the reason why i posted the query to the forum.

 

[xlbo]

you don't need to change the table - just query it from excel rather than access - that's what Skip is suggesting (right Skip ??)

Rgds, Geoff
[blue]Si hoc signum legere potes, operis boni in rebus Latinus alacribus et fructuosis potiri potes![/blue]
Want the [red]best[/red] answers to your questions ? faq222-2244

 

[SkipVought]

Using MS Query, for instance, in Excel, you can join a multitude of tables from your database, with various criteria and calculated columns if necessary, just as you would a query in Access, to return a resultset that you need!

Try it!

Data/Get external data.../New database query.

then, since your database is Access, in the Database tab select MS Access database*

then locate your database

then select your tables/fields

then View data or edit query in Microsoft Query - FINISH

and VOLA! you get a QBE grid -- HAVE AT IT!

Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com