Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Sum Exported data automatically from Access database
Hi
I created an Access database which exports data to Excel. I would like assistance in creating the required code which would allow me to sum some selected columns automatically after the data is exported (kindly note that I do not want any additional columns created to capture the total figure). Note that the rows from the database may vary hence the excel rows will also vary. Code to determine the row count would need to be included. Thanks in advance |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Sum Exported data automatically from Access database
The code below will add a fomula into the worksheet with the total.
Set Sht = activeworksheet LastRow = Sht.Range("A" & Rows.Count).end(Xlup) NewRow = LastRow + 1 Sht.Range("A" & NewRow).formula = _ "=SUM(A1:A" & LastRow & ")" "microsoft" wrote: Hi I created an Access database which exports data to Excel. I would like assistance in creating the required code which would allow me to sum some selected columns automatically after the data is exported (kindly note that I do not want any additional columns created to capture the total figure). Note that the rows from the database may vary hence the excel rows will also vary. Code to determine the row count would need to be included. Thanks in advance |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Sum Exported data automatically from Access database
Hi
Thanks for your response. As I am a novice at this, kindly confirm where this code would have to be placed. Should it be placed behind the button which intiates the export from access to excel Thanks again "joel" wrote: The code below will add a fomula into the worksheet with the total. Set Sht = activeworksheet LastRow = Sht.Range("A" & Rows.Count).end(Xlup) NewRow = LastRow + 1 Sht.Range("A" & NewRow).formula = _ "=SUM(A1:A" & LastRow & ")" "microsoft" wrote: Hi I created an Access database which exports data to Excel. I would like assistance in creating the required code which would allow me to sum some selected columns automatically after the data is exported (kindly note that I do not want any additional columns created to capture the total figure). Note that the rows from the database may vary hence the excel rows will also vary. Code to determine the row count would need to be included. Thanks in advance |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Sum Exported data automatically from Access database
My code should be placed at the end of the macro you already have behind the
button which moves the data to excel. There should be an excel object in the access macro where you would add the new code. If you post the access macro I can add the changes. "microsoft" wrote: Hi Thanks for your response. As I am a novice at this, kindly confirm where this code would have to be placed. Should it be placed behind the button which intiates the export from access to excel Thanks again "joel" wrote: The code below will add a fomula into the worksheet with the total. Set Sht = activeworksheet LastRow = Sht.Range("A" & Rows.Count).end(Xlup) NewRow = LastRow + 1 Sht.Range("A" & NewRow).formula = _ "=SUM(A1:A" & LastRow & ")" "microsoft" wrote: Hi I created an Access database which exports data to Excel. I would like assistance in creating the required code which would allow me to sum some selected columns automatically after the data is exported (kindly note that I do not want any additional columns created to capture the total figure). Note that the rows from the database may vary hence the excel rows will also vary. Code to determine the row count would need to be included. Thanks in advance |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Sum Exported data automatically from Access database
Hi Joel
Thanks for your assistance thus far. Kindly see the code below. This is being done in Access 2000. Private Sub Command0_Click() DoCmd.OutputTo acOutputQuery, "Excel Exportb", acFormatXLS, "c:\file.xls", True Set Sht = activeworksheet LastRow = Sht.Range("A" & Rows.Count).end(Xlup) NewRow = LastRow + 1 Sht.Range("A" & NewRow).formula = _ "=SUM(A1:A" & LastRow & ")" End Sub I await your response. "joel" wrote: My code should be placed at the end of the macro you already have behind the button which moves the data to excel. There should be an excel object in the access macro where you would add the new code. If you post the access macro I can add the changes. "microsoft" wrote: Hi Thanks for your response. As I am a novice at this, kindly confirm where this code would have to be placed. Should it be placed behind the button which intiates the export from access to excel Thanks again "joel" wrote: The code below will add a fomula into the worksheet with the total. Set Sht = activeworksheet LastRow = Sht.Range("A" & Rows.Count).end(Xlup) NewRow = LastRow + 1 Sht.Range("A" & NewRow).formula = _ "=SUM(A1:A" & LastRow & ")" "microsoft" wrote: Hi I created an Access database which exports data to Excel. I would like assistance in creating the required code which would allow me to sum some selected columns automatically after the data is exported (kindly note that I do not want any additional columns created to capture the total figure). Note that the rows from the database may vary hence the excel rows will also vary. Code to determine the row count would need to be included. Thanks in advance |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Sum Exported data automatically from Access database
See if this works. I having problems witth my PC and I can't test
evverything. I,m opening the workbook and adding SUM to column g. Private Sub Command0_Click() FName = "c:\temp\file.xls" MyQuery = "Excel Exportb" DoCmd.OutputTo _ ObjectType:=acOutputQuery, _ ObjectName:=MyQuery, _ OutputFormat:=acFormatXLS, _ OutputFile:=FName, _ AutoStart:=True Set obj = CreateObject("excel.application") Set bk = obj.Workbooks.Open(FileName:=FName) With bk.Sheets(MyQuery) LastRow = .Range("A" & 65536).End(xlUp).Row NewRow = LastRow + 1 .Range("G" & NewRow).Formula = _ "=SUM(G2:G" & LastRow & ")" End With bk.Close savechanges:=True Set obj = Nothing End Sub "microsoft" wrote: Hi Joel Thanks for your assistance thus far. Kindly see the code below. This is being done in Access 2000. Private Sub Command0_Click() DoCmd.OutputTo acOutputQuery, "Excel Exportb", acFormatXLS, "c:\file.xls", True Set Sht = activeworksheet LastRow = Sht.Range("A" & Rows.Count).end(Xlup) NewRow = LastRow + 1 Sht.Range("A" & NewRow).formula = _ "=SUM(A1:A" & LastRow & ")" End Sub I await your response. "joel" wrote: My code should be placed at the end of the macro you already have behind the button which moves the data to excel. There should be an excel object in the access macro where you would add the new code. If you post the access macro I can add the changes. "microsoft" wrote: Hi Thanks for your response. As I am a novice at this, kindly confirm where this code would have to be placed. Should it be placed behind the button which intiates the export from access to excel Thanks again "joel" wrote: The code below will add a fomula into the worksheet with the total. Set Sht = activeworksheet LastRow = Sht.Range("A" & Rows.Count).end(Xlup) NewRow = LastRow + 1 Sht.Range("A" & NewRow).formula = _ "=SUM(A1:A" & LastRow & ")" "microsoft" wrote: Hi I created an Access database which exports data to Excel. I would like assistance in creating the required code which would allow me to sum some selected columns automatically after the data is exported (kindly note that I do not want any additional columns created to capture the total figure). Note that the rows from the database may vary hence the excel rows will also vary. Code to determine the row count would need to be included. Thanks in advance |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
Sum Exported data automatically from Access database
Hi
This line of code is giving an error "LastRow = .Range("A" & 65536).End(xlUp).Row" Error - Run time error 1004 Application defined or object defined error Hope to hear from you soon. Thanks again "microsoft" wrote: Hi I created an Access database which exports data to Excel. I would like assistance in creating the required code which would allow me to sum some selected columns automatically after the data is exported (kindly note that I do not want any additional columns created to capture the total figure). Note that the rows from the database may vary hence the excel rows will also vary. Code to determine the row count would need to be included. Thanks in advance |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
Sum Exported data automatically from Access database
You need to add the excel refernce to Access. From Access VBA window
Tools - Reference - Microsft Excel 11.0 object library. Or latest version on your PC. 11.0 is excel 2003 10.0 is excel 2002 9.0 is excel 2000 I figured out what was wrong at work. I was getting a read-only workbook using createobject. I changed to getobject and the code is working except I get a Pop up window asking if I want to save in Excel 5.0. I have excel 2002 at home where I'm working. the pop up window may be hidden behind other window. If the code looks like it has stopped close some of the windows until you find the pop up. I have to leave now. I'm not sure if you get the pop up window since you are using excel 2000. I you get the pop up and want me to fix the problem let me know and I will work on it tomorrow. I'm SUMMING column G. You can change the column to any column and add additional columns. I' also using column A to determine the last row. This can also be changed. Private Sub Command0_Click() FName = "c:\temp\file.xls" MyQuery = "Excel Exportb" MyQuery = "AC_WC_DEMOG_CRSTAB Query" DoCmd.OutputTo _ ObjectType:=acOutputQuery, _ ObjectName:=MyQuery, _ OutputFormat:=acFormatXLS, _ OutputFile:=FName, _ AutoStart:=True Set bk = GetObject(pathname:=FName) With bk.Sheets(MyQuery) LastRow = .Range("A" & 65536).End(xlUp).Row NewRow = LastRow + 1 .Range("G" & NewRow).Formula = _ "=SUM(G2:G" & LastRow & ")" End With bk.Close savechanges:=True Set obj = Nothing End Sub "microsoft" wrote: Hi This line of code is giving an error "LastRow = .Range("A" & 65536).End(xlUp).Row" Error - Run time error 1004 Application defined or object defined error Hope to hear from you soon. Thanks again "microsoft" wrote: Hi I created an Access database which exports data to Excel. I would like assistance in creating the required code which would allow me to sum some selected columns automatically after the data is exported (kindly note that I do not want any additional columns created to capture the total figure). Note that the rows from the database may vary hence the excel rows will also vary. Code to determine the row count would need to be included. Thanks in advance |
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
Sum Exported data automatically from Access database
Remove the line
MyQuery = "AC_WC_DEMOG_CRSTAB Query" I added this to test with one of my databases. You just neeed the line MyQuery = "Excel Exportb" "microsoft" wrote: Hi This line of code is giving an error "LastRow = .Range("A" & 65536).End(xlUp).Row" Error - Run time error 1004 Application defined or object defined error Hope to hear from you soon. Thanks again "microsoft" wrote: Hi I created an Access database which exports data to Excel. I would like assistance in creating the required code which would allow me to sum some selected columns automatically after the data is exported (kindly note that I do not want any additional columns created to capture the total figure). Note that the rows from the database may vary hence the excel rows will also vary. Code to determine the row count would need to be included. Thanks in advance |
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
Sum Exported data automatically from Access database
Thanks for all your help, I got the issue resolved.
"joel" wrote: Remove the line MyQuery = "AC_WC_DEMOG_CRSTAB Query" I added this to test with one of my databases. You just neeed the line MyQuery = "Excel Exportb" "microsoft" wrote: Hi This line of code is giving an error "LastRow = .Range("A" & 65536).End(xlUp).Row" Error - Run time error 1004 Application defined or object defined error Hope to hear from you soon. Thanks again "microsoft" wrote: Hi I created an Access database which exports data to Excel. I would like assistance in creating the required code which would allow me to sum some selected columns automatically after the data is exported (kindly note that I do not want any additional columns created to capture the total figure). Note that the rows from the database may vary hence the excel rows will also vary. Code to determine the row count would need to be included. Thanks in advance |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Exported from Access - text has a character before it | Excel Discussion (Misc queries) | |||
Some columns exported from Access do not sum | Excel Discussion (Misc queries) | |||
Sending Data to Access Database | Excel Discussion (Misc queries) | |||
Conditional Formatting not applied to data exported from Access | Excel Worksheet Functions | |||
excel to get data from an access database | Excel Discussion (Misc queries) |