G
G. Vaught
I am trying to help a former student with her exporting problem. I am very
good with Access and somewhat good with VBA, but not that familiar with
connecting to Excel via Access VBA. I did buy the book from O'Reilly books
Excel to Access, but it has not helped that much.
The problem I am having is this. The former student has a query that sends
data out to a report. Two of the total values from this report needs to be
sent out to Excel. However the two totals comes from the same field. What I
had to do is create a calculation to group the records into the two grand
totals.
Example: Boehm Meter Station is listed with all the other Meters. Boehm must
be totaled separate from the others. I created a totals grouping query and
create the calculate field called Grouping to place a value of 1 for all
meters other than Boehm and Boehm is set to a value of 2. Then I could get
the two totals. Here is an example of the finished query.
Grouping work_mcf
1 45678.488
2 2590.50
Now the problem. Using some VBA that my co-worker found and tried to use
using two forms to send the values out to Excel would only send one value. I
figured we need some way to loop through the query pulling only the work_mcf
values, I don't need the Grouping field sent to Excel, but it would be used
to send the correct value to the named cell .
I tried connecting to the query I made using ADODB.Connection and then a
WHILE WEND with a MoveFirst and MoveNext. However since I have to send each
value to two separate and separated cells in Excel, I had an issue on
sending the values to the named range because the two values uses the same
field name.
I think I need to somehow get the two values to be listed in one row next to
each other to sucessfully transfer the values to the Excel Form Template,
but I've been having a 'brain fart' on how to do this programmatically also.
The two totals need to transfer automatically to the Excel cells when the
user presses a command button from Access, of course.
I love it when users just want to push a button and this stuff magically
happens.
Hopefully, some has some ideas or other websites to visit. I have searched
somewhat on Google, but most examples are the basic transfer methods.
good with Access and somewhat good with VBA, but not that familiar with
connecting to Excel via Access VBA. I did buy the book from O'Reilly books
Excel to Access, but it has not helped that much.
The problem I am having is this. The former student has a query that sends
data out to a report. Two of the total values from this report needs to be
sent out to Excel. However the two totals comes from the same field. What I
had to do is create a calculation to group the records into the two grand
totals.
Example: Boehm Meter Station is listed with all the other Meters. Boehm must
be totaled separate from the others. I created a totals grouping query and
create the calculate field called Grouping to place a value of 1 for all
meters other than Boehm and Boehm is set to a value of 2. Then I could get
the two totals. Here is an example of the finished query.
Grouping work_mcf
1 45678.488
2 2590.50
Now the problem. Using some VBA that my co-worker found and tried to use
using two forms to send the values out to Excel would only send one value. I
figured we need some way to loop through the query pulling only the work_mcf
values, I don't need the Grouping field sent to Excel, but it would be used
to send the correct value to the named cell .
I tried connecting to the query I made using ADODB.Connection and then a
WHILE WEND with a MoveFirst and MoveNext. However since I have to send each
value to two separate and separated cells in Excel, I had an issue on
sending the values to the named range because the two values uses the same
field name.
I think I need to somehow get the two values to be listed in one row next to
each other to sucessfully transfer the values to the Excel Form Template,
but I've been having a 'brain fart' on how to do this programmatically also.
The two totals need to transfer automatically to the Excel cells when the
user presses a command button from Access, of course.
I love it when users just want to push a button and this stuff magically
happens.
Hopefully, some has some ideas or other websites to visit. I have searched
somewhat on Google, but most examples are the basic transfer methods.