Code to write Access fields into Excel cells?

[RSIboy]

Thanks to some marvellous help from you guys, I have been able to import the fields in an Excel questionnaire into an Access table.

I now need to populate an Excel 'template' spreadsheet with some of the Access fields. I have been able to make my database write standard letters in Word (populating the name, address etc) using the 'Bookmark' method, but I know it is different with Excel...

Could anyone explain how to open "C:\test.xls" and insert CustomerName in cell A1?

Any help very much appreciated....

 

[KeithG]

This will open up a sh in C drive name test.xls. You will need to modify the field and table names to fit your needs.

Dim xlApp As New Excel.Application
Dim xlWrkBk As Excel.Workbook
Dim xlSht As Excel.Worksheet
Dim myRec As DAO.Recordset
Dim fldCustName As DAO.Field


Set xlWrkBk = GetObject("C:\Test.xls")
Set xlSht = xlWrkBk.Worksheets(1)

Set myRec = CurrentDb.OpenRecordset("tblTest")
Set fldCustName = myRec.Fields("Name")

myRec.MoveFirst

xlSht.Cells(1, "A") = fldCustName
xlWrkBk.Save

 

[RSIboy]

Wow! - Cheers for the speedy response! I've got it working - just one small question...

I'm trying to leave the spreadsheet visible, for further editing... I've tried the line

xlSht.Visible = True
and
xlSht.Visible = xlSheetVisible

neither of which return an error, but don't do anything! I have to open the file, then choose 'Window', then 'Unhide', then click on the workbook to unhide...

Any thoughts?

Thanks in advance....

 

[KeithG]

I have always had the save problem. For some reason the workbook is hidden so trying to unhide the worksheet won't work.

 

[pghcpa7]

xlWrkBk.Parent.Windows("Test2.XLS").Visible = True

See:
http://support.microsoft.com/kb/111247

 

[OldManRiver]

This will open up a sh in C drive name test.xls. You will need to modify the field and table names to fit your needs.

Dim xlApp As New Excel.Application
Dim xlWrkBk As Excel.Workbook
Dim xlSht As Excel.Worksheet
Dim myRec As DAO.Recordset
Dim fldCustName As DAO.Field


Set xlWrkBk = GetObject("C:\Test.xls")
Set xlSht = xlWrkBk.Worksheets(1)

Set myRec = CurrentDb.OpenRecordset("tblTest")
Set fldCustName = myRec.Fields("Name")

myRec.MoveFirst

xlSht.Cells(1, "A") = fldCustName
xlWrkBk.Save

Tried your code and it did not work, but I have slightly different scenario.

My existing code opens a table, writes to an array, opens an excel template file, saves with rename that includes date, then gets to this part where it will write to the SS, but writes from array not a table.

Line that it is choking on is:

Yours:

Set xlWrkBk = GetObject("C:\Test.xls")

Mine:

Set wrkb = GetObject(MySht)

where MySht is passed from the function opening and renaming the SS.

If I post my code, are you open to helping debug?

OMR

 

[sumox]

THERE IS PROBLEM WITH ME TOO reg. same

I've copied the Access 2 Excel code written in this thread's first post's reply

But Error is in form of Bug :
1> The Script Executes thrice if inserted in Reports OnFormat event
2> The Access connection/data trf. agents or whatever language system
Open file in Read only and says "File exists, re-write? or not"
3> file doesn't saves even after yes pressed

There's a lot of codd problem going on

Dim xlApp As New Excel.Application
Dim xlWrkBk As Excel.Workbook
Dim xlSht As Excel.Worksheet
'Dim myRec As DAO.Recordset
Dim fldCustName As String


Set xlWrkBk = GetObject("C:\dodata.xls")
Set xlSht = xlWrkBk.Worksheets("Sheet1")

'Set myRec = CurrentDb.OpenRecordset("tblTest")
fldCustName = "Gword(" & Me.ImportGT.Value & ")"


'myRec.MoveFirst

xlSht.Cells(1, "A") = fldCustName
xlWrkBk.Save
xlWrkBk.Close

Error is attached in picture format See :

PLEEASE HELP
:banghead: