Help with adding to the code

[voskouee]

After testing and reading i came up with the following code to export a single table ton an Excel Spreadsheet. I cant get to commands for all the things i want.. so if someone can help me or direct me i would be gratefull.

1. I want to export all tables (their names are lister in a table, so i can gather them from there)
2. i want to rename the sheets names to the table names
3. i want to colour some line based on some criteria
4. i want to add the date to the name of the created file. eg. TD21022007.. or something..

This is code i have so far..

thanks in advance...

Sub CopyToExcel()

Dim conn As ADODB.Connection
Dim rst As ADODB.Recordset
Dim wbk As Excel.Workbook
Dim wks As Excel.Worksheet
Dim startrange As Excel.Range
Dim strConn As String
Dim i As Integer
Dim f As Variant

Dim rs As DAO.Recordset
Dim strSQL As String


Set rs = CurrentDb.OpenRecordset("SELECT * FROM RN00144GE3;")

Do While Not rs.EOF


'declare a module level object
'variable myExcel as application
'at the top of the module

Set myExcel = New Excel.Application

'create new Excel Workbook
Set wbk = myExcel.Workbooks.Add

'set the reference to the Active Sheet
Set wks = wbk.ActiveSheet


'make the excel apllication window visible
myExcel.Visible = True

i = 1

'create the column headings in cells
With rs
For Each f In .Fields
With wks
.Cells(1, i).Value = f.Name
i = i + 1
End With
Next
End With


' specify the cell range that will receive data (A2)
Set startrange = wks.Cells(2, 1)

'copy the records from the recordset
'and place in cel A2
startrange.CopyFromRecordset rs

Loop

rs.Close
Set rs = Nothing

'autofit the columns to make the data fit
'wks.Columns("A:Z").autofit
wks.Columns.AutoFit

'close the workbook and save the file
wbk.Close savechanges:=True, _
FileName:="c:\TestingRFG.xls"

'quit the Excel Application
myExcel.Quit
Set conn = Nothing


End Sub

 

[namliam]

Why not "simply" use the docmd.transferspreadsheet command to export to excel?

 

[voskouee]

Why not "simply" use the docmd.transferspreadsheet command to export to excel?

that will not give me the chance to export tables to different sheets in the same workbook..

 

[namliam]

1. I want to export all tables (their names are lister in a table, so i can gather them from there)
2. i want to rename the sheets names to the table names
3. i want to colour some line based on some criteria
4. i want to add the date to the name of the created file. eg. TD21022007.. or something..

OK Lets see... some of this is right in the help...
1) Check out the help for "Tabledefs"
2+3) record your action in Excel and cheat of the code recorded
4) wbk.Close savechanges:=True, _
FileName:="c:\TestingRFG" & format(date(),"YYYYMMDD") & ".xls"

Needless to point out probably but you have a problem if the tables start exceeding 65000 records!

 

[voskouee]

Object Required?

When i try to change the Activesheet to Sheet2 i get the error

"object required" Any ideas?


Set Rs2 = CurrentDb.OpenRecordset("SELECT * FROM RN00144GE4RFG;")

Do While Not Rs2.EOF


'set the reference to the Active Sheet
Set wks = Sheets("Sheet2").Select

 

[namliam]

You have to specify that you are doing something in excel...

Set wks = MyExcel.Sheets("Sheet2").Select

 

[voskouee]

is still giving me the same error.
the excel is open from previous command. whatelse might be..

i am opening a recordset.
selecting/adding a sheet and then copying
then the same again untill all my tables are copied.

any ideas?

 

[namliam]

By doing
Set wks = MyExcel.Sheets("Sheet2")

You are automaticaly selecting it... Remove the .select I didnt spot that due to your first error...