Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
formatting ms access to ms excel report
Hi! I am exporting my report from ms access to ms excel with the help of
command: DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel97, "qryunionpayinv", "D:\My Reports\banking.xls", True I am not having the exported data in the desired currency format of "000.00", nd a small green sign is also coming with the data. Kindly guide me about how to export the data with the desired format, and of course, w/o the green sign, as well, I also want to have the total of the number field at the end of the records. Kindly guide me about how to achieve my task successfully in excel. Regards, Imran. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
formatting ms access to ms excel report
when data is copied to excel from Access, they're just numbers, so there is
no formatting. you should open excel and use code to format the data you can do this with Access VBA too Sub tester() Dim xl As Object Dim wb As Object Dim ws As Object Set xl = CreateObject("Excel.Application") ''xl.Visible = True 'not required - use for testing Set wb = xl.Workbooks.Open("C:\temp\abc.xls") Set ws = wb.ActiveSheet ws.Range("A1").CurrentRegion.NumberFormat = "0.00;(0.00)" 'cleanup - must have!! Set ws = Nothing wb.Close False Set wb = Nothing xl.Quit Set xl = Nothing End Sub "Imran Ghani" wrote in message ... Hi! I am exporting my report from ms access to ms excel with the help of command: DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel97, "qryunionpayinv", "D:\My Reports\banking.xls", True I am not having the exported data in the desired currency format of "000.00", nd a small green sign is also coming with the data. Kindly guide me about how to export the data with the desired format, and of course, w/o the green sign, as well, I also want to have the total of the number field at the end of the records. Kindly guide me about how to achieve my task successfully in excel. Regards, Imran. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
formatting ms access to ms excel report
Hi,
You'll have to apply the formatting in your code yourself, you can't make the export do it automatically. For instance: Dim wb As Workbook Set wb = Workbooks.Open("C:\Test.xls") Dim ws As Worksheet Set ws = wb.Worksheets("Sheet1") ws.Columns("A:A").NumberFormat = "#,##0.00;" Will open a file called test.xls saved at root C, and then in "Sheet1" will set the number format to two decimal places in column A. The green signs are likely to be caused by excel viewing the numbers as text - if hover over them with the mouse it'll say "Number stored as text" - you can use code to correct this too eg ws.range("a1").value = cdbl(ws.range("a1").value) Sam "Imran Ghani" wrote: Hi! I am exporting my report from ms access to ms excel with the help of command: DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel97, "qryunionpayinv", "D:\My Reports\banking.xls", True I am not having the exported data in the desired currency format of "000.00", nd a small green sign is also coming with the data. Kindly guide me about how to export the data with the desired format, and of course, w/o the green sign, as well, I also want to have the total of the number field at the end of the records. Kindly guide me about how to achieve my task successfully in excel. Regards, Imran. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
formatting ms access to ms excel report
Hi Patrick Molloy,
Thanks for your guidance. I have got my number data in column B and C. Please guide me about how to give the above range in the following ws.Range("A1").CurrentRegion Regards, Imran. "Patrick Molloy" wrote: when data is copied to excel from Access, they're just numbers, so there is no formatting. you should open excel and use code to format the data you can do this with Access VBA too Sub tester() Dim xl As Object Dim wb As Object Dim ws As Object Set xl = CreateObject("Excel.Application") ''xl.Visible = True 'not required - use for testing Set wb = xl.Workbooks.Open("C:\temp\abc.xls") Set ws = wb.ActiveSheet ws.Range("A1").CurrentRegion.NumberFormat = "0.00;(0.00)" 'cleanup - must have!! Set ws = Nothing wb.Close False Set wb = Nothing xl.Quit Set xl = Nothing End Sub "Imran Ghani" wrote in message ... Hi! I am exporting my report from ms access to ms excel with the help of command: DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel97, "qryunionpayinv", "D:\My Reports\banking.xls", True I am not having the exported data in the desired currency format of "000.00", nd a small green sign is also coming with the data. Kindly guide me about how to export the data with the desired format, and of course, w/o the green sign, as well, I also want to have the total of the number field at the end of the records. Kindly guide me about how to achieve my task successfully in excel. Regards, Imran. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
formatting ms access to ms excel report
Hi Sam Wilson,
Thanks for your guidance. The code is working fine in the A Column of the worksheet. But I'd like to format my columns B and C with the same code. Kindly do guide me in this respect. I'd tried different options but not succeeded. Regards, Imran. "Sam Wilson" wrote: Hi, You'll have to apply the formatting in your code yourself, you can't make the export do it automatically. For instance: Dim wb As Workbook Set wb = Workbooks.Open("C:\Test.xls") Dim ws As Worksheet Set ws = wb.Worksheets("Sheet1") ws.Columns("A:A").NumberFormat = "#,##0.00;" Will open a file called test.xls saved at root C, and then in "Sheet1" will set the number format to two decimal places in column A. The green signs are likely to be caused by excel viewing the numbers as text - if hover over them with the mouse it'll say "Number stored as text" - you can use code to correct this too eg ws.range("a1").value = cdbl(ws.range("a1").value) Sam "Imran Ghani" wrote: Hi! I am exporting my report from ms access to ms excel with the help of command: DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel97, "qryunionpayinv", "D:\My Reports\banking.xls", True I am not having the exported data in the desired currency format of "000.00", nd a small green sign is also coming with the data. Kindly guide me about how to export the data with the desired format, and of course, w/o the green sign, as well, I also want to have the total of the number field at the end of the records. Kindly guide me about how to achieve my task successfully in excel. Regards, Imran. |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
formatting ms access to ms excel report
Rather than use
ws.Columns("A:A").NumberFormat = "#,##0.00;" use ws.Columns("A:C").NumberFormat = "#,##0.00;" "Imran Ghani" wrote: Hi Sam Wilson, Thanks for your guidance. The code is working fine in the A Column of the worksheet. But I'd like to format my columns B and C with the same code. Kindly do guide me in this respect. I'd tried different options but not succeeded. Regards, Imran. "Sam Wilson" wrote: Hi, You'll have to apply the formatting in your code yourself, you can't make the export do it automatically. For instance: Dim wb As Workbook Set wb = Workbooks.Open("C:\Test.xls") Dim ws As Worksheet Set ws = wb.Worksheets("Sheet1") ws.Columns("A:A").NumberFormat = "#,##0.00;" Will open a file called test.xls saved at root C, and then in "Sheet1" will set the number format to two decimal places in column A. The green signs are likely to be caused by excel viewing the numbers as text - if hover over them with the mouse it'll say "Number stored as text" - you can use code to correct this too eg ws.range("a1").value = cdbl(ws.range("a1").value) Sam "Imran Ghani" wrote: Hi! I am exporting my report from ms access to ms excel with the help of command: DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel97, "qryunionpayinv", "D:\My Reports\banking.xls", True I am not having the exported data in the desired currency format of "000.00", nd a small green sign is also coming with the data. Kindly guide me about how to export the data with the desired format, and of course, w/o the green sign, as well, I also want to have the total of the number field at the end of the records. Kindly guide me about how to achieve my task successfully in excel. Regards, Imran. |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
formatting ms access to ms excel report
change
ws.Range("A1").CurrentRegion to ws.Range("B:C").NumberFormat = "0.00;(0.00)" "Imran Ghani" wrote in message ... Hi Patrick Molloy, Thanks for your guidance. I have got my number data in column B and C. Please guide me about how to give the above range in the following ws.Range("A1").CurrentRegion Regards, Imran. "Patrick Molloy" wrote: when data is copied to excel from Access, they're just numbers, so there is no formatting. you should open excel and use code to format the data you can do this with Access VBA too Sub tester() Dim xl As Object Dim wb As Object Dim ws As Object Set xl = CreateObject("Excel.Application") ''xl.Visible = True 'not required - use for testing Set wb = xl.Workbooks.Open("C:\temp\abc.xls") Set ws = wb.ActiveSheet ws.Range("A1").CurrentRegion.NumberFormat = "0.00;(0.00)" 'cleanup - must have!! Set ws = Nothing wb.Close False Set wb = Nothing xl.Quit Set xl = Nothing End Sub "Imran Ghani" wrote in message ... Hi! I am exporting my report from ms access to ms excel with the help of command: DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel97, "qryunionpayinv", "D:\My Reports\banking.xls", True I am not having the exported data in the desired currency format of "000.00", nd a small green sign is also coming with the data. Kindly guide me about how to export the data with the desired format, and of course, w/o the green sign, as well, I also want to have the total of the number field at the end of the records. Kindly guide me about how to achieve my task successfully in excel. Regards, Imran. |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
formatting ms access to ms excel report
Hi Sam Wilson,
Thanks for your guidance. A run-time error 13: Type Mismatch is coming, which is not letting the format to be completed successfully. When its applied on the date field column A, its formatting the data successfully over there. Kindly guide how to remove the error and format the data successfully in the B & C Columns. Regards, Imran. "Sam Wilson" wrote: Rather than use ws.Columns("A:A").NumberFormat = "#,##0.00;" use ws.Columns("A:C").NumberFormat = "#,##0.00;" "Imran Ghani" wrote: Hi Sam Wilson, Thanks for your guidance. The code is working fine in the A Column of the worksheet. But I'd like to format my columns B and C with the same code. Kindly do guide me in this respect. I'd tried different options but not succeeded. Regards, Imran. "Sam Wilson" wrote: Hi, You'll have to apply the formatting in your code yourself, you can't make the export do it automatically. For instance: Dim wb As Workbook Set wb = Workbooks.Open("C:\Test.xls") Dim ws As Worksheet Set ws = wb.Worksheets("Sheet1") ws.Columns("A:A").NumberFormat = "#,##0.00;" Will open a file called test.xls saved at root C, and then in "Sheet1" will set the number format to two decimal places in column A. The green signs are likely to be caused by excel viewing the numbers as text - if hover over them with the mouse it'll say "Number stored as text" - you can use code to correct this too eg ws.range("a1").value = cdbl(ws.range("a1").value) Sam "Imran Ghani" wrote: Hi! I am exporting my report from ms access to ms excel with the help of command: DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel97, "qryunionpayinv", "D:\My Reports\banking.xls", True I am not having the exported data in the desired currency format of "000.00", nd a small green sign is also coming with the data. Kindly guide me about how to export the data with the desired format, and of course, w/o the green sign, as well, I also want to have the total of the number field at the end of the records. Kindly guide me about how to achieve my task successfully in excel. Regards, Imran. |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
formatting ms access to ms excel report
Hi Patrick Molloy,
The command is working fine for the data in column A which is date field, but when applied to columns B & C, its giving a run-time error 13: Type Mismatch. Kindly guide about how to remove the error. Regards, Imran. "Patrick Molloy" wrote: change ws.Range("A1").CurrentRegion to ws.Range("B:C").NumberFormat = "0.00;(0.00)" "Imran Ghani" wrote in message ... Hi Patrick Molloy, Thanks for your guidance. I have got my number data in column B and C. Please guide me about how to give the above range in the following ws.Range("A1").CurrentRegion Regards, Imran. "Patrick Molloy" wrote: when data is copied to excel from Access, they're just numbers, so there is no formatting. you should open excel and use code to format the data you can do this with Access VBA too Sub tester() Dim xl As Object Dim wb As Object Dim ws As Object Set xl = CreateObject("Excel.Application") ''xl.Visible = True 'not required - use for testing Set wb = xl.Workbooks.Open("C:\temp\abc.xls") Set ws = wb.ActiveSheet ws.Range("A1").CurrentRegion.NumberFormat = "0.00;(0.00)" 'cleanup - must have!! Set ws = Nothing wb.Close False Set wb = Nothing xl.Quit Set xl = Nothing End Sub "Imran Ghani" wrote in message ... Hi! I am exporting my report from ms access to ms excel with the help of command: DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel97, "qryunionpayinv", "D:\My Reports\banking.xls", True I am not having the exported data in the desired currency format of "000.00", nd a small green sign is also coming with the data. Kindly guide me about how to export the data with the desired format, and of course, w/o the green sign, as well, I also want to have the total of the number field at the end of the records. Kindly guide me about how to achieve my task successfully in excel. Regards, Imran. |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
formatting ms access to ms excel report
Hi,
Thanks for your helping notes. I have not yet been able to apply the command to the desired columns, as there is an error message of data type mismatch. Can you please guide me about the command which can convert data back to number from other formats. Regards, Imran. "Patrick Molloy" wrote: change ws.Range("A1").CurrentRegion to ws.Range("B:C").NumberFormat = "0.00;(0.00)" "Imran Ghani" wrote in message ... Hi Patrick Molloy, Thanks for your guidance. I have got my number data in column B and C. Please guide me about how to give the above range in the following ws.Range("A1").CurrentRegion Regards, Imran. "Patrick Molloy" wrote: when data is copied to excel from Access, they're just numbers, so there is no formatting. you should open excel and use code to format the data you can do this with Access VBA too Sub tester() Dim xl As Object Dim wb As Object Dim ws As Object Set xl = CreateObject("Excel.Application") ''xl.Visible = True 'not required - use for testing Set wb = xl.Workbooks.Open("C:\temp\abc.xls") Set ws = wb.ActiveSheet ws.Range("A1").CurrentRegion.NumberFormat = "0.00;(0.00)" 'cleanup - must have!! Set ws = Nothing wb.Close False Set wb = Nothing xl.Quit Set xl = Nothing End Sub "Imran Ghani" wrote in message ... Hi! I am exporting my report from ms access to ms excel with the help of command: DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel97, "qryunionpayinv", "D:\My Reports\banking.xls", True I am not having the exported data in the desired currency format of "000.00", nd a small green sign is also coming with the data. Kindly guide me about how to export the data with the desired format, and of course, w/o the green sign, as well, I also want to have the total of the number field at the end of the records. Kindly guide me about how to achieve my task successfully in excel. Regards, Imran. |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
formatting ms access to ms excel report
Hi Sam Wilson,
Thanks for you kind help. Can you please also help about how to format the number data as currency, and I'd appreciate much if you could also guide me to have the sum of the values at the end of the data, programmatically, whatever the number of the records is, if it is possible. Thanks in advance. Regards, Imran. "Sam Wilson" wrote: Rather than use ws.Columns("A:A").NumberFormat = "#,##0.00;" use ws.Columns("A:C").NumberFormat = "#,##0.00;" "Imran Ghani" wrote: Hi Sam Wilson, Thanks for your guidance. The code is working fine in the A Column of the worksheet. But I'd like to format my columns B and C with the same code. Kindly do guide me in this respect. I'd tried different options but not succeeded. Regards, Imran. "Sam Wilson" wrote: Hi, You'll have to apply the formatting in your code yourself, you can't make the export do it automatically. For instance: Dim wb As Workbook Set wb = Workbooks.Open("C:\Test.xls") Dim ws As Worksheet Set ws = wb.Worksheets("Sheet1") ws.Columns("A:A").NumberFormat = "#,##0.00;" Will open a file called test.xls saved at root C, and then in "Sheet1" will set the number format to two decimal places in column A. The green signs are likely to be caused by excel viewing the numbers as text - if hover over them with the mouse it'll say "Number stored as text" - you can use code to correct this too eg ws.range("a1").value = cdbl(ws.range("a1").value) Sam "Imran Ghani" wrote: Hi! I am exporting my report from ms access to ms excel with the help of command: DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel97, "qryunionpayinv", "D:\My Reports\banking.xls", True I am not having the exported data in the desired currency format of "000.00", nd a small green sign is also coming with the data. Kindly guide me about how to export the data with the desired format, and of course, w/o the green sign, as well, I also want to have the total of the number field at the end of the records. Kindly guide me about how to achieve my task successfully in excel. Regards, Imran. |
#12
Posted to microsoft.public.excel.programming
|
|||
|
|||
formatting ms access to ms excel report
you need to give us more information regarding the error message.
it is unlikely that this line of code ws.Range("A1").CurrentRegion will raise any errors If you want just those two columns formatted then ws.Range("B:C").NumberFormat = "0.00;(0.00)" should work fine. if cells contain TEXT, then of course, they don't get formatted, but this is not an error. if not, then you need to give us more details of the data or errors. "Imran Ghani" wrote in message ... Hi, Thanks for your helping notes. I have not yet been able to apply the command to the desired columns, as there is an error message of data type mismatch. Can you please guide me about the command which can convert data back to number from other formats. Regards, Imran. "Patrick Molloy" wrote: change to ws.Range("B:C").NumberFormat = "0.00;(0.00)" "Imran Ghani" wrote in message ... Hi Patrick Molloy, Thanks for your guidance. I have got my number data in column B and C. Please guide me about how to give the above range in the following ws.Range("A1").CurrentRegion Regards, Imran. "Patrick Molloy" wrote: when data is copied to excel from Access, they're just numbers, so there is no formatting. you should open excel and use code to format the data you can do this with Access VBA too Sub tester() Dim xl As Object Dim wb As Object Dim ws As Object Set xl = CreateObject("Excel.Application") ''xl.Visible = True 'not required - use for testing Set wb = xl.Workbooks.Open("C:\temp\abc.xls") Set ws = wb.ActiveSheet ws.Range("A1").CurrentRegion.NumberFormat = "0.00;(0.00)" 'cleanup - must have!! Set ws = Nothing wb.Close False Set wb = Nothing xl.Quit Set xl = Nothing End Sub "Imran Ghani" wrote in message ... Hi! I am exporting my report from ms access to ms excel with the help of command: DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel97, "qryunionpayinv", "D:\My Reports\banking.xls", True I am not having the exported data in the desired currency format of "000.00", nd a small green sign is also coming with the data. Kindly guide me about how to export the data with the desired format, and of course, w/o the green sign, as well, I also want to have the total of the number field at the end of the records. Kindly guide me about how to achieve my task successfully in excel. Regards, Imran. |
#13
Posted to microsoft.public.excel.programming
|
|||
|
|||
formatting ms access to ms excel report
Hi Patrick Molloy,
Thanks for your reply. Actually, a run-time error 13: Type Mismatch is coming, which is not letting the format to be completed successfully on columns B & C. When its applied on the date field column A, its formatting the data successfully over there. I am calculating my fields from an sql joint query which is using the column alias names, and so the number columns are converted into text, when data is transferred into Excel, on which the code is not being applied successfully. A green arrow mark is coming on the column, on which message is coming to change the data from text to number. Kindly guide how to remove the error and format the data successfully in the B & C Columns. I'd much appreciate your guidance. "Patrick Molloy" wrote: you need to give us more information regarding the error message. it is unlikely that this line of code ws.Range("A1").CurrentRegion will raise any errors If you want just those two columns formatted then ws.Range("B:C").NumberFormat = "0.00;(0.00)" should work fine. if cells contain TEXT, then of course, they don't get formatted, but this is not an error. if not, then you need to give us more details of the data or errors. "Imran Ghani" wrote in message ... Hi, Thanks for your helping notes. I have not yet been able to apply the command to the desired columns, as there is an error message of data type mismatch. Can you please guide me about the command which can convert data back to number from other formats. Regards, Imran. "Patrick Molloy" wrote: change to ws.Range("B:C").NumberFormat = "0.00;(0.00)" "Imran Ghani" wrote in message ... Hi Patrick Molloy, Thanks for your guidance. I have got my number data in column B and C. Please guide me about how to give the above range in the following ws.Range("A1").CurrentRegion Regards, Imran. "Patrick Molloy" wrote: when data is copied to excel from Access, they're just numbers, so there is no formatting. you should open excel and use code to format the data you can do this with Access VBA too Sub tester() Dim xl As Object Dim wb As Object Dim ws As Object Set xl = CreateObject("Excel.Application") ''xl.Visible = True 'not required - use for testing Set wb = xl.Workbooks.Open("C:\temp\abc.xls") Set ws = wb.ActiveSheet ws.Range("A1").CurrentRegion.NumberFormat = "0.00;(0.00)" 'cleanup - must have!! Set ws = Nothing wb.Close False Set wb = Nothing xl.Quit Set xl = Nothing End Sub "Imran Ghani" wrote in message ... Hi! I am exporting my report from ms access to ms excel with the help of command: DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel97, "qryunionpayinv", "D:\My Reports\banking.xls", True I am not having the exported data in the desired currency format of "000.00", nd a small green sign is also coming with the data. Kindly guide me about how to export the data with the desired format, and of course, w/o the green sign, as well, I also want to have the total of the number field at the end of the records. Kindly guide me about how to achieve my task successfully in excel. Regards, Imran. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Exporting an Access report into Excel | Excel Discussion (Misc queries) | |||
Analyzing Access Report with Excel | Excel Discussion (Misc queries) | |||
MS OfficeLinks (from Access report to Excel) | Excel Discussion (Misc queries) | |||
Printing an Access Report from Excel | Excel Programming | |||
Create Access like report in Excel. | Excel Programming |