Hello sir,
I want to export data to existing excel file. can it is possible?
Thank you in advance.
Hello sir,
I want to export data to existing excel file. can it is possible?
Thank you in advance.
Yes.
See Ken Snell's site at: http://www.accessmvp.com/kdsnell/EXCEL_MainPage.htm
Thanku You.
is helpful to be.Dim lngColumn As LongDim xlx As Object, xlw As Object, xls As Object, xlc As Object
Dim dbs As DAO.Database
Dim rst As DAO.Recordset
Dim blnEXCEL As Boolean, blnHeaderRow As Boolean
blnEXCEL = False' Replace True with False if you do not want the first row of
' the worksheet to be a header row (the names of the fields
' from the recordset)
blnHeaderRow = True
' Establish an EXCEL application object
On Error Resume Next
Set xlx = GetObject(, "Excel.Application")
If Err.Number <> 0 Then
Set xlx = CreateObject("Excel.Application")
blnEXCEL = True
End If
Err.Clear
On Error GoTo 0
' Change True to False if you do not want the workbook to be
' visible when the code is running
xlx.Visible = True
' Replace C:\Filename.xls with the actual path and filename
' of the EXCEL file into which you will write the data
Set xlw = xlx.Workbooks.Open("C:\Filename.xls")
' Replace WorksheetName with the actual name of the worksheet
' in the EXCEL file
' (note that the worksheet must already be in the EXCEL file)
Set xls = xlw.Worksheets("WorksheetName")' Replace A1 with the cell reference into which the first data value
' is to be written
Set xlc = xls.Range("A1") ' this is the first cell into which data go
Set dbs = CurrentDb()
' Replace QueryOrTableName with the real name of the table or query
' whose data are to be written into the worksheet
Set rst = dbs.OpenRecordset("QueryOrTableName", dbOpenDynaset, dbReadOnly)
If rst.EOF = False And rst.BOF = False Thenrst.MoveFirst
If blnHeaderRow = True Then
For lngColumn = 0 To rst.Fields.Count - 1
xlc.Offset(0, lngColumn).Value = rst.Fields(lngColumn).Name
Next lngColumn
Set xlc = xlc.Offset(1,0)
End If
' write data to worksheet
Do While rst.EOF = False
For lngColumn = 0 To rst.Fields.Count - 1
xlc.Offset(0, lngColumn).Value = rst.Fields(lngColumn).Value
Next lngColumn
rst.MoveNext
Set xlc = xlc.Offset(1,0)
LoopEnd If
rst.Close
Set rst = Nothingdbs.Close
Set dbs = Nothing' Close the EXCEL file while saving the file, and clean up the EXCEL objectsSet xlx = Nothing
Set xlc = Nothing
Set xls = Nothing
xlw.Close True ' close the EXCEL file and save the new data
Set xlw = Nothing
If blnEXCEL = True Then xlx.Quit
but i have some cell written protected.
I want that where cell are written protected transfer should be skipped to another unprotected cell.
can it is possible.
Thank you once again
Yes. you have to be able to determine when a cell in protected and have the code skip that cell. I haven't written code for Excel for years - I think the last time was for Excel 2003.
So I can't tell you exactly how to write the code. But I think it can be done.
Good luck.
have you any code for this.
Thank You in advance
hey buddy,
can anybody help?
Thanks in advance
No, sorry. You will have to modify the code to suit your purpose.have you any code for this
Try recording a macro in Excel, then adapt the code to your Access code. That is what I do when I have to export to excel and format the data in the worksheet..
i don't no how can record a macro in excel?
Thanks in advance