Excel Macro Question

gravitywell

Beta member
Messages
2
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()
Range("A35:K69").Select
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.​
 
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:

Code:
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.
        Else
        End If
    End With

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

End Sub
 
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.
 
Back
Top Bottom