printing records to Excel worksheets

[gtslabs]

I am trying to open an excel file and insert the data from a Table or
Query into a sheet and print.
I actually need to loop thru all the filtered records and do this
task.

Currently I am using this code to open the excel file and place the
data into Cell B2 then print. This part works but I need to integrate
the table/query loop to print all the records.

I have a table called tbl_data with 3 fields.
Data, ExcelFileName, ExcelSheetName


Private Sub PrintToExcel()
Dim objXLApp As Excel.Application
Dim objXLBook As Excel.Workbook
Dim objDataSheet As Excel.Worksheet

Set objXLBook = GetObject("C:\test.xls") ' Put File Name Here
'Set objXLBook = GetObject(Application.CurrentProject.Path &
"\ExcelFile\FileName.xls")

Set objXLApp = objXLBook.Parent
Set objDataSheet = objXLBook.Worksheets("Data") 'Select Worksheet

'Need to loop thru records and place field contents here.
objDataSheet.Cells(1, 2).Value = "data" ' test with string works.

objDataSheet.PrintOut Copies:=1, Collate:=True
objXLBook.Close savechanges:=False 'close without changes
Set objDataSheet = Nothing
Set objXLBook = Nothing
Set objXLApp = Nothing

End Sub

 

[JonWayn]

You know, if you really like things the easy way, then instead of looping
thru all records, you could just do :

docmd.TransferSpreadsheet acExport,acSpreadsheetTypeExcel9, "TableName",
"C:\MyPath\SomeSpreadsheet.xls",true

That single statement above creates an xls file with the given name and adds
all the records in the table to it. The only downside to that method is that
you cant tell it to place the records in B1 - it will force the first record
in row 1. But since you are using xl automation you can easily get that
shifted afterwards.



If, on the other hand, you like to experiment with more technical methods,
nothing wrong with that by the way, then you can do:


Dim objXLApp As Excel.Application
Dim objXLBook As Excel.Workbook
Dim objDataSheet As Excel.Worksheet
Dim db as Database, rst as Recordset
Dim r%, c%, rr%

Set db = CurrentDb
Set rst = db.OpenRecordset("TableName")
If Not rst.BOF And Not rst.EOF Then rst.MoveFirst
Set objXLBook = GetObject("C:\test.xls")
Set objXLApp = objXLBook.Parent
Set objDataSheet = objXLBook.Worksheets("Data") 'Select Worksheet
r=1 'Row index; initialize to 1 to skip
row 1 in loop below

Do Until rst.EOF
r = r + 1
'increment row index

'write first field data to column A of current row
objDataSheet.Cells(r, 1) = rst![Data]
'write 2nd field data to column B of current row
objDataSheet.Cells(r, 2) = rst!ExcelFileName
'write 3rd field data to column C of current row
objDataSheet.Cells(r, 3) = rst!ExcelSheetName
rst.MoveNext
Loop

rst.Close
Set rst = nothing
db.Close
Set db = Nothing

'Clean up your xl objects here


+++++++++++++++++++++++++++++++++++++++++++++++++++++++++

 

[gtslabs]

You know, if you really like things the easy way, then instead of looping
thru all records, you could just do :

docmd.TransferSpreadsheet acExport,acSpreadsheetTypeExcel9,  "TableName",
"C:\MyPath\SomeSpreadsheet.xls",true

That single statement above creates an xls file with the given name and adds
all the records in the table to it. The only downside to that method is that
you cant tell it to place the records in B1 - it will force the first record
in row 1. But since you are using xl automation you can easily get that
shifted afterwards.

If, on the other hand, you like to experiment with more technical methods,
nothing wrong with that by the way, then you can do:

Dim objXLApp As Excel.Application
Dim objXLBook As Excel.Workbook
Dim objDataSheet As Excel.Worksheet
Dim db as Database, rst as Recordset
Dim r%, c%, rr%

Set db = CurrentDb
Set rst = db.OpenRecordset("TableName")
If Not rst.BOF And Not rst.EOF Then   rst.MoveFirst
Set objXLBook = GetObject("C:\test.xls")
Set objXLApp = objXLBook.Parent
Set objDataSheet = objXLBook.Worksheets("Data") 'Select Worksheet
r=1                                    'Row index; initialize to 1 to skip
row 1 in loop below

Do Until rst.EOF
    r = r + 1                                                          
'increment row index

        'write first field data to column A of current row
    objDataSheet.Cells(r, 1) = rst![Data]    
        'write 2nd field data to column B of current row              
    objDataSheet.Cells(r, 2) = rst!ExcelFileName
        'write 3rd field data to column C of current row
    objDataSheet.Cells(r, 3) = rst!ExcelSheetName
    rst.MoveNext
Loop

rst.Close
Set rst = nothing
db.Close
Set db = Nothing

'Clean up your xl objects here

+++++++++++++++++++++++++++++++++++++++++++++++++++++++++

I am trying to open an excel file and insert the data from a Table or
Query into a sheet and print.
I actually need to loop thru all the filtered records and do this
task.

Currently I am using this code to open the excel file and place the
data into Cell B2 then print.  This part works but I need to integrate
the table/query loop to print all the records.

I have a table called tbl_data with 3 fields.
Data, ExcelFileName, ExcelSheetName

Private Sub PrintToExcel()
    Dim objXLApp As Excel.Application
    Dim objXLBook As Excel.Workbook
    Dim objDataSheet As Excel.Worksheet

    Set objXLBook = GetObject("C:\test.xls") ' Put File Name Here
    'Set objXLBook = GetObject(Application.CurrentProject.Path &
"\ExcelFile\FileName.xls")

    Set objXLApp = objXLBook.Parent
    Set objDataSheet = objXLBook.Worksheets("Data") 'Select Worksheet

 'Need to loop thru records and place field contents here.
    objDataSheet.Cells(1, 2).Value = "data" ' test with string works.

    objDataSheet.PrintOut Copies:=1, Collate:=True
    objXLBook.Close savechanges:=False 'close without changes
    Set objDataSheet = Nothing
    Set objXLBook = Nothing
    Set objXLApp = Nothing

End Sub- Hide quoted text -

- Show quoted text -

Thanks Jon
The 2nd approach is what I was looking for because I have to put
Access data in templates in Excel in different locations.
I have different worksheets that have a named range in different
locations.
For instance I am putting the Field CLIENT into a worksheet in Excel
with a named range CLIENT. the cell address is different for each
worksheet template within the workbook.

objDataSheet.Cells(1, 2).Value = rs![Client] ' This works but I
have many worksheets and it is not practical to list the cells for
each worksheet.
So I tried this:
objDataSheet.Range(Client) = rs![Client] ' This does not work
and
objDataSheet.Range("Client") = rs![Client] ' This does not work.

Can I reference a Named Range in Excel?
Steve

 

[JonWayn]

Yes you can reference a named range thru automation and you did it the right
way - objDataSheet.Range("RangeName"). However, you cant assign values to a
range unless the range contains only 1 cell. You will still have to access
each cell of each row of your range and you dont have to refer to each cell
by address, you can enter a nested loop and use the loop variables as cell
indexes:

Dim db As Database, rst As Recordset
Dim rr%, r%, c%

Set db = CurrentDb
Set rst = db.OpenRecordset("tblClients")
rst.MoveLast
rr = rst.RecordCount
rst.MoveFirst

For r = 1 To rr ' Ensure that the Clients range has as many or more rows
than rst
Set CurRow = objDataSheet.Range("Clients").Rows(r)

For c = 1 To rst.Fields.Count 'and tht the rng is wide enough for each
field
CurRow.Cells(c) = rst.Fields(c - 1).Value 'Field indexes a 0-based
I think
Next c

rst.MoveNext
If rst.EOF Then Exit For
Next r

Ensuring that the excel Clients range has at least as many columns as the
table has fields and at least as many rows as the table has records is on you.


======================================================

You know, if you really like things the easy way, then instead of looping
thru all records, you could just do :

docmd.TransferSpreadsheet acExport,acSpreadsheetTypeExcel9, "TableName",
"C:\MyPath\SomeSpreadsheet.xls",true

That single statement above creates an xls file with the given name and adds
all the records in the table to it. The only downside to that method is that
you cant tell it to place the records in B1 - it will force the first record
in row 1. But since you are using xl automation you can easily get that
shifted afterwards.

If, on the other hand, you like to experiment with more technical methods,
nothing wrong with that by the way, then you can do:

Dim objXLApp As Excel.Application
Dim objXLBook As Excel.Workbook
Dim objDataSheet As Excel.Worksheet
Dim db as Database, rst as Recordset
Dim r%, c%, rr%

Set db = CurrentDb
Set rst = db.OpenRecordset("TableName")
If Not rst.BOF And Not rst.EOF Then rst.MoveFirst
Set objXLBook = GetObject("C:\test.xls")
Set objXLApp = objXLBook.Parent
Set objDataSheet = objXLBook.Worksheets("Data") 'Select Worksheet
r=1 'Row index; initialize to 1 to skip
row 1 in loop below

Do Until rst.EOF
r = r + 1
'increment row index

'write first field data to column A of current row
objDataSheet.Cells(r, 1) = rst![Data]
'write 2nd field data to column B of current row
objDataSheet.Cells(r, 2) = rst!ExcelFileName
'write 3rd field data to column C of current row
objDataSheet.Cells(r, 3) = rst!ExcelSheetName
rst.MoveNext
Loop

rst.Close
Set rst = nothing
db.Close
Set db = Nothing

'Clean up your xl objects here

+++++++++++++++++++++++++++++++++++++++++++++++++++++++++

I am trying to open an excel file and insert the data from a Table or
Query into a sheet and print.
I actually need to loop thru all the filtered records and do this
task.

Currently I am using this code to open the excel file and place the
data into Cell B2 then print. This part works but I need to integrate
the table/query loop to print all the records.

I have a table called tbl_data with 3 fields.
Data, ExcelFileName, ExcelSheetName

Private Sub PrintToExcel()
Dim objXLApp As Excel.Application
Dim objXLBook As Excel.Workbook
Dim objDataSheet As Excel.Worksheet

Set objXLBook = GetObject("C:\test.xls") ' Put File Name Here
'Set objXLBook = GetObject(Application.CurrentProject.Path &
"\ExcelFile\FileName.xls")

Set objXLApp = objXLBook.Parent
Set objDataSheet = objXLBook.Worksheets("Data") 'Select Worksheet

'Need to loop thru records and place field contents here.
objDataSheet.Cells(1, 2).Value = "data" ' test with string works.

objDataSheet.PrintOut Copies:=1, Collate:=True
objXLBook.Close savechanges:=False 'close without changes
Set objDataSheet = Nothing
Set objXLBook = Nothing
Set objXLApp = Nothing

End Sub- Hide quoted text -

- Show quoted text -

Thanks Jon
The 2nd approach is what I was looking for because I have to put
Access data in templates in Excel in different locations.
I have different worksheets that have a named range in different
locations.
For instance I am putting the Field CLIENT into a worksheet in Excel
with a named range CLIENT. the cell address is different for each
worksheet template within the workbook.

objDataSheet.Cells(1, 2).Value = rs![Client] ' This works but I
have many worksheets and it is not practical to list the cells for
each worksheet.
So I tried this:
objDataSheet.Range(Client) = rs![Client] ' This does not work
and
objDataSheet.Range("Client") = rs![Client] ' This does not work.

Can I reference a Named Range in Excel?
Steve

 

[JonWayn]

Plus, since it seems that you excel range contains a single cell only, the
correct way to add data to the range is objDataSheet.Range("Client").Cells(1)
= "data". I havent tried objDataSheet.Range("Client") = "data", and frankly,
I dont know if it would work

You know, if you really like things the easy way, then instead of looping
thru all records, you could just do :

docmd.TransferSpreadsheet acExport,acSpreadsheetTypeExcel9, "TableName",
"C:\MyPath\SomeSpreadsheet.xls",true

That single statement above creates an xls file with the given name and adds
all the records in the table to it. The only downside to that method is that
you cant tell it to place the records in B1 - it will force the first record
in row 1. But since you are using xl automation you can easily get that
shifted afterwards.

If, on the other hand, you like to experiment with more technical methods,
nothing wrong with that by the way, then you can do:

Dim objXLApp As Excel.Application
Dim objXLBook As Excel.Workbook
Dim objDataSheet As Excel.Worksheet
Dim db as Database, rst as Recordset
Dim r%, c%, rr%

Set db = CurrentDb
Set rst = db.OpenRecordset("TableName")
If Not rst.BOF And Not rst.EOF Then rst.MoveFirst
Set objXLBook = GetObject("C:\test.xls")
Set objXLApp = objXLBook.Parent
Set objDataSheet = objXLBook.Worksheets("Data") 'Select Worksheet
r=1 'Row index; initialize to 1 to skip
row 1 in loop below

Do Until rst.EOF
r = r + 1
'increment row index

'write first field data to column A of current row
objDataSheet.Cells(r, 1) = rst![Data]
'write 2nd field data to column B of current row
objDataSheet.Cells(r, 2) = rst!ExcelFileName
'write 3rd field data to column C of current row
objDataSheet.Cells(r, 3) = rst!ExcelSheetName
rst.MoveNext
Loop

rst.Close
Set rst = nothing
db.Close
Set db = Nothing

'Clean up your xl objects here

+++++++++++++++++++++++++++++++++++++++++++++++++++++++++

I am trying to open an excel file and insert the data from a Table or
Query into a sheet and print.
I actually need to loop thru all the filtered records and do this
task.

Currently I am using this code to open the excel file and place the
data into Cell B2 then print. This part works but I need to integrate
the table/query loop to print all the records.

I have a table called tbl_data with 3 fields.
Data, ExcelFileName, ExcelSheetName

Private Sub PrintToExcel()
Dim objXLApp As Excel.Application
Dim objXLBook As Excel.Workbook
Dim objDataSheet As Excel.Worksheet

Set objXLBook = GetObject("C:\test.xls") ' Put File Name Here
'Set objXLBook = GetObject(Application.CurrentProject.Path &
"\ExcelFile\FileName.xls")

Set objXLApp = objXLBook.Parent
Set objDataSheet = objXLBook.Worksheets("Data") 'Select Worksheet

'Need to loop thru records and place field contents here.
objDataSheet.Cells(1, 2).Value = "data" ' test with string works.

objDataSheet.PrintOut Copies:=1, Collate:=True
objXLBook.Close savechanges:=False 'close without changes
Set objDataSheet = Nothing
Set objXLBook = Nothing
Set objXLApp = Nothing

End Sub- Hide quoted text -

- Show quoted text -

Thanks Jon
The 2nd approach is what I was looking for because I have to put
Access data in templates in Excel in different locations.
I have different worksheets that have a named range in different
locations.
For instance I am putting the Field CLIENT into a worksheet in Excel
with a named range CLIENT. the cell address is different for each
worksheet template within the workbook.

objDataSheet.Cells(1, 2).Value = rs![Client] ' This works but I
have many worksheets and it is not practical to list the cells for
each worksheet.
So I tried this:
objDataSheet.Range(Client) = rs![Client] ' This does not work
and
objDataSheet.Range("Client") = rs![Client] ' This does not work.

Can I reference a Named Range in Excel?
Steve