Need to name range/transfer spreadsheet

B

Bonnie

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.
 
J

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.
 
B

Bonnie

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


John Nurick said:
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.
 
J

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!
 
B

Bonnie

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


John Nurick said:
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!
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top