VBA Excel to open image below last row with data

[gipsyfkls14]

Following my vba practices, I am writing a code to open in a worksheet a picture below the last row with data.

The code reads Range("A2").value and open the image with the same value in StudentPhotos carpet
My code sends the cursor below the last row with data where I need to open the image.

Please Login or Register  to view this content.

My code opens the image but, as I said, I need VBA displays the image from the empty cell.

This is my code:

Please Login or Register  to view this content.

On the other hand, in myDir I am trying to use the relative path :

Please Login or Register  to view this content.

but, VBA displays "Invalid Qualifier Error"

Could I ask another pair of eyes to take a look and offer some advice?

Thank you for taking the time in helping me.

[Keebellah]

Since you code hard paths in you vba I'm not going into that.
But what you can do do is that once you have the form on screen, make sure you calculated the last filled row
You select the shape and set the top of it that range's top and also you multiply the number of rows by either a default row height or check each row and add them together, then you know where the top of that last cell is.
You also have top take into account the ribbon height

This may sound like abra-cadabra to you but once you think it over you'll see what I'm trying to explain.
The invalid qualifier is probably the shape's name since every time you insert a new shape the name is Shape 1 or Picture 1 then 2 then 3 etc.

You have to give that shape a specific name the moment you insert it and then you can move it around at will (with vba code)

Just holler and attach a file (with macros and NO hard-coded paths) and I'll (or someone else) will help you

[gipsyfkls14]

I really appreciate your reply
I attach my excel file (with macro and NO hard-coded paths) and the carpet with students photos.

I hope you can help with this vba project

[Vraag en antwoord]

CurrentProject.Path is VBA for Access. For Excel it is Thisworkbook.path

Please Login or Register  to view this content.

[gipsyfkls14]

Vraag en antwoord.
Your code is smaller than mine, but yours works and for sure it is more robust. Thanks.

I am practising VBA for excel and for MS Access.
In fact, I try to program this project from VBA for MS Access.
In my DB the code must reads the Range("A2").value in a textbox "txtstudent".
The query is exported to excel and the query is open in the worksheet
However, I have been unable to open the picture in the exported query

I would like to know if I can post here my vba code from access to open the picture in the worksheet.

In my code I already declare:
Dim FileName As String
Dim xl As Object, wb as Object
Dim sExcelWB As String
Dim ws As Worksheet.

At first sight, I see my problems in r and set r.
I think my code to open the image could begin as:

Please Login or Register  to view this content.

I really appreciated your opinion with concerning with my MS Access Code

Thanks

[Keebellah]

A question, do you want to display the picture permanently of only the picture of the selected student?
So if you select another student you want to see only the other picture?

[gipsyfkls14]

Keebellah. Thanks for your reply.
If I select another student I want to see his picture.
I have a picture per student.

In my MS Access Db I have a Form with a textbox to type the student_id value (id_number). Then, the command button exports the query to excel.
With the query in the worksheet, I have a VBA code that read the student_id (Range("A2") and displays the picture.
As I said, I would like to do with VBA MS Access:

Export the query to Excel
Open the image in the Worksheet.

I really appreciate your help.

[Keebellah]

I understand but if you want to do this in Access why use Excel?
I have attached a modified file, click on the student Id and it will show the picture.

It's quite a small world. I gey up in Costa Rica (1950-1965) and lived in Turrialba, 1962 - 1965. Attende el Liceo Clodomiro Picado (one year) then went back to school in San José (San Pedro) but travelled back and forth every Monday-Friday
I lived on the IICA campus (now CATIE)

[Keebellah]

If you check my avatar you will see the CR flag top middle

[gipsyfkls14]

Keebellah
My problem is with MS Access VBA.
I already have a code to export a query to excel.

Why I am using MS Access?
Because I need a VBA code from MS Access to export the query to excel and open the student's picture

I have been at least 6 days looking for code lines in MS Access to open the student picture in Excel. But, I have not found it yet.

Therefore, I wrote the code in Excel to show the picture, hoping to see the code and adapt it in MS Access VBA. That's the thing that I have not achieved.

My MS Access Db opens with the Form. In the textbox, you type 12018 and click Search Button, then, Excel open the query in the Worksheet.

I am looking for than MS Access VBA exports at a time the query and open the worksheet with the student's picture.


I'm hopeful that you can help me.

Saludos

[gipsyfkls14]

Keebellah. Yes, I notice my flag in your Avatar.
I am really happy to hear that you lived in Turrialba at IICA and attended our IET.

In the 1961-1968 I had some school friend's at IICA campus.

In 1978 two Dutch students one after the other were attended a Plant Genetic Resources training course at CATIE. They were living with us at my home.

Saludos

[Keebellah]

So if you can see the picture in Access, you do not need Excel?
Is that what you mean?

[gipsyfkls14]

Keebellah.
Yes. I need all from MS Access: export the query and open the picture
Having in mind the following:
Excel vba reads Range("A2") to open the picture.
Access VBA reads txtstudent_id (user types) to export the query and opens the picture.

This is my Access VBA Code and at the end are the Excel VBA code lines I need to write correctly within the MS Access code.

Please Login or Register  to view this content.

I really appreciate your help.

[Keebellah]

Here's the attachment.
Some minor modifications and s great VBA Access challenge
Are you 'Tico'?

[gipsyfkls14]

Keebellah. Thanks.
I delayed my reply because the link did not open Excel Forum Web Page.

The MS Access Form looks great showing at first sight student's picture and with the button showing the query will be exported to Excel.

Now, anticipating student's quantity increase, I'm going to back up your MS Access File to use a textbox to enter the student code.
I hope I can be able to correctly modify the MS Access's VBA code. I let you know.

Yes, I am Tico.
Did you know that Turrialba Volcano is erupting?

Cheers

[Keebellah]

Okay, let me know if you get stuck.
No, I didn't, I remember the Irazú in '63, I went up there then on my 'motocicleta' .
With friends I hiked from the Irazú to the Turrialba, sleeping at ranches. I was agreat time, long before the 'ecological' tourism started.
I still have plans to go back and visit, and show my family where I grew up, but when ... I don't know yet.
I still have friends form my youth that I'm in contact with that are still living there, los altos de Gudalupe, Escasú, etc..
I made my first plane travel from La Sabana to Pto. Limón with a DC3 de LACSA
How time flies.

[Keebellah]

Minor cosmetic correction.
If not photo is found the message is shown that there is no photo present, on the Form as well as in the exported worksheet