Docmd.TransferSpreadsheet

[Mikey C]

Hello!

Can anybody give me some code for the Docmd.TransferSpreadsheet
command, which i can adapt for my database (or know where to get it)?

I need to be able to create a command button which will export a number

of tables into the same workbook at the same time. Ideally, I would
like
to be able to export back this way but I don't think my user-level
security will let me? (Access wont let me import from Excel - even with
an admisistrators password).

Really appreciate the help

 

[Nick 'The database Guy']

Hi Mikey,

Docmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9,
varQuery(intLoopVar), strPath & "FileName.xls", 0, varName(intLoopVar)

The first argument (acSpreadsheetTypeExcel9) will need to be adjusted
according to what version of acccess you are using.

Good luck

Nick McMillen

 

[Mikey C]

You are a true saint - thank you very much!

Hi Mikey,

Docmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9,
varQuery(intLoopVar), strPath & "FileName.xls", 0, varName(intLoopVar)

The first argument (acSpreadsheetTypeExcel9) will need to be adjusted
according to what version of acccess you are using.

Good luck

Nick McMillen

 

[Nick 'The database Guy']

Correction:

Although I said that the first argument would need to be adjusted
according to what version of access you were using, what I actually
meant was that would have to be adjusted depending on which version of
Excel you would like in output to.

Also I should have explained that the code I pasted in would output
varQuery(intLoopVar) to the sheet varName(intLoopVar) within
"FileName.xls"

My halo keeps slipping.

Nick

 

[Mikey C]

Sorry Nick (or anyone), i'm a bit of a wolly when it comes to code

Can you explain this a little more for me?

I have created a command button called 'Export_Tables' and inserted the
following code in the 'On-Click' value:

Private Sub Export_Tables_Click()
Docmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9,
varQuery (intLoopVar), strPath & "Test Export.xls", 0,
varName(intLoopVar)
End Sub

1. I am using Excel 2003 SP2
2. I want to export the following tables: Financial/Stats, Company,
Country, Game Type, Segment, Period Covering, year, Financial / KPI's,
Measure, Data Type and Currency & Format
3. The workbook I'm using at the moment (for testing) is named 'Test
Export'.

Let me know if you need any more info

Thanks

 

[Mikey C]

Ah, no problem - got it cracked! Thanks anyways

Sorry Nick (or anyone), i'm a bit of a wolly when it comes to code

Can you explain this a little more for me?

I have created a command button called 'Export_Tables' and inserted the
following code in the 'On-Click' value:

Private Sub Export_Tables_Click()
Docmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9,
varQuery (intLoopVar), strPath & "Test Export.xls", 0,
varName(intLoopVar)
End Sub

1. I am using Excel 2003 SP2
2. I want to export the following tables: Financial/Stats, Company,
Country, Game Type, Segment, Period Covering, year, Financial / KPI's,
Measure, Data Type and Currency & Format
3. The workbook I'm using at the moment (for testing) is named 'Test
Export'.

Let me know if you need any more info

Thanks