|
|
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
|
|
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" wrote:
[Quoted Text] > 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
>
|
|
On Dec 15, 11:42 pm, JonWayn <JonW...[ at ]discussions.microsoft.com>
wrote:
[Quoted Text] > 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" wrote:
> > 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
|
|
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.
======================================================
"gtslabs" wrote:
[Quoted Text] > On Dec 15, 11:42 pm, JonWayn <JonW...[ at ]discussions.microsoft.com>
> wrote:
> > 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" wrote:
> > > 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
>
>
>
|
|
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
"gtslabs" wrote:
[Quoted Text] > On Dec 15, 11:42 pm, JonWayn <JonW...[ at ]discussions.microsoft.com>
> wrote:
> > 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" wrote:
> > > 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
>
>
>
|
|
|