Need to name range/transfer spreadsheet

[Guest]

Hi everyone! Using A02 on XP. Not a programmer but learning. On my form I
locate the contract number required and click a button to run a macro with a
transfer spreadsheet step. In the action arguments segment I have:

Transfer Type: Export
Spreadsheet Type: Microsoft Excel 5-7
Table Name: qExportCensus1
File Name: ="S:\RPS\PTS\CensusConversion\ToClient\" &
[Forms]![fCensus1Conversion]![RunThisOne] & ".xls"
Has Field Names: Yes
Range: Data

The above will export the file and data using the query listed and names the
file with the contract number contained in the field [RunThisOne]. Works
great. However, I would REALLY, REALLY like to name the new spreadsheet with
the contract number. I've written:
[Forms]![fCensus1Conversion]![RunThisOne] but nothing. Can this be done?
What am I missing?

Would appreciate any help or advice on this. Thanks in advance for your time.

 

[John Nurick]

Hi Bonnie,

If I understand you right, you just need to put something like this in
the Range argument of the macro:

=[Forms]![fCensus1Conversion]![RunThisOne]

Hi everyone! Using A02 on XP. Not a programmer but learning. On my form I
locate the contract number required and click a button to run a macro with a
transfer spreadsheet step. In the action arguments segment I have:

Transfer Type: Export
Spreadsheet Type: Microsoft Excel 5-7
Table Name: qExportCensus1
File Name: ="S:\RPS\PTS\CensusConversion\ToClient\" &
[Forms]![fCensus1Conversion]![RunThisOne] & ".xls"
Has Field Names: Yes
Range: Data

The above will export the file and data using the query listed and names the
file with the contract number contained in the field [RunThisOne]. Works
great. However, I would REALLY, REALLY like to name the new spreadsheet with
the contract number. I've written:
[Forms]![fCensus1Conversion]![RunThisOne] but nothing. Can this be done?
What am I missing?

Would appreciate any help or advice on this. Thanks in advance for your time.

 

[Guest]

Hi John. Thanks for the reply. I think I messed up my inquiry. Probably
why you said IF. May I start over? I should have written about a Transfer
Spreadsheet IMPORT.

On my form I locate the contract number required and click a button to run a
macro with a transfer spreadsheet step. I'm bringing an Excel spreadsheet in
to run a compare against the data prior to editing. Would a link be better?
Don't have any experience with them. Mainly need to get this import to work.
I know it's my wordage.

In the action arguments segment I have:

Transfer Type: Import
Spreadsheet Type: Microsoft Excel 5-7 (if the file is edited by an older
version)
Table Name: =[Forms]![fCensus1Conversion]![RunThisOne] & "Rev"
File Name: ="S:\RPS\PTS\CensusConversion\FromClient" &
[Forms]![fCensus1Conversion]![RunThisOne] & ".xls"
Has Field Names: Yes
Range: (blank)

When I click my button, nothing happens. Have I missed a quote, ampersand
or bracket somewhere?

Thanks again!
--
Bonnie

Hi Bonnie,

If I understand you right, you just need to put something like this in
the Range argument of the macro:

=[Forms]![fCensus1Conversion]![RunThisOne]

Hi everyone! Using A02 on XP. Not a programmer but learning. On my form I
locate the contract number required and click a button to run a macro with a
transfer spreadsheet step. In the action arguments segment I have:

Transfer Type: Export
Spreadsheet Type: Microsoft Excel 5-7
Table Name: qExportCensus1
File Name: ="S:\RPS\PTS\CensusConversion\ToClient\" &
[Forms]![fCensus1Conversion]![RunThisOne] & ".xls"
Has Field Names: Yes
Range: Data

The above will export the file and data using the query listed and names the
file with the contract number contained in the field [RunThisOne]. Works
great. However, I would REALLY, REALLY like to name the new spreadsheet with
the contract number. I've written:
[Forms]![fCensus1Conversion]![RunThisOne] but nothing. Can this be done?
What am I missing?

Would appreciate any help or advice on this. Thanks in advance for your time.

 

[John Nurick]

If there's a problem I can't see it. The same syntax works fine for me.
Are you certain that the records aren't being imported to an existing
table, that the data you want is on the first visible worksheet of the
workbook, and so on?

One disadvantage of macros is that there are no debugging and error
handling facilities. About all I can suggest (apart of course from
re-doing it in VBA) is that you try changing each argument, one at a
time.

E.g. replace the Table Name argument with a literal name, e.g.
tblTestImport. If the macro works then, that's where the problem was.

Repeat for each other argument.

Hi John. Thanks for the reply. I think I messed up my inquiry. Probably
why you said IF. May I start over? I should have written about a Transfer
Spreadsheet IMPORT.

On my form I locate the contract number required and click a button to run a
macro with a transfer spreadsheet step. I'm bringing an Excel spreadsheet in
to run a compare against the data prior to editing. Would a link be better?
Don't have any experience with them. Mainly need to get this import to work.
I know it's my wordage.

In the action arguments segment I have:

Transfer Type: Import
Spreadsheet Type: Microsoft Excel 5-7 (if the file is edited by an older
version)
Table Name: =[Forms]![fCensus1Conversion]![RunThisOne] & "Rev"
File Name: ="S:\RPS\PTS\CensusConversion\FromClient" &
[Forms]![fCensus1Conversion]![RunThisOne] & ".xls"
Has Field Names: Yes
Range: (blank)

When I click my button, nothing happens. Have I missed a quote, ampersand
or bracket somewhere?

Thanks again!

 

[Guest]

Hi John,

Thanks for the reply. I did the spot replace and found I had miskeyed the
file path. Thought I had checked it eight ways to Sunday.

I really appreciate your time.
--
Bonnie

If there's a problem I can't see it. The same syntax works fine for me.
Are you certain that the records aren't being imported to an existing
table, that the data you want is on the first visible worksheet of the
workbook, and so on?

One disadvantage of macros is that there are no debugging and error
handling facilities. About all I can suggest (apart of course from
re-doing it in VBA) is that you try changing each argument, one at a
time.

E.g. replace the Table Name argument with a literal name, e.g.
tblTestImport. If the macro works then, that's where the problem was.

Repeat for each other argument.

Hi John. Thanks for the reply. I think I messed up my inquiry. Probably
why you said IF. May I start over? I should have written about a Transfer
Spreadsheet IMPORT.

On my form I locate the contract number required and click a button to run a
macro with a transfer spreadsheet step. I'm bringing an Excel spreadsheet in
to run a compare against the data prior to editing. Would a link be better?
Don't have any experience with them. Mainly need to get this import to work.
I know it's my wordage.

In the action arguments segment I have:

Transfer Type: Import
Spreadsheet Type: Microsoft Excel 5-7 (if the file is edited by an older
version)
Table Name: =[Forms]![fCensus1Conversion]![RunThisOne] & "Rev"
File Name: ="S:\RPS\PTS\CensusConversion\FromClient" &
[Forms]![fCensus1Conversion]![RunThisOne] & ".xls"
Has Field Names: Yes
Range: (blank)

When I click my button, nothing happens. Have I missed a quote, ampersand
or bracket somewhere?

Thanks again!