Go Back   Computer Forums > General Computing > Programming
Click Here to Login
Join Computer forums Today

Thread Tools Search this Thread Display Modes
Old 11-23-2011, 02:06 AM   #1
Beta Member
Join Date: Nov 2011
Posts: 2
Default Excel Macro Question

Hey, I dont know much about macros or about scripting, but I've got a project to do for work and I looked up some codes and spliced them togeather to fit my specific purpose.
I'm pulling pictures out of folders and sticking them in an excel file. You'll understand what I mean when you read the script (if you know what you're talking about at least lol)

here's what I have so far:

Private Sub CommandButton17_Click()
Dim subfolder As String
subfolder = Range("A7")
Dim picname As String
picname = Range("B9")
ActiveSheet.Pictures.Insert(ThisWorkbook.Path & ("\" & subfolder & "\" & picname & ".jpg")).Select
Selection.ShapeRange.ScaleWidth 0.93, msoFalse, msoScaleFromTopLeft
Selection.ShapeRange.ScaleHeight 0.88, msoFalse, msoScaleFromTopLeft
ActiveWindow.SmallScroll Down:=8
End Sub

Pretty simple. "ThisWorkbook.Path gives me the path to the folder I want (the folder my excel file is in), and ("\" & subfolder & "\" & picname & ".jpg") give me the extentions to look into the folder and insert the pick I want. The latter should fit onto the former and make one address, but I'm having trouble with the connection point where the & is located. Any help would be much appreciated.

Also, I would like to create another button to open "subfolder" (whichever is named in the cell) in the browser in a new window, but have no idea how to tackle that task...

One more thing, there is no reason to tell me to just begin with the actual source address instead of "ThisWorkbook.Path... that's how it was before. I'm trying to create a floating file referance so I can use the file on multiple computers.

gravitywell is offline   Reply With Quote
Old 11-23-2011, 07:56 AM   #2
Fully Optimized
strollin's Avatar
Join Date: Mar 2011
Location: USA
Posts: 2,520
Default Re: Excel Macro Question

I have no idea what you mean by this statement: "The latter should fit onto the former and make one address, but I'm having trouble with the connection point where the & is located." I put your code into an Excel sheet and it worked fine as-is. I put the subfolder name in cell A7 and the pic name in cell B9. The pic is in a subfolder below the folder the Excel sheet is in. Your code loaded the picture fine.

Opening a subfolder to select a file from it is done with the FileDialog object. Here's some example code from the MS site that should get you started:

Sub Main()

    'Declare a variable as a FileDialog object.
    Dim fd As FileDialog

    'Create a FileDialog object as a File Picker dialog box.
    Set fd = Application.FileDialog(msoFileDialogFilePicker)

    'Declare a variable to contain the path
    'of each selected item. Even though the path is a String,
    'the variable must be a Variant because For Each...Next
    'routines only work with Variants and Objects.
    Dim vrtSelectedItem As Variant

    'Use a With...End With block to reference the FileDialog object.
    With fd

        'Use the Show method to display the File Picker dialog box
        'The user pressed the action button.
        If .Show = -1 Then

            'Step through the FileDialogSelectedItems collection.
            For Each vrtSelectedItem In .SelectedItems

                'vrtSelectedItem contains the path of each selected item.
                'Here use any file I/O functions you want on the path.
                'This example simply displays the path in a message box.
                MsgBox "The path is: " & vrtSelectedItem

            Next vrtSelectedItem
        'The user pressed Cancel.
        End If
    End With

    'Set the object variable to Nothing.
    Set fd = Nothing

End Sub

strollin is offline   Reply With Quote
Old 11-23-2011, 11:59 PM   #3
Beta Member
Join Date: Nov 2011
Posts: 2
Default Re: Excel Macro Question

Oh! I realized what I did. Thanks! lol I was so focused on the two folders that changed that I forgot that they were both located in a folder that shares the same location as the excel file. I just had to add the \SCAN SHEET COPIES before the "\" & subfolder &.... and it worked.
gravitywell is offline   Reply With Quote

Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are Off

All times are GMT -5. The time now is 10:24 AM.

Powered by vBulletin® Version 3.8.8 Beta 4
Copyright ©2000 - 2020, vBulletin Solutions, Inc.
Search Engine Friendly URLs by vBSEO 3.6.0