Exporting to an Excell template 1

[jedel]

Hi all,

Been working on some code that I found on this Forum and I'm have a few problems with it. I want to export multiple records to specific columns in an excel spreadsheet.

The code I have so far:

Public Sub ExportQuery()
On Error GoTo err_Handler

    'Excel object variables
    Dim appExcel As Excel.Application
    Dim wbk As Excel.Workbook
    Dim wks As Excel.Worksheet
    
    Dim sTemplate As String
    Dim sTempFile As String
    Dim sOutput As String
    Dim counter
    Dim dbs As DAO.Database
    Dim rst As DAO.Recordset
    Dim sSQL As String
    Dim IRecords As Long
    Dim iRow As Integer
    Dim iCol As Integer
    Dim iFld As Integer
     
   sOutput = CurrentProject.Path & "\RosterTemplate.xls"
    
    'Create the Excel Application, Workbook and Worksheet and Database object
    Set appExcel = New Excel.Application
    appExcel.Visible = True
    Set wbk = appExcel.Workbooks.Open(sOutput)
    
    sSQL = "SELECT * FROM tblSample"
    Set dbs = CurrentDb
    Set rst = dbs.OpenRecordset(sSQL, dbOpenSnapshot)
    If Not rst.BOF Then
    rst.MoveFirst
counter = 1
    Do While Not rst.EOF
        With wbk
            .Sheets("Week1,2").Cells(counter, "B1") = rst.Fields("AppointmentDate")
            .Sheets("Week1,2").Cells(counter, "B2") = rst.Fields("AppointmentDesc")
            .Sheets("Week1,2").Cells(counter, "B4") = rst.Fields("AppointmentTime")
            .Sheets("Week1,2").Cells(counter, "B5") = rst.Fields("MC")
            .Sheets("Week1,2").Cells(counter, "B6") = rst.Fields("Who")
            .Sheets("Week1,2").Cells(counter, "B7") = rst.Fields("LeadVox")
            .Sheets("Week1,2").Cells(counter, "B8") = rst.Fields("Vox1")
            .Sheets("Week1,2").Cells(counter, "B9") = rst.Fields("vox2")
            .Sheets("Week1,2").Cells(counter, "B10") = rst.Fields("vox3")
            .Sheets("Week1,2").Cells(counter, "B11") = rst.Fields("vox4")
            .Sheets("Week1,2").Cells(counter, "B12") = rst.Fields("vox5")
            .Sheets("Week1,2").Cells(counter, "B13") = rst.Fields("vox6")
            .Sheets("Week1,2").Cells(counter, "B14") = rst.Fields("piano")
            .Sheets("Week1,2").Cells(counter, "B15") = rst.Fields("keys1")
            .Sheets("Week1,2").Cells(counter, "B16") = rst.Fields("keys2")
            .Sheets("Week1,2").Cells(counter, "B17") = rst.Fields("LGtr")
            .Sheets("Week1,2").Cells(counter, "B18") = rst.Fields("RGtr")
            .Sheets("Week1,2").Cells(counter, "B19") = rst.Fields("AccGtr")
            .Sheets("Week1,2").Cells(counter, "B20") = rst.Fields("Bass")
            .Sheets("Week1,2").Cells(counter, "B21") = rst.Fields("sax")
            .Sheets("Week1,2").Cells(counter, "B22") = rst.Fields("Drums")
            .Sheets("Week1,2").Cells(counter, "B23") = rst.Fields("FOH")
            .Sheets("Week1,2").Cells(counter, "B24") = rst.Fields("SndStg")
            .Sheets("Week1,2").Cells(counter, "B25") = rst.Fields("Light")
            .Sheets("Week1,2").Cells(counter, "B26") = rst.Fields("LightAss")
            .Sheets("Week1,2").Cells(counter, "B27") = rst.Fields("Graphic")
            .Sheets("Week1,2").Cells(counter, "B28") = rst.Fields("vision")
            .Sheets("Week1,2").Cells(counter, "B29") = rst.Fields("cam1")
            .Sheets("Week1,2").Cells(counter, "B30") = rst.Fields("cam2")
            .Sheets("Week1,2").Cells(counter, "B31") = rst.Fields("rec")
            .Sheets("Week1,2").Cells(counter, "B32") = rst.Fields("Items")
            .Sheets("Week1,2").Cells(counter, "B33") = rst.Fields("Songlist")

' repeat these statements for each field
            counter = counter + 1

        End With
        rst.MoveNext
    Loop
    rst.Close
    
   
exit_Here:
'Cleanup all objects (resume next on errors)
Set wbk = Nothing
appExcel.Quit
Set appExcel = Nothing
Set rst = Nothing
Set dbs = Nothing
Exit Sub

err_Handler:
    MsgBox "Error is " & Err.Description
    Resume exit_Here
 End If
End Sub

The original Sub heading was

Public Sub ExportQuery() as String

But this shows up as red in Access 2003

I referenced Excel object library 11.0 to eliminate the application errors.

When I apply the code. It will open the spreadsheet, but it will not go any further and comes up with and error:
"Application-defined or Object-defined Error"

Any help would be appreciated

Cheers

Dean

-------------------------------------------------------------
"The most overlooked advantage of owning a computer is that if they foul up there's no law against whacking them around a bit."

 

[ck1999]

The error is because of

Cells(counter, "B33")

for cells(rowNum, colNum)

Try using
cells(counter,"B")
cells(counter,"c")
cells(counter,"d")
cells(counter,"e")
cells(counter,"f")
etc . . .
to put each record in a row in excel

ck1999

 

[jedel]

Ah!

Yes I see that now. Problem is I need to put the records in a column. ie on recordset down column B, the next down c etc.

The other issue is That I need to skip past some rows. I got around this by going

cells(counter + 2,"B")

This gets the first record into column B ok, but then the code just overwrites that column.

Thoughts?


-------------------------------------------------------------
"The most overlooked advantage of owning a computer is that if they foul up there's no law against whacking them around a bit."

 

[PHV]

Why not simply use the CopyFromRecordset method of the Excel.Range object ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[ck1999]

Your counter should be your column and your row should be put in manually

cells(1,counter)
cells(2,counter)
cells(4,counter)

ck1999

 

[jedel]

PHV

I don't use it mainly because this is the first time I've had to get data to an Excel spreadsheet template. I'm open to any idea that works. I'll check the help files for the CopyFromRecordset method. Does the code belong to Access or Excel?

ck1999
Thanks for that. I changed the code around. and made the counter = "B" and the first column went in nicely....and then stopped. I need to then go to column C, E,F, H,I until the recordset goes to .EOF

How do I add one to counter so that B = C and so on?

We're getting there

Cheers

Dean

-------------------------------------------------------------
"The most overlooked advantage of owning a computer is that if they foul up there's no law against whacking them around a bit."

 

[PHV]

Column B is column 2, C is 3, ...

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[ck1999]

Does not you code add 1 to counter

if you use cells(1,counter)

then when counter=2 col B has data added to it
when counter=3 col C has data added to it
when counter 4 col 5 has data added to it

for skipping columns
option 1
you could use an array such as

vcol = array("b","c","e","f","h","i", etc....)

then leave counter = counter +1
and change cells(1,vcol(counter)

option2

change counter=1 to counter=2

change counter=counter+1 to

if counter mod 3 = 1 then
counter = counter +2
else
counter = counter+1
end if

ck1999

 

[ck1999]

last post should of said col d not col 5

ck1999

 

[jedel]

ck1999,

That was a good progression! and worthy of a star!

I used the Array (option 1) and the data flowed through nicely.

The next hurdle. So far, I've set this up using data from a table;

sSQL = SELECT * FROM tblSample

What I need to do now is make sure that the data that goes into the spreadsheet is in order and from a specific Range. I have a query which details it for me, however i get an error;

too few parameters, expected 1"

That was after I placed the name of the query into the sSQL line.

Should I have just grabbed the SQL? or is there a way for the query to work?

Cheers

Dean

-------------------------------------------------------------
"The most overlooked advantage of owning a computer is that if they foul up there's no law against whacking them around a bit."

 

[PHV]

What is th SQL code of the query ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[ck1999]

Did you use : sSQL = "SELECT * FROM qryTable" 'qryTable = =name of your query

I would copy and paste your sql from the query into your code.

If you still get an error post your actual code

ck1999

 

[jedel]

Sorry Guys, Here it is the offending area:

sSQL = "SELECT tblSample.* FROM tblSample" _
    & " WHERE (((tblSample.dteMonth) = [Forms]![DteSelectFRM]![cboMonth]))" _
    & " ORDER BY tblSample.AppointmentDate, tblSample.AppointmentTime;"

    Set dbs = CurrentDb
    Set rst = dbs.OpenRecordset(sSQL, dbOpenSnapshot)

Dean

-------------------------------------------------------------
"The most overlooked advantage of owning a computer is that if they foul up there's no law against whacking them around a bit."

 

[PHV]

sSQL = "SELECT * FROM tblSample" _
& " WHERE dteMonth=" & Forms!DteSelectFRM!cboMonth" _
& " ORDER BY AppointmentDate, AppointmentTime"

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[ck1999]

try

sSQL = "SELECT tblSample.* FROM tblSample WHERE (((tblSample.dteMonth) = " & [Forms]![DteSelectFRM]![cboMonth] & "))"_ & " ORDER BY tblSample.AppointmentDate, tblSample.AppointmentTime;"

ck1999

 

[jedel]

Sorry Guys

I tried both ways, Each code when pasted still got the same Parameter error.



-------------------------------------------------------------
"The most overlooked advantage of owning a computer is that if they foul up there's no law against whacking them around a bit."

 

[PHV]

Sorry for the typo:

sSQL = "SELECT * FROM tblSample" _
    & " WHERE dteMonth=" & Forms!DteSelectFRM!cboMonth _
    & " ORDER BY AppointmentDate, AppointmentTime"

The DteSelectFRM form should be open.

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[jedel]

PHV,

Yes it is. That's where I call the function from.
And Sorry. Still get that pesky
"Error is too few parameters, Expected 1."

The error definately lies in the WHERE line. I'm woindering if I have to specify a column,(ie Forms!DteSelectFRM!cboMonth.Column(0))

It works fine without the WHERE parameter.


-------------------------------------------------------------
"The most overlooked advantage of owning a computer is that if they foul up there's no law against whacking them around a bit."

 

[PHV]

Does tblSample have a field named dteMonth ?
What is the result of Debug.Print sSQL ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[jedel]

Yes, the tblSample has a dteMonth

I can't get to the debug.Print sSQL line because the Parameter Error stops the code before it can reach it. Unless I'm putting the Debug.Print line where It shouldn't go? Immediately after the sSQL query?

Thanks for your perseverance

Dean

-------------------------------------------------------------
"The most overlooked advantage of owning a computer is that if they foul up there's no law against whacking them around a bit."