Export Logo from Access 97 to Excel 2000 from file on hard drive

[syoung4]

Please can anyone help me. I have a form in Access 97 which has parameter fields that are used to extract
data from my database and Export to Excel when a button is clicked. We have Access 97 and Excel 2000 using the EXCEL9.OLB

My code is quite long as it loops though recordsets and exports to multiple worksheets.

The problem I am having is trying to export a logo from a folder on my harddrive and put the logo on to each worksheet at cells(1,7)

At the moment I have a picture field on my form called logo. When the button is clicked this is copied into memory
and pasted into the excel sheets. This does work ok.

Me.logo.SetFocus 'this just goes to the logo field so that it can be copied
DoCmd.RunCommand acCmdCopy 'this copies the logo into memory

Set Rge = shts.Rows.Cells(1, 7)
Rge.PasteSpecial 'this pastes the logo on after all other data so that it only pastes once into each worksheet

But because the logo is pasted on to each sheet I can not get my code to go to cell (1,1) to finish.
Each worksheet stops at the logo position. If I could end the focus on cell(1,1) this would save me having to open each sheet and manualy position onto cell(1,1) each week.

If I create a macro from excel I have this code but it does not work in access97

ActiveSheet.Pictures.Insert( _
"S:\Invoicing\PAYMENT CERTIFICATES\Payment Certificate Database\Telent Logos\telent_logo.gif" _
).Select
Selection.ShapeRange.IncrementLeft 0.75
Selection.ShapeRange.IncrementTop 4.5

Even though I have reference to EXCEL9.OLB in the references I can not find Pictures.Insert that would get the code going.

 

[PHV]

In the access VBA code you should use full qualified excel objects:
yourXLappObj.ActiveSheet.Pictures.Insert( _
"S:\Invoicing\PAYMENT CERTIFICATES\Payment Certificate Database\Telent Logos\telent_logo.gif" _
).Select
yourXLappObj.Selection.ShapeRange.IncrementLeft 0.75
yourXLappObj.Selection.ShapeRange.IncrementTop 4.5

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[syoung4]

Thank you for your help the code works perfect.
The only problem I am having now is that I am exporting to one workbook with multiple worksheets by looping through the data.

I am putting the logo onto Cell G1 this works on all the sheets except the last one where the logo is put on to cell A1 at the end of the loop.

This is my code

objExc.Range("G1").Select
shts.Pictures.Insert( _
"S:\Invoicing\PAYMENT CERTIFICATES\Payment Certificate Database\Telent Logos\telent_logo.gif" _
).Select

I would also like to finish by setting the focus on to cell A1

Sid

 

[syoung4]

This is just to let you know that I have now successfully fixed my problem.

I had the pastespecial in the wrong place. I put it at the end instead of the begining where the sheets were created.

this is my code now. It pastes the logo on to all the created worksheets in position (1,7) and then finishes by selecting cells (1,1)


Do Until Rst_2.EOF
FldName = Rst_2.Fields("ContractName")
Set shts = wkbk.ActiveSheet

'puting the pastspecial of the logo here makes sure it is not selected on each sheet
'and that each sheet finishes on cell "A1"
Set Rge = shts.Rows.Cells(1, 7)
Rge.PasteSpecial 'this pastes the logo on after all other data so that it only pastes once into each workshee
Set Rge = shts.Rows.Cells(1, 1)
Rge.Select


Thank you for all your help
regards,
Sid