adding data to an excel spreadsheet 1

[robojeff]

I am creating an excel sheet using the Transfersheet function but I would also like to create an additional worksheet within that spreadsheet and populate it with the contents of My_tbl as follows:

Set rs = db.OpenRecordset("My_tbl")
With objXLSheet
Do
.Columns("A:B").HorizontalAlignment = xlCenter
.Cells(intLoop, 1) = rs!Month
.Cells(intLoop, 2) = rs![Pass Qty]
.Cells(intLoop, 3) = rs![Fail Qty]
.Cells(intLoop, 4) = rs![FPY Avg]
.Cells(intLoop, 4).NumberFormat = "0"
intLoop = intLoop + 1
rs.MoveNext
Loop Until rs.EOF
End With

Not sure if this is correct format to add the new worksheet:
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, strFileName ", True, "New Sheet"

Also, how can I set the contents of my objXLSheet to populate the new worksheet that
I create within the spreadsheet created by the TransferSheet function?

thanks

 

[jebry]

Hi robojeff!

I'll need to see all the code to get an idea of what you are doing.

But, in general, if you create an Excel boox using the TransferSpreadsheet method then you have to open the book to do anything with it. It seems that you have opened the book in the code above, but I can't tell if you have created a new sheet in the code or not.

Also, if you let me know what exactly you are trying to accomplish, then I might be able to take you a different direction to do it. I have found that it is often easier to create a template in Excel to work from. You can open it and do a saveas, then add all the data you need. That way all of the formatting is already done.



Jeff Bridgham
Purdue University
Graduate School
Data Analyst

 

[henniec]

Hi Robojeff,

Yes you can open another spreadsheet from within Access.

I assume you are doing the necessary to open the workbook.

There is one additional step you need to do and that is to set the worksheet.

With a slight little twist in your method of transfering data to Excel I do not use the Docmd.Transfer method but use CopyRecordset as shown below. It is an extract of the code I use. You are setting your rs so now you copy the whole table in one step and not the method you are using. You may need to name your heading but it can be used for within Access.

You will need to set the worksheet for each one in the workbook and activate the new worksheet before you can transfer data. Either name your worksheets as I have done or with each new worksheet add one to the previous one . (conSht_Name= conSheet-Name+1).

The name you chose for the new worksheet must be exactly the same as it appears in Excel otherwise you will get an error and dont know why. I would suggest copy the name on the tab in Excel and paste it in Access. (objWkb.Worksheets("RSP").Activate) Any formatting can be done from within Access if needed. I added some below incase you wanted to know how.

I hope it helps.

Hennie

With objXL
.Visible = True

Set objWkb = .Workbooks.Open(conWKB_NAME)

On Error Resume Next

Set objSht = objWkb.Worksheets(conSHT_NAME1) 'RSP
objWkb.Worksheets("RSP").Activate

' If Not Err.Number = 0 Then
' Set objSht = objWkb.Worksheets.Add
' objSht.name = conSHT_NAME1
' End If

objWkb.Windows("RSP").Visible = True

Err.Clear

On Error GoTo 0


With objSht


'Copy data from the two record sets
' On Error Resume Next
.Range("A2").CopyFromRecordset rs1

'Add column headings
.Cells(1, 1).Value = "Sample Date "
.Cells(1, 2).Value = "Control Point"
.Cells(1, 3).Value = "Parameter"
.Cells(1, 4).Value = "Result"

' Format columns
.Columns("A").NumberFormat = "MMM dd, yyyy"
.Columns("A").ColumnWidth = 14
.Columns("B:G").ColumnWidth = 11
.Columns("A").HorizontalAlignment = xlCenter
.Columns("f").HorizontalAlignment = xlLeft