Excel Export - Field Names in First Row of Spreadsheet

[Guest]

Hi

I have done a lot of "digging" through posts to try to find the answers I
need.....

I am setting up an export to excel. I have read in the Access help File,
TransferSpreadsheet Action, that it is not possible to export to Excel
without the Field Names appearing in the first row of the spreadsheet. (Is
there any way around this? Or is it possible to have the field names appear
in the "horizontal axis" at the top of the spreadsheet where A, B, C etc
appear as column names by default?)

I have read in posts that a way to put the field names in column headers in
the spreadsheet is to use a "make table query." How do I do this? I would
like to have a button that the user presses on an Access form to save just
one field from one table to an Excel file.
This is new to me... if you could post what is needed step by step
(including code) I'd be very grateful.

TIA
Rich

 

[Guest]

That is incorrect. Go into the VB Editor, look up TransferSpreadsheet in
Help. The fourth argument is Has Field Names. If you enter True in that
argument, the Access field names will be the first row of the spreadsheet.

 

[Ken Snell [MVP]]

Comments inline...

--

Ken Snell
<MS ACCESS MVP>

Hi

I have done a lot of "digging" through posts to try to find the answers I
need.....

I am setting up an export to excel. I have read in the Access help File,
TransferSpreadsheet Action, that it is not possible to export to Excel
without the Field Names appearing in the first row of the spreadsheet.

This is correct. TransferSpreadsheet will always export the field names when
exporting to an EXCEL file.

(Is there any way around this?

Not unless you use VBA code to open a recordset for the query you're
exporting, open an EXCEL file using Automation, and write the data directly
into the spreadsheet. Or use TransferSpreadsheet to export the query, then
use Automation to open the spreadsheet and delete the first row.

Or is it possible to have the field names appear
in the "horizontal axis" at the top of the spreadsheet where A, B, C etc
appear as column names by default?)

Nope, not to my knowledge.

I have read in posts that a way to put the field names in column headers
in
the spreadsheet is to use a "make table query." How do I do this? I
would
like to have a button that the user presses on an Access form to save just
one field from one table to an Excel file.

I'm not understanding this. A "make table query" has no advantages for what
you want to do. You can export a query directly, and you can "change" the
field names in the query using calculated fields in place of the original
fields. For example, if you wanted the "field name" of a field to be "User
Name" instead of "UserName" (the name in the table), you'd have a calculated
field that would look like this in the QBE design window:
User Name: [UserName]

This means you can use customized "field names" for the export, and these
customized names will appear in the first row of the spreadsheet.

 

[George Nicholson]

Klatuu:

(paraphrasing the Help entry) "HasFieldNames" argument only applies when
importing or linking. It tells VBA/Jet how to handle the incoming data. When
exporting, fieldnames are always included regardless of the value for this
argument.

HTH,

 

[Guest]

At the moment I am using TransferSpreadsheet to create the Excel file.
How do I open the Excel file and delete the first row of records using
automation? I've never done this before. The TransferSpreadsheet is carried
out in VBA called from a command button (not in a macro.) Or is the first
way you suggest a better way of doing this?

One other thought: I am including the time and date in the filename when
creating the Excel file, using the following code:
Dim sNow As String
sNow = Format(Date, " dd-mm-yyyy") & " " & Format(Time, "hhmm")
DoCmd.TransferSpreadsheet acExport, 8, "VRM for Export", "C:\My
Documents\DSCP ANPR" & sNow & ".xls", True

If I need to include code to open the Excel file (to delete the first row),
I will (presumably) need to include the filename again. So using the
following for the filename would work (right?):
"VRM for Export", "C:\My Documents\DSCP ANPR" & sNow & ".xls"
My only thought is whether this would include the time as it "now" is,
rather than as it was when the Excel file was saved.... i.e. if the realtime
has just changed to the next minute (in the milliseconds it has taken for the
code to be processed), would the variable sNow now show the updated minute?
If it would, then the filename would be cited above would now have changed!
If it would still show the "old" time, there would be no problem.
Can you advise me on this point as well?

Thank you
rich

Comments inline...

--

Ken Snell
<MS ACCESS MVP>

Hi

I have done a lot of "digging" through posts to try to find the answers I
need.....

I am setting up an export to excel. I have read in the Access help File,
TransferSpreadsheet Action, that it is not possible to export to Excel
without the Field Names appearing in the first row of the spreadsheet.

This is correct. TransferSpreadsheet will always export the field names when
exporting to an EXCEL file.

(Is there any way around this?

Not unless you use VBA code to open a recordset for the query you're
exporting, open an EXCEL file using Automation, and write the data directly
into the spreadsheet. Or use TransferSpreadsheet to export the query, then
use Automation to open the spreadsheet and delete the first row.

Or is it possible to have the field names appear
in the "horizontal axis" at the top of the spreadsheet where A, B, C etc
appear as column names by default?)

Nope, not to my knowledge.

I have read in posts that a way to put the field names in column headers
in
the spreadsheet is to use a "make table query." How do I do this? I
would
like to have a button that the user presses on an Access form to save just
one field from one table to an Excel file.

I'm not understanding this. A "make table query" has no advantages for what
you want to do. You can export a query directly, and you can "change" the
field names in the query using calculated fields in place of the original
fields. For example, if you wanted the "field name" of a field to be "User
Name" instead of "UserName" (the name in the table), you'd have a calculated
field that would look like this in the QBE design window:
User Name: [UserName]

This means you can use customized "field names" for the export, and these
customized names will appear in the first row of the spreadsheet.

This is new to me... if you could post what is needed step by step
(including code) I'd be very grateful.

TIA
Rich

 

[Ken Snell [MVP]]

Here is some sample code that will open the EXCEL file and delete the first
row from the specfic spreadsheet:

Dim sNow As String, sFile As String, sSheet As String
Dim xApp As Object, xWk As Object, xWs As Object
sNow = Format(Date, " dd-mm-yyyy") & " " & Format(Time, "hhmm")
sFile = "C:\My Documents\DSCP ANPR" & sNow & ".xls"
sSheet = "VRM for Export"
DoCmd.TransferSpreadsheet acExport, 8, sSheet, sFile, True
Set xApp = CreateObject("Excel.Application")
Set xWk = xApp.Workbooks.Open sFile
Set xWs = xWk.Worksheets(Left(sSheet, 31))
xWs.Range("A1").EntireRow.Delete
Set xWs = Nothing
xWk.Close True
Set xWk = Nothing
xApp.Quit
Set xApp = Nothing


--

Ken Snell
<MS ACCESS MVP>

At the moment I am using TransferSpreadsheet to create the Excel file.
How do I open the Excel file and delete the first row of records using
automation? I've never done this before. The TransferSpreadsheet is
carried
out in VBA called from a command button (not in a macro.) Or is the first
way you suggest a better way of doing this?

One other thought: I am including the time and date in the filename when
creating the Excel file, using the following code:
Dim sNow As String
sNow = Format(Date, " dd-mm-yyyy") & " " & Format(Time, "hhmm")
DoCmd.TransferSpreadsheet acExport, 8, "VRM for Export", "C:\My
Documents\DSCP ANPR" & sNow & ".xls", True

If I need to include code to open the Excel file (to delete the first
row),
I will (presumably) need to include the filename again. So using the
following for the filename would work (right?):
"VRM for Export", "C:\My Documents\DSCP ANPR" & sNow & ".xls"
My only thought is whether this would include the time as it "now" is,
rather than as it was when the Excel file was saved.... i.e. if the
realtime
has just changed to the next minute (in the milliseconds it has taken for
the
code to be processed), would the variable sNow now show the updated
minute?
If it would, then the filename would be cited above would now have
changed!
If it would still show the "old" time, there would be no problem.
Can you advise me on this point as well?

Thank you
rich

Comments inline...

--

Ken Snell
<MS ACCESS MVP>

Hi

I have done a lot of "digging" through posts to try to find the answers
I
need.....

I am setting up an export to excel. I have read in the Access help
File,
TransferSpreadsheet Action, that it is not possible to export to Excel
without the Field Names appearing in the first row of the spreadsheet.

This is correct. TransferSpreadsheet will always export the field names
when
exporting to an EXCEL file.

(Is there any way around this?

Not unless you use VBA code to open a recordset for the query you're
exporting, open an EXCEL file using Automation, and write the data
directly
into the spreadsheet. Or use TransferSpreadsheet to export the query,
then
use Automation to open the spreadsheet and delete the first row.

Or is it possible to have the field names appear
in the "horizontal axis" at the top of the spreadsheet where A, B, C
etc
appear as column names by default?)

Nope, not to my knowledge.

I have read in posts that a way to put the field names in column
headers
in
the spreadsheet is to use a "make table query." How do I do this? I
would
like to have a button that the user presses on an Access form to save
just
one field from one table to an Excel file.

I'm not understanding this. A "make table query" has no advantages for
what
you want to do. You can export a query directly, and you can "change" the
field names in the query using calculated fields in place of the original
fields. For example, if you wanted the "field name" of a field to be
"User
Name" instead of "UserName" (the name in the table), you'd have a
calculated
field that would look like this in the QBE design window:
User Name: [UserName]

This means you can use customized "field names" for the export, and these
customized names will appear in the first row of the spreadsheet.

This is new to me... if you could post what is needed step by step
(including code) I'd be very grateful.

TIA
Rich

 

[Guest]

Thanks for your post Ken. There is an error in here.
When I paste your code into VB the following line is highlighted in red:
Set xWk = xApp.Workbooks.Open sFile
I tried putting a dot after "Open" but then I get the following error:
error #449 Argument not optional
(I'm not sure to which line of the code this relates.)

Here is some sample code that will open the EXCEL file and delete the first
row from the specfic spreadsheet:

Dim sNow As String, sFile As String, sSheet As String
Dim xApp As Object, xWk As Object, xWs As Object
sNow = Format(Date, " dd-mm-yyyy") & " " & Format(Time, "hhmm")
sFile = "C:\My Documents\DSCP ANPR" & sNow & ".xls"
sSheet = "VRM for Export"
DoCmd.TransferSpreadsheet acExport, 8, sSheet, sFile, True
Set xApp = CreateObject("Excel.Application")
Set xWk = xApp.Workbooks.Open sFile
Set xWs = xWk.Worksheets(Left(sSheet, 31))
xWs.Range("A1").EntireRow.Delete
Set xWs = Nothing
xWk.Close True
Set xWk = Nothing
xApp.Quit
Set xApp = Nothing


--

Ken Snell
<MS ACCESS MVP>

At the moment I am using TransferSpreadsheet to create the Excel file.
How do I open the Excel file and delete the first row of records using
automation? I've never done this before. The TransferSpreadsheet is
carried
out in VBA called from a command button (not in a macro.) Or is the first
way you suggest a better way of doing this?

One other thought: I am including the time and date in the filename when
creating the Excel file, using the following code:
Dim sNow As String
sNow = Format(Date, " dd-mm-yyyy") & " " & Format(Time, "hhmm")
DoCmd.TransferSpreadsheet acExport, 8, "VRM for Export", "C:\My
Documents\DSCP ANPR" & sNow & ".xls", True

If I need to include code to open the Excel file (to delete the first
row),
I will (presumably) need to include the filename again. So using the
following for the filename would work (right?):
"VRM for Export", "C:\My Documents\DSCP ANPR" & sNow & ".xls"
My only thought is whether this would include the time as it "now" is,
rather than as it was when the Excel file was saved.... i.e. if the
realtime
has just changed to the next minute (in the milliseconds it has taken for
the
code to be processed), would the variable sNow now show the updated
minute?
If it would, then the filename would be cited above would now have
changed!
If it would still show the "old" time, there would be no problem.
Can you advise me on this point as well?

Thank you
rich

Comments inline...

--

Ken Snell
<MS ACCESS MVP>

Hi

I have done a lot of "digging" through posts to try to find the answers
I
need.....

I am setting up an export to excel. I have read in the Access help
File,
TransferSpreadsheet Action, that it is not possible to export to Excel
without the Field Names appearing in the first row of the spreadsheet.

This is correct. TransferSpreadsheet will always export the field names
when
exporting to an EXCEL file.


(Is there any way around this?

Not unless you use VBA code to open a recordset for the query you're
exporting, open an EXCEL file using Automation, and write the data
directly
into the spreadsheet. Or use TransferSpreadsheet to export the query,
then
use Automation to open the spreadsheet and delete the first row.


Or is it possible to have the field names appear
in the "horizontal axis" at the top of the spreadsheet where A, B, C
etc
appear as column names by default?)

Nope, not to my knowledge.


I have read in posts that a way to put the field names in column
headers
in
the spreadsheet is to use a "make table query." How do I do this? I
would
like to have a button that the user presses on an Access form to save
just
one field from one table to an Excel file.

I'm not understanding this. A "make table query" has no advantages for
what
you want to do. You can export a query directly, and you can "change" the
field names in the query using calculated fields in place of the original
fields. For example, if you wanted the "field name" of a field to be
"User
Name" instead of "UserName" (the name in the table), you'd have a
calculated
field that would look like this in the QBE design window:
User Name: [UserName]

This means you can use customized "field names" for the export, and these
customized names will appear in the first row of the spreadsheet.



This is new to me... if you could post what is needed step by step
(including code) I'd be very grateful.

TIA
Rich

 

[Ken Snell [MVP]]

Sorry.. typo on my part. Change that line to this:

Set xWk = xApp.Workbooks.Open(sFile)


--

Ken Snell
<MS ACCESS MVP>

Thanks for your post Ken. There is an error in here.
When I paste your code into VB the following line is highlighted in red:
Set xWk = xApp.Workbooks.Open sFile
I tried putting a dot after "Open" but then I get the following error:
error #449 Argument not optional
(I'm not sure to which line of the code this relates.)

Here is some sample code that will open the EXCEL file and delete the
first
row from the specfic spreadsheet:

Dim sNow As String, sFile As String, sSheet As String
Dim xApp As Object, xWk As Object, xWs As Object
sNow = Format(Date, " dd-mm-yyyy") & " " & Format(Time, "hhmm")
sFile = "C:\My Documents\DSCP ANPR" & sNow & ".xls"
sSheet = "VRM for Export"
DoCmd.TransferSpreadsheet acExport, 8, sSheet, sFile, True
Set xApp = CreateObject("Excel.Application")
Set xWk = xApp.Workbooks.Open sFile
Set xWs = xWk.Worksheets(Left(sSheet, 31))
xWs.Range("A1").EntireRow.Delete
Set xWs = Nothing
xWk.Close True
Set xWk = Nothing
xApp.Quit
Set xApp = Nothing


--

Ken Snell
<MS ACCESS MVP>

At the moment I am using TransferSpreadsheet to create the Excel file.
How do I open the Excel file and delete the first row of records using
automation? I've never done this before. The TransferSpreadsheet is
carried
out in VBA called from a command button (not in a macro.) Or is the
first
way you suggest a better way of doing this?

One other thought: I am including the time and date in the filename
when
creating the Excel file, using the following code:
Dim sNow As String
sNow = Format(Date, " dd-mm-yyyy") & " " & Format(Time, "hhmm")
DoCmd.TransferSpreadsheet acExport, 8, "VRM for Export", "C:\My
Documents\DSCP ANPR" & sNow & ".xls", True

If I need to include code to open the Excel file (to delete the first
row),
I will (presumably) need to include the filename again. So using the
following for the filename would work (right?):
"VRM for Export", "C:\My Documents\DSCP ANPR" & sNow & ".xls"
My only thought is whether this would include the time as it "now" is,
rather than as it was when the Excel file was saved.... i.e. if the
realtime
has just changed to the next minute (in the milliseconds it has taken
for
the
code to be processed), would the variable sNow now show the updated
minute?
If it would, then the filename would be cited above would now have
changed!
If it would still show the "old" time, there would be no problem.
Can you advise me on this point as well?

Thank you
rich

:

Comments inline...

--

Ken Snell
<MS ACCESS MVP>

Hi

I have done a lot of "digging" through posts to try to find the
answers
I
need.....

I am setting up an export to excel. I have read in the Access help
File,
TransferSpreadsheet Action, that it is not possible to export to
Excel
without the Field Names appearing in the first row of the
spreadsheet.

This is correct. TransferSpreadsheet will always export the field
names
when
exporting to an EXCEL file.


(Is there any way around this?

Not unless you use VBA code to open a recordset for the query you're
exporting, open an EXCEL file using Automation, and write the data
directly
into the spreadsheet. Or use TransferSpreadsheet to export the query,
then
use Automation to open the spreadsheet and delete the first row.


Or is it possible to have the field names appear
in the "horizontal axis" at the top of the spreadsheet where A, B, C
etc
appear as column names by default?)

Nope, not to my knowledge.


I have read in posts that a way to put the field names in column
headers
in
the spreadsheet is to use a "make table query." How do I do this?
I
would
like to have a button that the user presses on an Access form to
save
just
one field from one table to an Excel file.

I'm not understanding this. A "make table query" has no advantages for
what
you want to do. You can export a query directly, and you can "change"
the
field names in the query using calculated fields in place of the
original
fields. For example, if you wanted the "field name" of a field to be
"User
Name" instead of "UserName" (the name in the table), you'd have a
calculated
field that would look like this in the QBE design window:
User Name: [UserName]

This means you can use customized "field names" for the export, and
these
customized names will appear in the first row of the spreadsheet.



This is new to me... if you could post what is needed step by step
(including code) I'd be very grateful.

TIA
Rich

 

[Guest]

I've just done that... now I'm getting the following error:
error #9
subscript out of range

Sorry.. typo on my part. Change that line to this:

Set xWk = xApp.Workbooks.Open(sFile)


--

Ken Snell
<MS ACCESS MVP>

Thanks for your post Ken. There is an error in here.
When I paste your code into VB the following line is highlighted in red:
Set xWk = xApp.Workbooks.Open sFile
I tried putting a dot after "Open" but then I get the following error:
error #449 Argument not optional
(I'm not sure to which line of the code this relates.)

Here is some sample code that will open the EXCEL file and delete the
first
row from the specfic spreadsheet:

Dim sNow As String, sFile As String, sSheet As String
Dim xApp As Object, xWk As Object, xWs As Object
sNow = Format(Date, " dd-mm-yyyy") & " " & Format(Time, "hhmm")
sFile = "C:\My Documents\DSCP ANPR" & sNow & ".xls"
sSheet = "VRM for Export"
DoCmd.TransferSpreadsheet acExport, 8, sSheet, sFile, True
Set xApp = CreateObject("Excel.Application")
Set xWk = xApp.Workbooks.Open sFile
Set xWs = xWk.Worksheets(Left(sSheet, 31))
xWs.Range("A1").EntireRow.Delete
Set xWs = Nothing
xWk.Close True
Set xWk = Nothing
xApp.Quit
Set xApp = Nothing


--

Ken Snell
<MS ACCESS MVP>




At the moment I am using TransferSpreadsheet to create the Excel file.
How do I open the Excel file and delete the first row of records using
automation? I've never done this before. The TransferSpreadsheet is
carried
out in VBA called from a command button (not in a macro.) Or is the
first
way you suggest a better way of doing this?

One other thought: I am including the time and date in the filename
when
creating the Excel file, using the following code:
Dim sNow As String
sNow = Format(Date, " dd-mm-yyyy") & " " & Format(Time, "hhmm")
DoCmd.TransferSpreadsheet acExport, 8, "VRM for Export", "C:\My
Documents\DSCP ANPR" & sNow & ".xls", True

If I need to include code to open the Excel file (to delete the first
row),
I will (presumably) need to include the filename again. So using the
following for the filename would work (right?):
"VRM for Export", "C:\My Documents\DSCP ANPR" & sNow & ".xls"
My only thought is whether this would include the time as it "now" is,
rather than as it was when the Excel file was saved.... i.e. if the
realtime
has just changed to the next minute (in the milliseconds it has taken
for
the
code to be processed), would the variable sNow now show the updated
minute?
If it would, then the filename would be cited above would now have
changed!
If it would still show the "old" time, there would be no problem.
Can you advise me on this point as well?

Thank you
rich

:

Comments inline...

--

Ken Snell
<MS ACCESS MVP>

Hi

I have done a lot of "digging" through posts to try to find the
answers
I
need.....

I am setting up an export to excel. I have read in the Access help
File,
TransferSpreadsheet Action, that it is not possible to export to
Excel
without the Field Names appearing in the first row of the
spreadsheet.

This is correct. TransferSpreadsheet will always export the field
names
when
exporting to an EXCEL file.


(Is there any way around this?

Not unless you use VBA code to open a recordset for the query you're
exporting, open an EXCEL file using Automation, and write the data
directly
into the spreadsheet. Or use TransferSpreadsheet to export the query,
then
use Automation to open the spreadsheet and delete the first row.


Or is it possible to have the field names appear
in the "horizontal axis" at the top of the spreadsheet where A, B, C
etc
appear as column names by default?)

Nope, not to my knowledge.


I have read in posts that a way to put the field names in column
headers
in
the spreadsheet is to use a "make table query." How do I do this?
I
would
like to have a button that the user presses on an Access form to
save
just
one field from one table to an Excel file.

I'm not understanding this. A "make table query" has no advantages for
what
you want to do. You can export a query directly, and you can "change"
the
field names in the query using calculated fields in place of the
original
fields. For example, if you wanted the "field name" of a field to be
"User
Name" instead of "UserName" (the name in the table), you'd have a
calculated
field that would look like this in the QBE design window:
User Name: [UserName]

This means you can use customized "field names" for the export, and
these
customized names will appear in the first row of the spreadsheet.



This is new to me... if you could post what is needed step by step
(including code) I'd be very grateful.

TIA
Rich

 

[Ken Snell [MVP]]

It's possible that the file hasn't been fully saved yet when the attempt is
made to open it. Let's change the code by adding a DoEvents step to give
time for that to happen:

Dim sNow As String, sFile As String, sSheet As String
Dim xApp As Object, xWk As Object, xWs As Object
sNow = Format(Date, " dd-mm-yyyy") & " " & Format(Time, "hhmm")
sFile = "C:\My Documents\DSCP ANPR" & sNow & ".xls"
sSheet = "VRM for Export"
DoCmd.TransferSpreadsheet acExport, 8, sSheet, sFile, True
DoEvents
Set xApp = CreateObject("Excel.Application")
Set xWk = xApp.Workbooks.Open(sFile)
Set xWs = xWk.Worksheets(Left(sSheet, 31))
xWs.Range("A1").EntireRow.Delete
Set xWs = Nothing
xWk.Close True
Set xWk = Nothing
xApp.Quit
Set xApp = Nothing

--

Ken Snell
<MS ACCESS MVP>

I've just done that... now I'm getting the following error:
error #9
subscript out of range

Sorry.. typo on my part. Change that line to this:

Set xWk = xApp.Workbooks.Open(sFile)


--

Ken Snell
<MS ACCESS MVP>

Thanks for your post Ken. There is an error in here.
When I paste your code into VB the following line is highlighted in
red:
Set xWk = xApp.Workbooks.Open sFile
I tried putting a dot after "Open" but then I get the following error:
error #449 Argument not optional
(I'm not sure to which line of the code this relates.)



:

Here is some sample code that will open the EXCEL file and delete the
first
row from the specfic spreadsheet:

Dim sNow As String, sFile As String, sSheet As String
Dim xApp As Object, xWk As Object, xWs As Object
sNow = Format(Date, " dd-mm-yyyy") & " " & Format(Time, "hhmm")
sFile = "C:\My Documents\DSCP ANPR" & sNow & ".xls"
sSheet = "VRM for Export"
DoCmd.TransferSpreadsheet acExport, 8, sSheet, sFile, True
Set xApp = CreateObject("Excel.Application")
Set xWk = xApp.Workbooks.Open sFile
Set xWs = xWk.Worksheets(Left(sSheet, 31))
xWs.Range("A1").EntireRow.Delete
Set xWs = Nothing
xWk.Close True
Set xWk = Nothing
xApp.Quit
Set xApp = Nothing


--

Ken Snell
<MS ACCESS MVP>




At the moment I am using TransferSpreadsheet to create the Excel
file.
How do I open the Excel file and delete the first row of records
using
automation? I've never done this before. The TransferSpreadsheet
is
carried
out in VBA called from a command button (not in a macro.) Or is the
first
way you suggest a better way of doing this?

One other thought: I am including the time and date in the filename
when
creating the Excel file, using the following code:
Dim sNow As String
sNow = Format(Date, " dd-mm-yyyy") & " " & Format(Time, "hhmm")
DoCmd.TransferSpreadsheet acExport, 8, "VRM for Export", "C:\My
Documents\DSCP ANPR" & sNow & ".xls", True

If I need to include code to open the Excel file (to delete the
first
row),
I will (presumably) need to include the filename again. So using
the
following for the filename would work (right?):
"VRM for Export", "C:\My Documents\DSCP ANPR" & sNow & ".xls"
My only thought is whether this would include the time as it "now"
is,
rather than as it was when the Excel file was saved.... i.e. if the
realtime
has just changed to the next minute (in the milliseconds it has
taken
for
the
code to be processed), would the variable sNow now show the updated
minute?
If it would, then the filename would be cited above would now have
changed!
If it would still show the "old" time, there would be no problem.
Can you advise me on this point as well?

Thank you
rich

:

Comments inline...

--

Ken Snell
<MS ACCESS MVP>

Hi

I have done a lot of "digging" through posts to try to find the
answers
I
need.....

I am setting up an export to excel. I have read in the Access
help
File,
TransferSpreadsheet Action, that it is not possible to export to
Excel
without the Field Names appearing in the first row of the
spreadsheet.

This is correct. TransferSpreadsheet will always export the field
names
when
exporting to an EXCEL file.


(Is there any way around this?

Not unless you use VBA code to open a recordset for the query
you're
exporting, open an EXCEL file using Automation, and write the data
directly
into the spreadsheet. Or use TransferSpreadsheet to export the
query,
then
use Automation to open the spreadsheet and delete the first row.


Or is it possible to have the field names appear
in the "horizontal axis" at the top of the spreadsheet where A,
B, C
etc
appear as column names by default?)

Nope, not to my knowledge.


I have read in posts that a way to put the field names in column
headers
in
the spreadsheet is to use a "make table query." How do I do
this?
I
would
like to have a button that the user presses on an Access form to
save
just
one field from one table to an Excel file.

I'm not understanding this. A "make table query" has no advantages
for
what
you want to do. You can export a query directly, and you can
"change"
the
field names in the query using calculated fields in place of the
original
fields. For example, if you wanted the "field name" of a field to
be
"User
Name" instead of "UserName" (the name in the table), you'd have a
calculated
field that would look like this in the QBE design window:
User Name: [UserName]

This means you can use customized "field names" for the export, and
these
customized names will appear in the first row of the spreadsheet.



This is new to me... if you could post what is needed step by
step
(including code) I'd be very grateful.

TIA
Rich

 

[Guest]

Hi Ken
I tried your updated code and still get the same error:
subscript out of range

There is only one field being exported... I don't think this will make any
difference but didn't mention it before.

It's possible that the file hasn't been fully saved yet when the attempt is
made to open it. Let's change the code by adding a DoEvents step to give
time for that to happen:

Dim sNow As String, sFile As String, sSheet As String
Dim xApp As Object, xWk As Object, xWs As Object
sNow = Format(Date, " dd-mm-yyyy") & " " & Format(Time, "hhmm")
sFile = "C:\My Documents\DSCP ANPR" & sNow & ".xls"
sSheet = "VRM for Export"
DoCmd.TransferSpreadsheet acExport, 8, sSheet, sFile, True
DoEvents
Set xApp = CreateObject("Excel.Application")
Set xWk = xApp.Workbooks.Open(sFile)
Set xWs = xWk.Worksheets(Left(sSheet, 31))
xWs.Range("A1").EntireRow.Delete
Set xWs = Nothing
xWk.Close True
Set xWk = Nothing
xApp.Quit
Set xApp = Nothing

--

Ken Snell
<MS ACCESS MVP>

I've just done that... now I'm getting the following error:
error #9
subscript out of range

Sorry.. typo on my part. Change that line to this:

Set xWk = xApp.Workbooks.Open(sFile)


--

Ken Snell
<MS ACCESS MVP>


Thanks for your post Ken. There is an error in here.
When I paste your code into VB the following line is highlighted in
red:
Set xWk = xApp.Workbooks.Open sFile
I tried putting a dot after "Open" but then I get the following error:
error #449 Argument not optional
(I'm not sure to which line of the code this relates.)



:

Here is some sample code that will open the EXCEL file and delete the
first
row from the specfic spreadsheet:

Dim sNow As String, sFile As String, sSheet As String
Dim xApp As Object, xWk As Object, xWs As Object
sNow = Format(Date, " dd-mm-yyyy") & " " & Format(Time, "hhmm")
sFile = "C:\My Documents\DSCP ANPR" & sNow & ".xls"
sSheet = "VRM for Export"
DoCmd.TransferSpreadsheet acExport, 8, sSheet, sFile, True
Set xApp = CreateObject("Excel.Application")
Set xWk = xApp.Workbooks.Open sFile
Set xWs = xWk.Worksheets(Left(sSheet, 31))
xWs.Range("A1").EntireRow.Delete
Set xWs = Nothing
xWk.Close True
Set xWk = Nothing
xApp.Quit
Set xApp = Nothing


--

Ken Snell
<MS ACCESS MVP>




At the moment I am using TransferSpreadsheet to create the Excel
file.
How do I open the Excel file and delete the first row of records
using
automation? I've never done this before. The TransferSpreadsheet
is
carried
out in VBA called from a command button (not in a macro.) Or is the
first
way you suggest a better way of doing this?

One other thought: I am including the time and date in the filename
when
creating the Excel file, using the following code:
Dim sNow As String
sNow = Format(Date, " dd-mm-yyyy") & " " & Format(Time, "hhmm")
DoCmd.TransferSpreadsheet acExport, 8, "VRM for Export", "C:\My
Documents\DSCP ANPR" & sNow & ".xls", True

If I need to include code to open the Excel file (to delete the
first
row),
I will (presumably) need to include the filename again. So using
the
following for the filename would work (right?):
"VRM for Export", "C:\My Documents\DSCP ANPR" & sNow & ".xls"
My only thought is whether this would include the time as it "now"
is,
rather than as it was when the Excel file was saved.... i.e. if the
realtime
has just changed to the next minute (in the milliseconds it has
taken
for
the
code to be processed), would the variable sNow now show the updated
minute?
If it would, then the filename would be cited above would now have
changed!
If it would still show the "old" time, there would be no problem.
Can you advise me on this point as well?

Thank you
rich

:

Comments inline...

--

Ken Snell
<MS ACCESS MVP>

Hi

I have done a lot of "digging" through posts to try to find the
answers
I
need.....

I am setting up an export to excel. I have read in the Access
help
File,
TransferSpreadsheet Action, that it is not possible to export to
Excel
without the Field Names appearing in the first row of the
spreadsheet.

This is correct. TransferSpreadsheet will always export the field
names
when
exporting to an EXCEL file.


(Is there any way around this?

Not unless you use VBA code to open a recordset for the query
you're
exporting, open an EXCEL file using Automation, and write the data
directly
into the spreadsheet. Or use TransferSpreadsheet to export the
query,
then
use Automation to open the spreadsheet and delete the first row.


Or is it possible to have the field names appear
in the "horizontal axis" at the top of the spreadsheet where A,
B, C
etc
appear as column names by default?)

Nope, not to my knowledge.


I have read in posts that a way to put the field names in column
headers
in
the spreadsheet is to use a "make table query." How do I do
this?
I
would
like to have a button that the user presses on an Access form to
save
just
one field from one table to an Excel file.

I'm not understanding this. A "make table query" has no advantages
for
what
you want to do. You can export a query directly, and you can
"change"
the
field names in the query using calculated fields in place of the
original
fields. For example, if you wanted the "field name" of a field to
be
"User
Name" instead of "UserName" (the name in the table), you'd have a
calculated
field that would look like this in the QBE design window:
User Name: [UserName]

This means you can use customized "field names" for the export, and
these
customized names will appear in the first row of the spreadsheet.



This is new to me... if you could post what is needed step by
step
(including code) I'd be very grateful.

TIA
Rich

 

[Ken Snell [MVP]]

It's happening on the "Open" line? or on another line?

--

Ken Snell
<MS ACCESS MVP>

Hi Ken
I tried your updated code and still get the same error:
subscript out of range

There is only one field being exported... I don't think this will make any
difference but didn't mention it before.

It's possible that the file hasn't been fully saved yet when the attempt
is
made to open it. Let's change the code by adding a DoEvents step to give
time for that to happen:

Dim sNow As String, sFile As String, sSheet As String
Dim xApp As Object, xWk As Object, xWs As Object
sNow = Format(Date, " dd-mm-yyyy") & " " & Format(Time, "hhmm")
sFile = "C:\My Documents\DSCP ANPR" & sNow & ".xls"
sSheet = "VRM for Export"
DoCmd.TransferSpreadsheet acExport, 8, sSheet, sFile, True
DoEvents
Set xApp = CreateObject("Excel.Application")
Set xWk = xApp.Workbooks.Open(sFile)
Set xWs = xWk.Worksheets(Left(sSheet, 31))
xWs.Range("A1").EntireRow.Delete
Set xWs = Nothing
xWk.Close True
Set xWk = Nothing
xApp.Quit
Set xApp = Nothing

--

Ken Snell
<MS ACCESS MVP>

I've just done that... now I'm getting the following error:
error #9
subscript out of range


:

Sorry.. typo on my part. Change that line to this:

Set xWk = xApp.Workbooks.Open(sFile)


--

Ken Snell
<MS ACCESS MVP>


Thanks for your post Ken. There is an error in here.
When I paste your code into VB the following line is highlighted in
red:
Set xWk = xApp.Workbooks.Open sFile
I tried putting a dot after "Open" but then I get the following
error:
error #449 Argument not optional
(I'm not sure to which line of the code this relates.)



:

Here is some sample code that will open the EXCEL file and delete
the
first
row from the specfic spreadsheet:

Dim sNow As String, sFile As String, sSheet As String
Dim xApp As Object, xWk As Object, xWs As Object
sNow = Format(Date, " dd-mm-yyyy") & " " & Format(Time, "hhmm")
sFile = "C:\My Documents\DSCP ANPR" & sNow & ".xls"
sSheet = "VRM for Export"
DoCmd.TransferSpreadsheet acExport, 8, sSheet, sFile, True
Set xApp = CreateObject("Excel.Application")
Set xWk = xApp.Workbooks.Open sFile
Set xWs = xWk.Worksheets(Left(sSheet, 31))
xWs.Range("A1").EntireRow.Delete
Set xWs = Nothing
xWk.Close True
Set xWk = Nothing
xApp.Quit
Set xApp = Nothing


--

Ken Snell
<MS ACCESS MVP>




At the moment I am using TransferSpreadsheet to create the Excel
file.
How do I open the Excel file and delete the first row of records
using
automation? I've never done this before. The
TransferSpreadsheet
is
carried
out in VBA called from a command button (not in a macro.) Or is
the
first
way you suggest a better way of doing this?

One other thought: I am including the time and date in the
filename
when
creating the Excel file, using the following code:
Dim sNow As String
sNow = Format(Date, " dd-mm-yyyy") & " " & Format(Time,
"hhmm")
DoCmd.TransferSpreadsheet acExport, 8, "VRM for Export",
"C:\My
Documents\DSCP ANPR" & sNow & ".xls", True

If I need to include code to open the Excel file (to delete the
first
row),
I will (presumably) need to include the filename again. So using
the
following for the filename would work (right?):
"VRM for Export", "C:\My Documents\DSCP ANPR" & sNow & ".xls"
My only thought is whether this would include the time as it
"now"
is,
rather than as it was when the Excel file was saved.... i.e. if
the
realtime
has just changed to the next minute (in the milliseconds it has
taken
for
the
code to be processed), would the variable sNow now show the
updated
minute?
If it would, then the filename would be cited above would now
have
changed!
If it would still show the "old" time, there would be no problem.
Can you advise me on this point as well?

Thank you
rich

:

Comments inline...

--

Ken Snell
<MS ACCESS MVP>

Hi

I have done a lot of "digging" through posts to try to find
the
answers
I
need.....

I am setting up an export to excel. I have read in the Access
help
File,
TransferSpreadsheet Action, that it is not possible to export
to
Excel
without the Field Names appearing in the first row of the
spreadsheet.

This is correct. TransferSpreadsheet will always export the
field
names
when
exporting to an EXCEL file.


(Is there any way around this?

Not unless you use VBA code to open a recordset for the query
you're
exporting, open an EXCEL file using Automation, and write the
data
directly
into the spreadsheet. Or use TransferSpreadsheet to export the
query,
then
use Automation to open the spreadsheet and delete the first row.


Or is it possible to have the field names appear
in the "horizontal axis" at the top of the spreadsheet where
A,
B, C
etc
appear as column names by default?)

Nope, not to my knowledge.


I have read in posts that a way to put the field names in
column
headers
in
the spreadsheet is to use a "make table query." How do I do
this?
I
would
like to have a button that the user presses on an Access form
to
save
just
one field from one table to an Excel file.

I'm not understanding this. A "make table query" has no
advantages
for
what
you want to do. You can export a query directly, and you can
"change"
the
field names in the query using calculated fields in place of the
original
fields. For example, if you wanted the "field name" of a field
to
be
"User
Name" instead of "UserName" (the name in the table), you'd have
a
calculated
field that would look like this in the QBE design window:
User Name: [UserName]

This means you can use customized "field names" for the export,
and
these
customized names will appear in the first row of the
spreadsheet.



This is new to me... if you could post what is needed step by
step
(including code) I'd be very grateful.

TIA
Rich

 

[Guest]

Hi Ken
I'm not sure which line is generating the error because when I look at the
VB, none of the lines is highlighted in red. When the error occurs, the "end
or debug" buttons don't appear in the msg box and after clicking OK, Access
just continues as normal.

The error is trapped by the CmdExport command button's OnClickevent
(Err_CmdExport_Click) which uses the following code:
MsgBox "Error in CmdExport_Click( ) in" & vbCrLf & _
Me.Name & " form." & vbCrLf & vbCrLf & _
"Error #" & Err.Number & vbCrLf & Err.Description

Is there any way to find out which line is generating the problem by
adapting this code?
thanks
rich

It's happening on the "Open" line? or on another line?

--

Ken Snell
<MS ACCESS MVP>

Hi Ken
I tried your updated code and still get the same error:
subscript out of range

There is only one field being exported... I don't think this will make any
difference but didn't mention it before.

It's possible that the file hasn't been fully saved yet when the attempt
is
made to open it. Let's change the code by adding a DoEvents step to give
time for that to happen:

Dim sNow As String, sFile As String, sSheet As String
Dim xApp As Object, xWk As Object, xWs As Object
sNow = Format(Date, " dd-mm-yyyy") & " " & Format(Time, "hhmm")
sFile = "C:\My Documents\DSCP ANPR" & sNow & ".xls"
sSheet = "VRM for Export"
DoCmd.TransferSpreadsheet acExport, 8, sSheet, sFile, True
DoEvents
Set xApp = CreateObject("Excel.Application")
Set xWk = xApp.Workbooks.Open(sFile)
Set xWs = xWk.Worksheets(Left(sSheet, 31))
xWs.Range("A1").EntireRow.Delete
Set xWs = Nothing
xWk.Close True
Set xWk = Nothing
xApp.Quit
Set xApp = Nothing

--

Ken Snell
<MS ACCESS MVP>
I've just done that... now I'm getting the following error:
error #9
subscript out of range


:

Sorry.. typo on my part. Change that line to this:

Set xWk = xApp.Workbooks.Open(sFile)


--

Ken Snell
<MS ACCESS MVP>


Thanks for your post Ken. There is an error in here.
When I paste your code into VB the following line is highlighted in
red:
Set xWk = xApp.Workbooks.Open sFile
I tried putting a dot after "Open" but then I get the following
error:
error #449 Argument not optional
(I'm not sure to which line of the code this relates.)



:

Here is some sample code that will open the EXCEL file and delete
the
first
row from the specfic spreadsheet:

Dim sNow As String, sFile As String, sSheet As String
Dim xApp As Object, xWk As Object, xWs As Object
sNow = Format(Date, " dd-mm-yyyy") & " " & Format(Time, "hhmm")
sFile = "C:\My Documents\DSCP ANPR" & sNow & ".xls"
sSheet = "VRM for Export"
DoCmd.TransferSpreadsheet acExport, 8, sSheet, sFile, True
Set xApp = CreateObject("Excel.Application")
Set xWk = xApp.Workbooks.Open sFile
Set xWs = xWk.Worksheets(Left(sSheet, 31))
xWs.Range("A1").EntireRow.Delete
Set xWs = Nothing
xWk.Close True
Set xWk = Nothing
xApp.Quit
Set xApp = Nothing


--

Ken Snell
<MS ACCESS MVP>




At the moment I am using TransferSpreadsheet to create the Excel
file.
How do I open the Excel file and delete the first row of records
using
automation? I've never done this before. The
TransferSpreadsheet
is
carried
out in VBA called from a command button (not in a macro.) Or is
the
first
way you suggest a better way of doing this?

One other thought: I am including the time and date in the
filename
when
creating the Excel file, using the following code:
Dim sNow As String
sNow = Format(Date, " dd-mm-yyyy") & " " & Format(Time,
"hhmm")
DoCmd.TransferSpreadsheet acExport, 8, "VRM for Export",
"C:\My
Documents\DSCP ANPR" & sNow & ".xls", True

If I need to include code to open the Excel file (to delete the
first
row),
I will (presumably) need to include the filename again. So using
the
following for the filename would work (right?):
"VRM for Export", "C:\My Documents\DSCP ANPR" & sNow & ".xls"
My only thought is whether this would include the time as it
"now"
is,
rather than as it was when the Excel file was saved.... i.e. if
the
realtime
has just changed to the next minute (in the milliseconds it has
taken
for
the
code to be processed), would the variable sNow now show the
updated
minute?
If it would, then the filename would be cited above would now
have
changed!
If it would still show the "old" time, there would be no problem.
Can you advise me on this point as well?

Thank you
rich

:

Comments inline...

--

Ken Snell
<MS ACCESS MVP>

Hi

I have done a lot of "digging" through posts to try to find
the
answers
I
need.....

I am setting up an export to excel. I have read in the Access
help
File,
TransferSpreadsheet Action, that it is not possible to export
to
Excel
without the Field Names appearing in the first row of the
spreadsheet.

This is correct. TransferSpreadsheet will always export the
field
names
when
exporting to an EXCEL file.


(Is there any way around this?

Not unless you use VBA code to open a recordset for the query
you're
exporting, open an EXCEL file using Automation, and write the
data
directly
into the spreadsheet. Or use TransferSpreadsheet to export the
query,
then
use Automation to open the spreadsheet and delete the first row.


Or is it possible to have the field names appear
in the "horizontal axis" at the top of the spreadsheet where
A,
B, C
etc
appear as column names by default?)

Nope, not to my knowledge.


I have read in posts that a way to put the field names in
column
headers
in
the spreadsheet is to use a "make table query." How do I do
this?
I
would
like to have a button that the user presses on an Access form
to
save
just
one field from one table to an Excel file.

I'm not understanding this. A "make table query" has no
advantages
for
what
you want to do. You can export a query directly, and you can
"change"
the
field names in the query using calculated fields in place of the
original
fields. For example, if you wanted the "field name" of a field
to
be
"User
Name" instead of "UserName" (the name in the table), you'd have
a
calculated
field that would look like this in the QBE design window:
User Name: [UserName]

This means you can use customized "field names" for the export,
and
these
customized names will appear in the first row of the
spreadsheet.



This is new to me... if you could post what is needed step by
step
(including code) I'd be very grateful.

TIA
Rich

 

[Ken Snell [MVP]]

For debugging purposes, comment out the
On Error GoTo Err_CmdExport_Click

line. That will disable the error handler so that the program will stop on
the offending line.

--

Ken Snell
<MS ACCESS MVP>

Hi Ken
I'm not sure which line is generating the error because when I look at the
VB, none of the lines is highlighted in red. When the error occurs, the
"end
or debug" buttons don't appear in the msg box and after clicking OK,
Access
just continues as normal.

The error is trapped by the CmdExport command button's OnClickevent
(Err_CmdExport_Click) which uses the following code:
MsgBox "Error in CmdExport_Click( ) in" & vbCrLf & _
Me.Name & " form." & vbCrLf & vbCrLf & _
"Error #" & Err.Number & vbCrLf & Err.Description

Is there any way to find out which line is generating the problem by
adapting this code?
thanks
rich

It's happening on the "Open" line? or on another line?

--

Ken Snell
<MS ACCESS MVP>

Hi Ken
I tried your updated code and still get the same error:
subscript out of range

There is only one field being exported... I don't think this will make
any
difference but didn't mention it before.

:

It's possible that the file hasn't been fully saved yet when the
attempt
is
made to open it. Let's change the code by adding a DoEvents step to
give
time for that to happen:

Dim sNow As String, sFile As String, sSheet As String
Dim xApp As Object, xWk As Object, xWs As Object
sNow = Format(Date, " dd-mm-yyyy") & " " & Format(Time, "hhmm")
sFile = "C:\My Documents\DSCP ANPR" & sNow & ".xls"
sSheet = "VRM for Export"
DoCmd.TransferSpreadsheet acExport, 8, sSheet, sFile, True
DoEvents
Set xApp = CreateObject("Excel.Application")
Set xWk = xApp.Workbooks.Open(sFile)
Set xWs = xWk.Worksheets(Left(sSheet, 31))
xWs.Range("A1").EntireRow.Delete
Set xWs = Nothing
xWk.Close True
Set xWk = Nothing
xApp.Quit
Set xApp = Nothing

--

Ken Snell
<MS ACCESS MVP>
I've just done that... now I'm getting the following error:
error #9
subscript out of range


:

Sorry.. typo on my part. Change that line to this:

Set xWk = xApp.Workbooks.Open(sFile)


--

Ken Snell
<MS ACCESS MVP>


Thanks for your post Ken. There is an error in here.
When I paste your code into VB the following line is highlighted
in
red:
Set xWk = xApp.Workbooks.Open sFile
I tried putting a dot after "Open" but then I get the following
error:
error #449 Argument not optional
(I'm not sure to which line of the code this relates.)



:

Here is some sample code that will open the EXCEL file and
delete
the
first
row from the specfic spreadsheet:

Dim sNow As String, sFile As String, sSheet As String
Dim xApp As Object, xWk As Object, xWs As Object
sNow = Format(Date, " dd-mm-yyyy") & " " & Format(Time,
"hhmm")
sFile = "C:\My Documents\DSCP ANPR" & sNow & ".xls"
sSheet = "VRM for Export"
DoCmd.TransferSpreadsheet acExport, 8, sSheet, sFile, True
Set xApp = CreateObject("Excel.Application")
Set xWk = xApp.Workbooks.Open sFile
Set xWs = xWk.Worksheets(Left(sSheet, 31))
xWs.Range("A1").EntireRow.Delete
Set xWs = Nothing
xWk.Close True
Set xWk = Nothing
xApp.Quit
Set xApp = Nothing


--

Ken Snell
<MS ACCESS MVP>




At the moment I am using TransferSpreadsheet to create the
Excel
file.
How do I open the Excel file and delete the first row of
records
using
automation? I've never done this before. The
TransferSpreadsheet
is
carried
out in VBA called from a command button (not in a macro.) Or
is
the
first
way you suggest a better way of doing this?

One other thought: I am including the time and date in the
filename
when
creating the Excel file, using the following code:
Dim sNow As String
sNow = Format(Date, " dd-mm-yyyy") & " " & Format(Time,
"hhmm")
DoCmd.TransferSpreadsheet acExport, 8, "VRM for Export",
"C:\My
Documents\DSCP ANPR" & sNow & ".xls", True

If I need to include code to open the Excel file (to delete
the
first
row),
I will (presumably) need to include the filename again. So
using
the
following for the filename would work (right?):
"VRM for Export", "C:\My Documents\DSCP ANPR" & sNow & ".xls"
My only thought is whether this would include the time as it
"now"
is,
rather than as it was when the Excel file was saved.... i.e.
if
the
realtime
has just changed to the next minute (in the milliseconds it
has
taken
for
the
code to be processed), would the variable sNow now show the
updated
minute?
If it would, then the filename would be cited above would now
have
changed!
If it would still show the "old" time, there would be no
problem.
Can you advise me on this point as well?

Thank you
rich

:

Comments inline...

--

Ken Snell
<MS ACCESS MVP>

message
Hi

I have done a lot of "digging" through posts to try to find
the
answers
I
need.....

I am setting up an export to excel. I have read in the
Access
help
File,
TransferSpreadsheet Action, that it is not possible to
export
to
Excel
without the Field Names appearing in the first row of the
spreadsheet.

This is correct. TransferSpreadsheet will always export the
field
names
when
exporting to an EXCEL file.


(Is there any way around this?

Not unless you use VBA code to open a recordset for the query
you're
exporting, open an EXCEL file using Automation, and write the
data
directly
into the spreadsheet. Or use TransferSpreadsheet to export
the
query,
then
use Automation to open the spreadsheet and delete the first
row.


Or is it possible to have the field names appear
in the "horizontal axis" at the top of the spreadsheet
where
A,
B, C
etc
appear as column names by default?)

Nope, not to my knowledge.


I have read in posts that a way to put the field names in
column
headers
in
the spreadsheet is to use a "make table query." How do I
do
this?
I
would
like to have a button that the user presses on an Access
form
to
save
just
one field from one table to an Excel file.

I'm not understanding this. A "make table query" has no
advantages
for
what
you want to do. You can export a query directly, and you can
"change"
the
field names in the query using calculated fields in place of
the
original
fields. For example, if you wanted the "field name" of a
field
to
be
"User
Name" instead of "UserName" (the name in the table), you'd
have
a
calculated
field that would look like this in the QBE design window:
User Name: [UserName]

This means you can use customized "field names" for the
export,
and
these
customized names will appear in the first row of the
spreadsheet.



This is new to me... if you could post what is needed step
by
step
(including code) I'd be very grateful.

TIA
Rich

 

[Guest]

OK Ken - Just done that - the offending line is
Set xWs = xWk.Worksheets(Left(sSheet, 31))

Not knowing a great deal about the cause of this error, I tried changing the
last number to '1', but still got a "subscript out of range' error.

 

[Ken Snell [MVP]]

Hmmm... that error indicates that the workbook that is opened does not have
a worksheet in it with the name that I'd expect. So, let's do an experiment.

When the code stops on that line because of the error, make the Immediate
Window visible (View | Immediate Window), and type the following into that
window and then press Enter:

?xWk.Name


Then type this and press Enter:

?xWk.Path


Then type this and press Enter:

?xWk.Worksheets.Count


Then type this and press Enter:

?xWk.Worksheets(1).Count


What do you get for each of these tests?

 

[Ken Snell [MVP]]

Typo in my earlier post -- use this one instead!
---
Hmmm... that error indicates that the workbook that is opened does not have
a worksheet in it with the name that I'd expect. So, let's do an experiment.

When the code stops on that line because of the error, make the Immediate
Window visible (View | Immediate Window), and type the following into that
window and then press Enter:

?xWk.Name


Then type this and press Enter:

?xWk.Path


Then type this and press Enter:

?xWk.Worksheets.Count


Then type this and press Enter:

?xWk.Worksheets(1).Name


What do you get for each of these tests?

 

[Guest]

Hi Ken

The project is now complete.. unfortunately I couldn't get this issue sorted
in time, but it shouldn't affect the functionality.. just a little niggle
really.
Thanks for your time on this.

Rich

 

[Ken Snell [MVP]]

Hi Ken

The project is now complete.. unfortunately I couldn't get this issue
sorted
in time, but it shouldn't affect the functionality.. just a little niggle
really.
Thanks for your time on this.

Rich

Sorry that we didn't get this issue fully resolved in time.....
Congratulations on finishing the project.