VBA Excel: DIR function - I'm trying to find a file.

[Thingol]

Hi, I'm relatively new to VBA, and I hope someone here may be able to help me with this.

I'm trying to find the location of a file (a bitmap) in Excel. I want to put the bitmap on a commandbutton. It would be simple if the program was going to be used on only my computer. However, it won't be, so I can never be sure in which directory the bitmap I'm looking for will be. I think the DIR function should be able to look up the file (including its path), but I can't figure out how to pull it off. I've tried something like:

Dim Bitmap
Bitmap = Dir("Bitmap.bmp&quot
msgbox "Location of bitmap: " & CStr(Bitmap)

The msgbox doesn't show a string with the directory where bitmap.bmp is located. Instead it shows "".

What am I doing wrong, and what should I do?

 

[SkipVought]

Hi,
Try using...

fname = Application.GetOpenFilename("BMP Files (*.bmp), *.bmp")

Hope this helps Skip,
metzgsk@voughtaircraft.com

 

[Thingol]

Even though that would work, It would be nice if the user wouldn't have to indicate the location of the file. Is it possible to find the file without the user telling the application where the file is located? The file would be in the same directory as the application itself.

 

[Thingol]

Or did I misunderstand the GetOpenFilename command?

 

[rekclaw]

Your dir function looks right. If you are looking at the same directory as the workbook, shouldn't it be something like:

BitMap = dir(ThisWorkbook.Path & "\bitmap.bmp&quot

 

[SkipVought]

You can insert ActiveWorkbook.Path in the default of the GetOpenFilename.

If you are sure of the path, then rekclaw's solution is sufficient. Skip,
metzgsk@voughtaircraft.com

 

[Thingol]

Thanks, this did work out the problem of finding the directory. However, I bumped into another problem.

I tried to apply the picture to a new msoControlButton like this:


'Adding the new button to a commandbar I made earlier:
Set ctlNewButton = Application.CommandBars("NewCommandBar&quot.Controls.Add(Type:=msoControlButton, ID:=2950, before:=1)

'Finding the directory of the picture:
dim Bitmap
Bitmap = CStr(ThisWorkbook.Path)

'Setting the properties of the button:
With ctlNewButton
.Width = "130"
.Height = "52"
.Picture = LoadPicture(Bitmap & "\Bitmap.bmp&quot
End With


The height and width properties are the exact same amount of pixels as the picture has. However, I get an error message (this is a translation from Dutch):

Error 438
This property or method is not supported by this object.

Doesn't the button have a picture property? Or am I doing something wrong? If it doesn't have a Picture property, then how can I set the Icon of the button?

Thanks.
Martijn.

 

[SkipVought]

width & height are NUMERIC not TEXT...

therefore, remove the QUOTES around the values

With ctlNewButton
    .Width = 130
    .Height = 52
    .Picture = LoadPicture(Bitmap & "\Bitmap.bmp")
End With

Hope this helps Skip,
metzgsk@voughtaircraft.com

 

[Thingol]

No, that wasn't what was going wrong. It shouldn't matter whether I use the QUOTES or not fot the values of width and height (Excel seems to understand it bothways). The line that causes the problem is:

.Picture = LoadPicture(Bitmap & "\Bitmap.bmp&quot

I think the .picture property isn't valid for ctlNewButton, except I don't know if there is any other way to assign a bitmap to a controlbarbutton.