Transferspreadsheet

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Transferspreadsheet when exporting to Excel doesn't really seem to be a good
function to use when exporting to Excel.
1. I am using a template with formatting, first sheet gets bypassed beacuae
a new sheet gets created.
2. you never know if the data even got to the spreadsheet,unless you close
out of the database,got to where the file resides an open it.
3. IF you try to create a new spreadsheet instead of a template so that the
user can save the file while still in Access you have to create a function
that is half a page long or longer to use a Save As dialog.
4.Giving desinating a sheet for the data to go to doesn't wok for a range as
was said in this forum at least I didn't get it to work.

So,I guess I just don't understand the purpose,unless someone can tell me
why it is better than just creating a procedure in vba .

I have benn trying to get this to work for a week,
 
Hi Cyndy,

TransferSpreadsheet certainly has its limitations. If you need to work
with a particular template or export to a specific range of cells in an
existing sheet it's better to work another way. The alternatives
basically are

1) VBA automation, either using Excel's Range.CopyFromRecordset to
(effectively) paste the contents of a recordset into a sheet starting at
a particular cell, or manipulating the values of individual cells. (You
can manipulate their formats at the same time.)

2) Jet queries to poke data into worksheet cells or ranges, e.g.:

NSERT INTO [Excel
8.0;HDR=Yes;Database=C:\Temp\T97\MyWorkbook.xls;].[MySheet]
SELECT * FROM MyTable
;

Single cell:
UPDATE
[Excel 8.0;HDR=NO;Database=C:\MyWorkbook.xls;].[MyWorksheet$B4:B4]
SET
F1=55
;
 
Thanks John,
I did try the following code,but not sure what to do with the
qdf.parameters. I diddn't think was going to have to use them in the code
because they are in the query. So now I can't get the code to run.

Dim db As DAO.Database
Dim qdf As DAO.QueryDef
Dim rpt As Report
Dim rst As DAO.Recordset
Dim objExcel As Excel.Application
Dim wkb As Workbook
Dim wks As Worksheet
Dim s As Object
Dim i As Integer
Dim j As Integer
Dim intMaxCol As Integer
Dim intMaxRow As Integer


Set db = CurrentDb()
Set qdf = db.QueryDefs("qryCIO")
qdf.Parameters ([Forms]![frmCIOC]![Open] Or [Forms]![frmCIOC]![Closed])
qdf.Parameters ([Forms]![frmCIOC]![Closed] Or [Forms]![frmCIOC]![Open])
' qdf.Parameters (>=[Forms]![frmCIOC]![StartDate] And
<=[Forms]![frmCIOC]![EndDate])

Set rst = qdf.OpenRecordset

intMaxCol = rst.Fields.Count
If rst.RecordCount > 0 Then
rst.MoveLast: rst.MoveFirst
intMaxRow = rst.RecordCount
Set objExcel = New Excel.Application
With objExcel
.Visible = True
Set wkb = .Workbooks.Add
Set wks = wkb.Worksheets(1)
With wks
.Range(.Cells(2, 1), .Cells(intMaxRow, _
intMaxCol)).CopyFromRecordset rst
End With
End With
End If

Set objExcel = Nothing
Set wkb = Nothing
Set wks = Nothing

End Sub





John Nurick said:
Hi Cyndy,

TransferSpreadsheet certainly has its limitations. If you need to work
with a particular template or export to a specific range of cells in an
existing sheet it's better to work another way. The alternatives
basically are

1) VBA automation, either using Excel's Range.CopyFromRecordset to
(effectively) paste the contents of a recordset into a sheet starting at
a particular cell, or manipulating the values of individual cells. (You
can manipulate their formats at the same time.)

2) Jet queries to poke data into worksheet cells or ranges, e.g.:

NSERT INTO [Excel
8.0;HDR=Yes;Database=C:\Temp\T97\MyWorkbook.xls;].[MySheet]
SELECT * FROM MyTable
;

Single cell:
UPDATE
[Excel 8.0;HDR=NO;Database=C:\MyWorkbook.xls;].[MyWorksheet$B4:B4]
SET
F1=55
;



Transferspreadsheet when exporting to Excel doesn't really seem to be a good
function to use when exporting to Excel.
1. I am using a template with formatting, first sheet gets bypassed beacuae
a new sheet gets created.
2. you never know if the data even got to the spreadsheet,unless you close
out of the database,got to where the file resides an open it.
3. IF you try to create a new spreadsheet instead of a template so that the
user can save the file while still in Access you have to create a function
that is half a page long or longer to use a Save As dialog.
4.Giving desinating a sheet for the data to go to doesn't wok for a range as
was said in this forum at least I didn't get it to work.

So,I guess I just don't understand the purpose,unless someone can tell me
why it is better than just creating a procedure in vba .

I have benn trying to get this to work for a week,
Click to expand...
Click to expand...
 
The syntax for setting the parameters should probably be something like
this. You need to specify each parameter by name (or index in the
QueryDef's Parameters collection) and set its Value to the value of the
corresponding control on the form.

...
Set qdf = db.QueryDefs("My Query")
With qdf
.Parameters("XXX").Value = Forms("MyForm").Controls("Foo").Value
OR THIS
.Parameters(1).Value = [Forms]![MyForm]![Foo]

End With

The name of the parameter (represented by XXX above) is whatever you
typed into the query, e.g.
Please enter the ID
or
[Forms]![MyForm]![Foo]


Thanks John,
I did try the following code,but not sure what to do with the
qdf.parameters. I diddn't think was going to have to use them in the code
because they are in the query. So now I can't get the code to run.

Dim db As DAO.Database
Dim qdf As DAO.QueryDef
Dim rpt As Report
Dim rst As DAO.Recordset
Dim objExcel As Excel.Application
Dim wkb As Workbook
Dim wks As Worksheet
Dim s As Object
Dim i As Integer
Dim j As Integer
Dim intMaxCol As Integer
Dim intMaxRow As Integer


Set db = CurrentDb()
Set qdf = db.QueryDefs("qryCIO")
qdf.Parameters ([Forms]![frmCIOC]![Open] Or [Forms]![frmCIOC]![Closed])
qdf.Parameters ([Forms]![frmCIOC]![Closed] Or [Forms]![frmCIOC]![Open])
' qdf.Parameters (>=[Forms]![frmCIOC]![StartDate] And
<=[Forms]![frmCIOC]![EndDate])

Set rst = qdf.OpenRecordset

intMaxCol = rst.Fields.Count
If rst.RecordCount > 0 Then
rst.MoveLast: rst.MoveFirst
intMaxRow = rst.RecordCount
Set objExcel = New Excel.Application
With objExcel
.Visible = True
Set wkb = .Workbooks.Add
Set wks = wkb.Worksheets(1)
With wks
.Range(.Cells(2, 1), .Cells(intMaxRow, _
intMaxCol)).CopyFromRecordset rst
End With
End With
End If

Set objExcel = Nothing
Set wkb = Nothing
Set wks = Nothing

End Sub





John Nurick said:
Hi Cyndy,

TransferSpreadsheet certainly has its limitations. If you need to work
with a particular template or export to a specific range of cells in an
existing sheet it's better to work another way. The alternatives
basically are

1) VBA automation, either using Excel's Range.CopyFromRecordset to
(effectively) paste the contents of a recordset into a sheet starting at
a particular cell, or manipulating the values of individual cells. (You
can manipulate their formats at the same time.)

2) Jet queries to poke data into worksheet cells or ranges, e.g.:

NSERT INTO [Excel
8.0;HDR=Yes;Database=C:\Temp\T97\MyWorkbook.xls;].[MySheet]
SELECT * FROM MyTable
;

Single cell:
UPDATE
[Excel 8.0;HDR=NO;Database=C:\MyWorkbook.xls;].[MyWorksheet$B4:B4]
SET
F1=55
;



Transferspreadsheet when exporting to Excel doesn't really seem to be a good
function to use when exporting to Excel.
1. I am using a template with formatting, first sheet gets bypassed beacuae
a new sheet gets created.
2. you never know if the data even got to the spreadsheet,unless you close
out of the database,got to where the file resides an open it.
3. IF you try to create a new spreadsheet instead of a template so that the
user can save the file while still in Access you have to create a function
that is half a page long or longer to use a Save As dialog.
4.Giving desinating a sheet for the data to go to doesn't wok for a range as
was said in this forum at least I didn't get it to work.

So,I guess I just don't understand the purpose,unless someone can tell me
why it is better than just creating a procedure in vba .

I have benn trying to get this to work for a week,
Click to expand...
Click to expand...
Click to expand...
 
John,
I am getting a runtime error 3265 Item not found in the collection.

Parameters for query:
[Forms]![frmCIOC]![Open]

[Forms]![frmCIOC]![Closed] >=[Forms]![frmCIOC]![StartDate] And
<=[Forms]![frmCIOC]![EndDate]

Revised code
Private Sub cmdStatus_Click()

Dim db As DAO.Database
Dim qdf As DAO.QueryDef
Dim rpt As Report
Dim rst As DAO.Recordset
Dim objExcel As Excel.Application
Dim wkb As Workbook
Dim wks As Worksheet
Dim s As Object
Dim i As Integer
Dim j As Integer
Dim intMaxCol As Integer
Dim intMaxRow As Integer


Set db = CurrentDb()
Set qdf = db.QueryDefs("qryCIO")
With qdf
.Parameters("Open").Value = Forms("frmCIOC").Controls("Open").Value
.Parameters("Closed").Value = Forms("frmCIOC").Controls("Closed").Value
And .Parameters("StartDate").Value =
Forms("frmCIOC").Controls("StartDate").Value And .Parameters("EndDate").Value
= Forms("frmCIOC").Controls("EndDate").Value

' .Parameters("StartDate").Value =
[Forms]![frmCIOC].Controls("StartDate").Value And
..Parameters("EndDate").Value = [Forms]![frmCIOC].Controls("EndDate").Value
End With
Set rst = qdf.OpenRecordset

With rst
intMaxCol = rst.Fields.Count
If rst.RecordCount > 0 Then
rst.MoveLast: rst.MoveFirst
intMaxRow = rst.RecordCount
Set objExcel = New Excel.Application
With objExcel
.Visible = True
Set wkb = .Workbooks.Add
Set wks = wkb.Worksheets(1)
With wks
.Range(.Cells(2, 1), .Cells(intMaxRow, _
intMaxCol)).CopyFromRecordset rst
End With
End With
End If

End With
Set objExcel = Nothing
Set wkb = Nothing
Set wks = Nothing
'Set s = Nothing

End Sub












John Nurick said:
The syntax for setting the parameters should probably be something like
this. You need to specify each parameter by name (or index in the
QueryDef's Parameters collection) and set its Value to the value of the
corresponding control on the form.

...
Set qdf = db.QueryDefs("My Query")
With qdf
.Parameters("XXX").Value = Forms("MyForm").Controls("Foo").Value
OR THIS
.Parameters(1).Value = [Forms]![MyForm]![Foo]

End With

The name of the parameter (represented by XXX above) is whatever you
typed into the query, e.g.
Please enter the ID
or
[Forms]![MyForm]![Foo]


Thanks John,
I did try the following code,but not sure what to do with the
qdf.parameters. I diddn't think was going to have to use them in the code
because they are in the query. So now I can't get the code to run.

Dim db As DAO.Database
Dim qdf As DAO.QueryDef
Dim rpt As Report
Dim rst As DAO.Recordset
Dim objExcel As Excel.Application
Dim wkb As Workbook
Dim wks As Worksheet
Dim s As Object
Dim i As Integer
Dim j As Integer
Dim intMaxCol As Integer
Dim intMaxRow As Integer


Set db = CurrentDb()
Set qdf = db.QueryDefs("qryCIO")
qdf.Parameters ([Forms]![frmCIOC]![Open] Or [Forms]![frmCIOC]![Closed])
qdf.Parameters ([Forms]![frmCIOC]![Closed] Or [Forms]![frmCIOC]![Open])
' qdf.Parameters (>=[Forms]![frmCIOC]![StartDate] And
<=[Forms]![frmCIOC]![EndDate])

Set rst = qdf.OpenRecordset

intMaxCol = rst.Fields.Count
If rst.RecordCount > 0 Then
rst.MoveLast: rst.MoveFirst
intMaxRow = rst.RecordCount
Set objExcel = New Excel.Application
With objExcel
.Visible = True
Set wkb = .Workbooks.Add
Set wks = wkb.Worksheets(1)
With wks
.Range(.Cells(2, 1), .Cells(intMaxRow, _
intMaxCol)).CopyFromRecordset rst
End With
End With
End If

Set objExcel = Nothing
Set wkb = Nothing
Set wks = Nothing

End Sub





John Nurick said:
Hi Cyndy,

TransferSpreadsheet certainly has its limitations. If you need to work
with a particular template or export to a specific range of cells in an
existing sheet it's better to work another way. The alternatives
basically are

1) VBA automation, either using Excel's Range.CopyFromRecordset to
(effectively) paste the contents of a recordset into a sheet starting at
a particular cell, or manipulating the values of individual cells. (You
can manipulate their formats at the same time.)

2) Jet queries to poke data into worksheet cells or ranges, e.g.:

NSERT INTO [Excel
8.0;HDR=Yes;Database=C:\Temp\T97\MyWorkbook.xls;].[MySheet]
SELECT * FROM MyTable
;

Single cell:
UPDATE
[Excel 8.0;HDR=NO;Database=C:\MyWorkbook.xls;].[MyWorksheet$B4:B4]
SET
F1=55
;



On Wed, 14 Sep 2005 21:52:02 -0700, CyndyG

Transferspreadsheet when exporting to Excel doesn't really seem to be a good
function to use when exporting to Excel.
1. I am using a template with formatting, first sheet gets bypassed beacuae
a new sheet gets created.
2. you never know if the data even got to the spreadsheet,unless you close
out of the database,got to where the file resides an open it.
3. IF you try to create a new spreadsheet instead of a template so that the
user can save the file while still in Access you have to create a function
that is half a page long or longer to use a Save As dialog.
4.Giving desinating a sheet for the data to go to doesn't wok for a range as
was said in this forum at least I didn't get it to work.

So,I guess I just don't understand the purpose,unless someone can tell me
why it is better than just creating a procedure in vba .

I have benn trying to get this to work for a week,
Click to expand...
Click to expand...
Click to expand...
 
John,
I am getting a runtime error 3265 Item not found in the collection.
Click to expand...

That's not much use unless you also say which line of code is triggering
the error and what values the relevant variables have at that point. But
it probably means that one or more of the parameter names is different
from what you think. You say the names are
Open
Closed
StartDate
EndDate
but if you open the query in design view are they
[Open]
[Closed]
etc. or
[Forms]![frmCIOC]![Open]
etc.? The parameter names you use in your code must match whatever you
have used in the query (or the query must match the names in the code).
Parameters for query:
[Forms]![frmCIOC]![Open]
Click to expand...

This expression seems wrong to me:
[Forms]![frmCIOC]![Closed] >=[Forms]![frmCIOC]![StartDate] And
<=[Forms]![frmCIOC]![EndDate]
Click to expand...
It looks as if it should be more like
([...]![Closed] >= [...]![StartDate]) _
And ([...]![Closed] <= [...]![EndDate])


Revised code
Private Sub cmdStatus_Click()

Dim db As DAO.Database
Dim qdf As DAO.QueryDef
Dim rpt As Report
Dim rst As DAO.Recordset
Dim objExcel As Excel.Application
Dim wkb As Workbook
Dim wks As Worksheet
Dim s As Object
Dim i As Integer
Dim j As Integer
Dim intMaxCol As Integer
Dim intMaxRow As Integer


Set db = CurrentDb()
Set qdf = db.QueryDefs("qryCIO")
With qdf
.Parameters("Open").Value = Forms("frmCIOC").Controls("Open").Value
Click to expand...

This statement looks really confused. I'm surprised it compiles and
suspect that whatever it does it's not what you expect.
.Parameters("Closed").Value = Forms("frmCIOC").Controls("Closed").Value
And .Parameters("StartDate").Value =
Forms("frmCIOC").Controls("StartDate").Value And .Parameters("EndDate").Value
= Forms("frmCIOC").Controls("EndDate").Value

' .Parameters("StartDate").Value =
[Forms]![frmCIOC].Controls("StartDate").Value And
.Parameters("EndDate").Value = [Forms]![frmCIOC].Controls("EndDate").Value
End With
Set rst = qdf.OpenRecordset

With rst
intMaxCol = rst.Fields.Count
Click to expand...

As far as I know you don't need to define a range that matches the
dimensions of the recordset, but can just specify one cell.
CopyFromRecordset then pastes the data into the sheet starting at that
cell.
If rst.RecordCount > 0 Then
rst.MoveLast: rst.MoveFirst
intMaxRow = rst.RecordCount
Set objExcel = New Excel.Application
With objExcel
.Visible = True
Set wkb = .Workbooks.Add
Set wks = wkb.Worksheets(1)
With wks
.Range(.Cells(2, 1), .Cells(intMaxRow, _
intMaxCol)).CopyFromRecordset rst
End With
End With
End If

End With
Set objExcel = Nothing
Set wkb = Nothing
Set wks = Nothing
'Set s = Nothing

End Sub












John Nurick said:
The syntax for setting the parameters should probably be something like
this. You need to specify each parameter by name (or index in the
QueryDef's Parameters collection) and set its Value to the value of the
corresponding control on the form.

...
Set qdf = db.QueryDefs("My Query")
With qdf
.Parameters("XXX").Value = Forms("MyForm").Controls("Foo").Value
OR THIS
.Parameters(1).Value = [Forms]![MyForm]![Foo]

End With

The name of the parameter (represented by XXX above) is whatever you
typed into the query, e.g.
Please enter the ID
or
[Forms]![MyForm]![Foo]


Thanks John,
I did try the following code,but not sure what to do with the
qdf.parameters. I diddn't think was going to have to use them in the code
because they are in the query. So now I can't get the code to run.

Dim db As DAO.Database
Dim qdf As DAO.QueryDef
Dim rpt As Report
Dim rst As DAO.Recordset
Dim objExcel As Excel.Application
Dim wkb As Workbook
Dim wks As Worksheet
Dim s As Object
Dim i As Integer
Dim j As Integer
Dim intMaxCol As Integer
Dim intMaxRow As Integer


Set db = CurrentDb()
Set qdf = db.QueryDefs("qryCIO")
qdf.Parameters ([Forms]![frmCIOC]![Open] Or [Forms]![frmCIOC]![Closed])
qdf.Parameters ([Forms]![frmCIOC]![Closed] Or [Forms]![frmCIOC]![Open])
' qdf.Parameters (>=[Forms]![frmCIOC]![StartDate] And
<=[Forms]![frmCIOC]![EndDate])

Set rst = qdf.OpenRecordset

intMaxCol = rst.Fields.Count
If rst.RecordCount > 0 Then
rst.MoveLast: rst.MoveFirst
intMaxRow = rst.RecordCount
Set objExcel = New Excel.Application
With objExcel
.Visible = True
Set wkb = .Workbooks.Add
Set wks = wkb.Worksheets(1)
With wks
.Range(.Cells(2, 1), .Cells(intMaxRow, _
intMaxCol)).CopyFromRecordset rst
End With
End With
End If

Set objExcel = Nothing
Set wkb = Nothing
Set wks = Nothing

End Sub





:

Hi Cyndy,

TransferSpreadsheet certainly has its limitations. If you need to work
with a particular template or export to a specific range of cells in an
existing sheet it's better to work another way. The alternatives
basically are

1) VBA automation, either using Excel's Range.CopyFromRecordset to
(effectively) paste the contents of a recordset into a sheet starting at
a particular cell, or manipulating the values of individual cells. (You
can manipulate their formats at the same time.)

2) Jet queries to poke data into worksheet cells or ranges, e.g.:

NSERT INTO [Excel
8.0;HDR=Yes;Database=C:\Temp\T97\MyWorkbook.xls;].[MySheet]
SELECT * FROM MyTable
;

Single cell:
UPDATE
[Excel 8.0;HDR=NO;Database=C:\MyWorkbook.xls;].[MyWorksheet$B4:B4]
SET
F1=55
;



On Wed, 14 Sep 2005 21:52:02 -0700, CyndyG

Transferspreadsheet when exporting to Excel doesn't really seem to be a good
function to use when exporting to Excel.
1. I am using a template with formatting, first sheet gets bypassed beacuae
a new sheet gets created.
2. you never know if the data even got to the spreadsheet,unless you close
out of the database,got to where the file resides an open it.
3. IF you try to create a new spreadsheet instead of a template so that the
user can save the file while still in Access you have to create a function
that is half a page long or longer to use a Save As dialog.
4.Giving desinating a sheet for the data to go to doesn't wok for a range as
was said in this forum at least I didn't get it to work.

So,I guess I just don't understand the purpose,unless someone can tell me
why it is better than just creating a procedure in vba .

I have benn trying to get this to work for a week,
Click to expand...
Click to expand...
Click to expand...
 
Thank You.

John Nurick said:
John,
I am getting a runtime error 3265 Item not found in the collection.
Click to expand...

That's not much use unless you also say which line of code is triggering
the error and what values the relevant variables have at that point. But
it probably means that one or more of the parameter names is different
from what you think. You say the names are
Open
Closed
StartDate
EndDate
but if you open the query in design view are they
[Open]
[Closed]
etc. or
[Forms]![frmCIOC]![Open]
etc.? The parameter names you use in your code must match whatever you
have used in the query (or the query must match the names in the code).
Parameters for query:
[Forms]![frmCIOC]![Open]
Click to expand...

This expression seems wrong to me:
[Forms]![frmCIOC]![Closed] >=[Forms]![frmCIOC]![StartDate] And
<=[Forms]![frmCIOC]![EndDate]
Click to expand...
It looks as if it should be more like
([...]![Closed] >= [...]![StartDate]) _
And ([...]![Closed] <= [...]![EndDate])


Revised code
Private Sub cmdStatus_Click()

Dim db As DAO.Database
Dim qdf As DAO.QueryDef
Dim rpt As Report
Dim rst As DAO.Recordset
Dim objExcel As Excel.Application
Dim wkb As Workbook
Dim wks As Worksheet
Dim s As Object
Dim i As Integer
Dim j As Integer
Dim intMaxCol As Integer
Dim intMaxRow As Integer


Set db = CurrentDb()
Set qdf = db.QueryDefs("qryCIO")
With qdf
.Parameters("Open").Value = Forms("frmCIOC").Controls("Open").Value
Click to expand...

This statement looks really confused. I'm surprised it compiles and
suspect that whatever it does it's not what you expect.
.Parameters("Closed").Value = Forms("frmCIOC").Controls("Closed").Value
And .Parameters("StartDate").Value =
Forms("frmCIOC").Controls("StartDate").Value And .Parameters("EndDate").Value
= Forms("frmCIOC").Controls("EndDate").Value

' .Parameters("StartDate").Value =
[Forms]![frmCIOC].Controls("StartDate").Value And
.Parameters("EndDate").Value = [Forms]![frmCIOC].Controls("EndDate").Value
End With
Set rst = qdf.OpenRecordset

With rst
intMaxCol = rst.Fields.Count
Click to expand...

As far as I know you don't need to define a range that matches the
dimensions of the recordset, but can just specify one cell.
CopyFromRecordset then pastes the data into the sheet starting at that
cell.
If rst.RecordCount > 0 Then
rst.MoveLast: rst.MoveFirst
intMaxRow = rst.RecordCount
Set objExcel = New Excel.Application
With objExcel
.Visible = True
Set wkb = .Workbooks.Add
Set wks = wkb.Worksheets(1)
With wks
.Range(.Cells(2, 1), .Cells(intMaxRow, _
intMaxCol)).CopyFromRecordset rst
End With
End With
End If

End With
Set objExcel = Nothing
Set wkb = Nothing
Set wks = Nothing
'Set s = Nothing

End Sub












John Nurick said:
The syntax for setting the parameters should probably be something like
this. You need to specify each parameter by name (or index in the
QueryDef's Parameters collection) and set its Value to the value of the
corresponding control on the form.

...
Set qdf = db.QueryDefs("My Query")
With qdf
.Parameters("XXX").Value = Forms("MyForm").Controls("Foo").Value
OR THIS
.Parameters(1).Value = [Forms]![MyForm]![Foo]

End With

The name of the parameter (represented by XXX above) is whatever you
typed into the query, e.g.
Please enter the ID
or
[Forms]![MyForm]![Foo]


On Thu, 15 Sep 2005 01:55:03 -0700, CyndyG

Thanks John,
I did try the following code,but not sure what to do with the
qdf.parameters. I diddn't think was going to have to use them in the code
because they are in the query. So now I can't get the code to run.

Dim db As DAO.Database
Dim qdf As DAO.QueryDef
Dim rpt As Report
Dim rst As DAO.Recordset
Dim objExcel As Excel.Application
Dim wkb As Workbook
Dim wks As Worksheet
Dim s As Object
Dim i As Integer
Dim j As Integer
Dim intMaxCol As Integer
Dim intMaxRow As Integer


Set db = CurrentDb()
Set qdf = db.QueryDefs("qryCIO")
qdf.Parameters ([Forms]![frmCIOC]![Open] Or [Forms]![frmCIOC]![Closed])
qdf.Parameters ([Forms]![frmCIOC]![Closed] Or [Forms]![frmCIOC]![Open])
' qdf.Parameters (>=[Forms]![frmCIOC]![StartDate] And
<=[Forms]![frmCIOC]![EndDate])

Set rst = qdf.OpenRecordset

intMaxCol = rst.Fields.Count
If rst.RecordCount > 0 Then
rst.MoveLast: rst.MoveFirst
intMaxRow = rst.RecordCount
Set objExcel = New Excel.Application
With objExcel
.Visible = True
Set wkb = .Workbooks.Add
Set wks = wkb.Worksheets(1)
With wks
.Range(.Cells(2, 1), .Cells(intMaxRow, _
intMaxCol)).CopyFromRecordset rst
End With
End With
End If

Set objExcel = Nothing
Set wkb = Nothing
Set wks = Nothing

End Sub





:

Hi Cyndy,

TransferSpreadsheet certainly has its limitations. If you need to work
with a particular template or export to a specific range of cells in an
existing sheet it's better to work another way. The alternatives
basically are

1) VBA automation, either using Excel's Range.CopyFromRecordset to
(effectively) paste the contents of a recordset into a sheet starting at
a particular cell, or manipulating the values of individual cells. (You
can manipulate their formats at the same time.)

2) Jet queries to poke data into worksheet cells or ranges, e.g.:

NSERT INTO [Excel
8.0;HDR=Yes;Database=C:\Temp\T97\MyWorkbook.xls;].[MySheet]
SELECT * FROM MyTable
;

Single cell:
UPDATE
[Excel 8.0;HDR=NO;Database=C:\MyWorkbook.xls;].[MyWorksheet$B4:B4]
SET
F1=55
;



On Wed, 14 Sep 2005 21:52:02 -0700, CyndyG

Transferspreadsheet when exporting to Excel doesn't really seem to be a good
function to use when exporting to Excel.
1. I am using a template with formatting, first sheet gets bypassed beacuae
a new sheet gets created.
2. you never know if the data even got to the spreadsheet,unless you close
out of the database,got to where the file resides an open it.
3. IF you try to create a new spreadsheet instead of a template so that the
user can save the file while still in Access you have to create a function
that is half a page long or longer to use a Save As dialog.
4.Giving desinating a sheet for the data to go to doesn't wok for a range as
was said in this forum at least I didn't get it to work.

So,I guess I just don't understand the purpose,unless someone can tell me
why it is better than just creating a procedure in vba .

I have benn trying to get this to work for a week,
Click to expand...
Click to expand...
Click to expand...
 
Back
Top