 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
MS Access Forum / Forms / December 2007Output to excel ==> replace worksheet
Usually out to excel will replace a workbook.Can we make it to replace worksheet only.Thank's > Usually out to excel will replace a workbook.Can we make it > to replace worksheet only.Thank's How about, just replacing the contents of a cell, or cells? Dim appXL As Object Dim wkb As Object Dim wks As Object Set appXL = CreateObject("Excel.Application") Set wkb = appXL.Workbooks.Open("C:\MyFolder\MyWorkBook.xls") Set wks = wkb.Worksheets(1) wks.Cells(1, 1) = "Hello World" wks.Cells(1, 2) = "Good-bye now"  SignatureArvin Meyer, MCP, MVP http://www.datastrat.com http://www.mvps.org/access http://www.accessmvp.com I'm sorry Mr Arvin, I haven't test your code,but I want to ask more question. If I outputting a continuous form,and if there are some records in it,does this code will insert all records of a field into one cell? And then,how to cahnge the value of the cell with the value of my query's fields or my form's fields. Thank's for help > > Usually out to excel will replace a workbook.Can we make it > > to replace worksheet only.Thank's [quoted text clipped - 16 lines] > http://www.mvps.org/access > http://www.accessmvp.com Instead of inserting values cell by cell, you can use Excel ranges to insert records row by row. Here's some code that will do that: http://www.mvps.org/access/modules/mdl0035.htm SignatureArvin Meyer, MCP, MVP http://www.datastrat.com http://www.mvps.org/access http://www.accessmvp.com > I'm sorry Mr Arvin, > I haven't test your code,but I want to ask more question. [quoted text clipped - 24 lines] >> http://www.mvps.org/access >> http://www.accessmvp.com I've read the code written by Mr Dev on thesite but that is not excatly what I want.Here is my condition: Some data that I want to output to are on the form Header,and the others are on the detail section of my continuous form.Below is where I've got so far Dim appXL As Object Dim wkb As Object Dim wks As Object Set appXL = CreateObject("Excel.Application") Set wkb = appXL.Workbooks.Open("C:\MyFolder\My Workbook.xls") Set wks = wkb.Worksheets(1) appXL.Visible = True wks.Cells(4, 3) = [Customer] wks.Cells(6, 3) = [LotNo] wks.Cells(7, 3) = [Model] Field 'Customer' and 'LotNo' are on the form header and 'Model' is on the detail form.And I always limitting the number of record returned to 5 record only that mean excel cell (7, 3) until cell (11, 3) should contains the 5 values of the 'Model'.How to do that.? > Instead of inserting values cell by cell, you can use Excel ranges to insert > records row by row. Here's some code that will do that: [quoted text clipped - 34 lines] > >> http://www.mvps.org/access > >> http://www.accessmvp.com > Field 'Customer' and 'LotNo' are on the form header and 'Model' is on > the detail form.And I always limitting the number of record returned to > 5 record only that mean excel cell (7, 3) until cell (11, 3) should > contains > the 5 values of the 'Model'.How to do that.? You must build a recordset or a query and either export the query with the TransferSpreadsheet function (look it up in help) Or loop through a recordset, writing the data to the Excel sheet one cell at a time. SignatureArvin Meyer, MCP, MVP http://www.datastrat.com http://www.mvps.org/access http://www.accessmvp.com |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.