Lnking vs moving

  • Thread starter Thread starter Connie Reed
  • Start date Start date
C

Connie Reed

Hi all,
The question I am asking is, when one needs to move data,
field to cell by field to cell into a large spreadsheet
from Access 2000 to Excel 2000, is it better to link the
data or create a module like this one:
objxl_Sheet.range("D9") = DLookup
("[ExpiredAmount]", "tblRT21", "[GameNumber]='02'")
If objxl_Sheet.range("D9") = "" Then
objxl_Sheet.range("D9") = "0"
End If
objxl_Sheet.range("D10") = DLookup
("[ExpiredAmount]", "tblRT21", "[GameNumber]='03'")
objxl_Sheet.range("D11") = DLookup
("[ExpiredAmount]", "tblRT21", "[GameNumber]='04'")
objxl_Sheet.range("D12") = DLookup
("[ExpiredAmount]", "tblRT21", "[GameNumber]='05'")
objxl_Sheet.range("D13") = DLookup
("[ExpiredAmount]", "tblRT21", "[GameNumber]='06'")
objxl_Sheet.range("D14") = DLookup
("[ExpiredAmount]", "tblRT21", "[GameNumber]='07'")
objxl_Sheet.range("D15") = DLookup
("[ExpiredAmount]", "tblRT21", "[GameNumber]='08'")
objxl_Sheet.range("D16") = DLookup
("[ExpiredTodayAmount]", "tblRT01", "[GameNumber]='09'")
If I create the above I would need to do the if statement
for each line item, as I cannot leave a field blank, it
must have a zer in it in the spreadsheet. I cannot create
the specs to be currency, as I have 21 tables (non of them
consistent with the other) and each table would need to
have a spec. That's way too much work for a simple databse
to import data from a mainframe, and export it into Excel
spreadsheet. How can i make the above code more efficient?
Tha'ts just a sampling of what I would need to create to
get this spreadsheet done.
Thanks so much for your help. I confess I have never
worked in VB in Excel so I don't know the power of Excel.
Connie
 
Hi Connie,

Check out Excel's Range.CopyFromRecordset method. If you create an
Access query that returns the data you want, you can open a recordset
(DAO or ADO) on the query and use CopyFromRecordSet to plonk it into
your worksheet in one go.

See also:
Sample Excel automation
http://www.mvps.org/access/modules/mdl0006.htm

Q123859 ACC: Sample OLE Automation for MS Word and MS Excel
http://support.microsoft.com/?id=123859

ACC2000: Using Automation to Create and Manipulate an Excel Workbook
(Q210148) http://support.microsoft.com/?id=210148

ACC: Using Automation to Create and Manipulate an Excel Workbook
(Q142476)
http://support.microsoft.com/?id=142476

Using Excel VBA it's possible to do just about anything that's possible
via the user interface, and quite a few things that aren't.

Hi all,
The question I am asking is, when one needs to move data,
field to cell by field to cell into a large spreadsheet
from Access 2000 to Excel 2000, is it better to link the
data or create a module like this one:
objxl_Sheet.range("D9") = DLookup
("[ExpiredAmount]", "tblRT21", "[GameNumber]='02'")
If objxl_Sheet.range("D9") = "" Then
objxl_Sheet.range("D9") = "0"
End If
objxl_Sheet.range("D10") = DLookup
("[ExpiredAmount]", "tblRT21", "[GameNumber]='03'")
objxl_Sheet.range("D11") = DLookup
("[ExpiredAmount]", "tblRT21", "[GameNumber]='04'")
objxl_Sheet.range("D12") = DLookup
("[ExpiredAmount]", "tblRT21", "[GameNumber]='05'")
objxl_Sheet.range("D13") = DLookup
("[ExpiredAmount]", "tblRT21", "[GameNumber]='06'")
objxl_Sheet.range("D14") = DLookup
("[ExpiredAmount]", "tblRT21", "[GameNumber]='07'")
objxl_Sheet.range("D15") = DLookup
("[ExpiredAmount]", "tblRT21", "[GameNumber]='08'")
objxl_Sheet.range("D16") = DLookup
("[ExpiredTodayAmount]", "tblRT01", "[GameNumber]='09'")
If I create the above I would need to do the if statement
for each line item, as I cannot leave a field blank, it
must have a zer in it in the spreadsheet. I cannot create
the specs to be currency, as I have 21 tables (non of them
consistent with the other) and each table would need to
have a spec. That's way too much work for a simple databse
to import data from a mainframe, and export it into Excel
spreadsheet. How can i make the above code more efficient?
Tha'ts just a sampling of what I would need to create to
get this spreadsheet done.
Thanks so much for your help. I confess I have never
worked in VB in Excel so I don't know the power of Excel.
Connie
Click to expand...
 
Back
Top