TransferSpreadsheet

[chrisms]

Guys,

I have a from where i select some values(including a code) and then produce a query filtered by these values.

From this i need to save the filtered query as an Excel spreadsheet with filename = code.

TransferSpreadsheet seems to eb the answer but I can't get it to modify the filename by using c:\" & strCode &". Is this the way to do it?

Any help is great as this is now pretty urgent.

Cheers

Chris

 

[KenHigg]

Post the entire pc of code that you have.

kh

 

[KenHigg]

...,"c:\" & strCode,...

(Actually, I would not hard code the path either...)

???
ken

 

[chrisms]

Currently:

Private Sub Export_Click()

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel97, [Results], "c:\" & [Cost Code].Value & "", True

End Sub

I am only a beginner, so please don't laugh too loudly at the lack of error handling etc!

Cheers

Chris

 

[KenHigg]

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel97, [Results], "c:\" & myFormName.[Cost Code] , True

???
kh

fyi:

[Cost Code] = bad naming practice (lose the space)

 

[chrisms]

Seems to get me somewhere but returns an error(yes i put the handling in):

Access can't find the field the field '!' referred to in you expression.

For the ! please read a vertical bar, I just can't find the symbol.

Does this mean the code has an error or the query? The query runs fine just to view it.

Chris

 

[KenHigg]

Sorry to keep asking this, but could you copy and paste your code?

kh

 

[chrisms]

Ken,

Private Sub Command19_Click()
On Error GoTo Err_Command19_Click

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel97, [Results], "c:\" & [Front End].[Cost Code], True


Exit_Command19_Click:
Exit Sub

Err_Command19_Click:
MsgBox Err.Description
Resume Exit_Command19_Click

End Sub

Is the code I am using, just as u recommended. The query is quite detailed, do u want that as well?

C

 

[KenHigg]

The assumption I was making that may have been in error was that in:

[Front End].[Cost Code]

[Front End] = the name of a form and,
[Cost Code] = a name of a text box on that form.

Is that right or wrong?


kh

 

[chrisms]

Thats correct, Front End is the form and Cost Code is a list box, where the code is selected

 

[KenHigg]

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel97, [Results], "c:\" & forms.[Front End].[Cost Code], True

???
ken

 

[chrisms]

Ken,

Again thanks for all the help, much appreciated.

The same error is occuring, no idea what it means really... If you have no ideas thanks for all your help anyway, has been very useful to me.

Chris

 

[KenHigg]

Don't despair, this is really easy if we can get on the same page...

Can you put enough of the offending db in a second mdb to post? Maybe I can find the problem that way...

kh

 

[chrisms]

Unfortunately can't post the data. However I know the query runs fine, because it opens fine and filters perfectly when asked to.

Therefore the field referred to should be within VBA, but the error occurs within Access, rather as a VBA debug.

I will try to give you as much info as poss:

Front End Form:
Cost Code: A list box containg a set of cost codes.
No of Users: The number of users within the selected cost code, update when the cost code is selected.
Percentage: A value(int) which is used to multiply the number of users by.

Query.Results:
A number of fields including:

Cost Code. The query is filtered by this field using the cost code specified on the Front End form
Product. A product or service that is used by that cost code.
Users. The number of users using that product for that cost code. This is filtered by the number generated by mulitplying the Number of users by the Percentage.

I have a button on the Form.Front End which opens the query and then filters it by cost code and then either "users >= the number of user*percentage" or another button for "users <= the number of users*percentage"

I then need to output this query to Excel for each cost code that I run the query for.

Am I doing this in the right way, maybe there is a different workaround we can try?

Hope this helps

Chris

 

[KenHigg]

Using this example from Access help:

TransferSpreadsheet acImport, 3, "Employees","C:\Lotus\Newemps.wk3", True,

Should you be using:

TransferSpreadsheet acExport, acSpreadsheetTypeExcel97, "Results", "c:\" & forms.[Front End].[Cost Code], True


???
ken

 

[chrisms]

Ken,

Thanks, well spotted. That gets rid of the strange | (yep, found it) error. I can then export data if i type the destination in completely, but using:

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel97, "Results", "c:\" & Forms.[Front End].[Cost Code] & ".xls", True

Gives me a: "Object doesn't support this property or method" type error

I added the .xls as it is required I think to get the right file, but without it I still get the same result, using the code u pasted in the last reply.

Any thoughts?

 

[KenHigg]

Looks like acSpreadsheetTypeExcel97 should be acSpreadsheetTypeExcel7

???
ken

 

[chrisms]

Looks like we could have been barking up the wrong tree. That fixes the | issue, but now a:

Object doesn't support this property or method

error appears.

Does this mean we can't play around with the filename or is it something else?

 

[KenHigg]

One of the arguments in the 'TransferSpreadsheet' is bugging it out...

Do you have to use Excel 97?

???
ken

 

[chrisms]

No, can use any form of Excel, could use word as well, but don't know if that is the issue. Have got it working in the export with a static file name now, so not sure whether it allows this dynamic filenaming or not

Chris