Upload/Link a text file to a cell using Excel VBA 2

[Moreco]

Hi everyone,

I have a big project using user-forms in Excel and one of the task is to allow the user to attach different types of files such as text and picture to a cell pre-destinated by my VBA code, so that it can be downloaded by a different user/tech. I am not sure if this can be done.

Thank you in advance for any help.

Moreco

 

[PHV]

Use the macrorecorder when doing it manually and then review the generated code.

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

 

[Moreco]

Hi PH,

I am new to programming, so thanks for the hint above. I did what you suggested, but now I am getting an error that says: "Cannot insert object". Can you please help me?

Here is my code:

[Private Sub cmdAddFile_Click()
Dim VarFile As String

  VarFile = InputBox("Enter the destination filename" & _
    Chr(10) & "(with complete path and extension):", _
    "Add a file")
  
  ActiveSheet.OLEObjects.Add(Filename:="VarFile", Link:=False, _
  DisplayAsIcon:=False).Select
  
End Sub]

I hope I pasted my code correctly! It is also my first time, so if it is wrong, I would like some help on that too.
Thanks

 

[PHV]

ActiveSheet.OLEObjects.Add(Filename:=VarFile, Link:=False, _
DisplayAsIcon:=False).Select

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

 

[Moreco]

PH,

I have tried that before and it doesn't work either. Does it work for you if you run my code?

Thanks

Moreco

 

[PHV]

It works if I run my code (xl2003), provided I type a valid pathname in the inputbox.

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

 

[Moreco]

PH,

Thank you so much. My error was that I was not writing the extension of the file.

You were extremely helpful!

Thanks

Moreco

 

[SkipVought]

check this out...

Private Sub cmdAddFile_Click()
    Dim VarFile
 [b]   
    VarFile = Application.GetOpenFilename("All Files (*.*), *.*")
    If VarFile <> False Then
 [/b]       
        ActiveSheet.OLEObjects.Add( _
            Filename:=VarFile, _
            Link:=False, _
            DisplayAsIcon:=False).Select
    End If
End Sub

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[Moreco]

Skip,

Thank you so much, this is a lot better than what I had before! You are always giving great answers! You are the best!!!!

Thank you so much!

Moreco

 

[Moreco]

Hi,

The code above works beautifuly, but if you add more than one picture file, for example, the files get placed on top of each other. Is it possible to assign a specific location for the files? If not, how can I make such that the files don't get placed on top of each other.

The other major problem happens when you attach a file other than a text file. If you add a word or excel file, the icon is the file itself. If you modified the above code to DisplayAsIcon:= True, then you get the file window but not the name of the file and its extension.

In resume, is it possible to have the files to a particular cell and have all of them look like when a txt file is attached where it comes as an icon and has the file name and its extension written on it?

Thank you for any help

Moreco

 

[electricpete]

I hope I pasted my code correctly! It is also my first time, so if it is wrong, I would like some help on that too.

I think you did something wrong, although I'm not sure what it was (did you use an ignore tag?). The syntax is [ignore]

your code here

[/ignore]
which should show up as

your code here

You can use the preview window to make sure it is displaying correctly

 

[SkipVought]

your FILES are not getting, "placed on top of each other."

Your Shape Objects are...

dim shp as shape, i as integer
i=1
for each shp in activesheet.shapes
    select case i
       case 1
       case 2
          shp.left = activesheet.shapes(i-1).left + activesheet.shapes(i-1).width
    end select
next

This positions the second shape to the right of the first shape.

You can do the same thing with the Top and Height properties to stack them one over the other.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[Moreco]

Electricpete, Thank you for your help.

Skip,

I am still having some issues. Here is my complete code:

 Private Sub cmdPhoto_Click()
Dim VarFile As Variant, Ans As Variant
Dim shp As Shape, i As Integer
    
    VarFile = Application.GetOpenFilename("All Files (*.*), *.*")
    If VarFile <> False Then
        
        ActiveSheet.OLEObjects.Add( _
            Filename:=VarFile, _
            Link:=False, _
            DisplayAsIcon:=True).Select
      
    End If
    
    Ans = MsgBox("Do you want to add another photo file?", vbYesNo)
    
    While Ans = vbYes
        VarFile = Application.GetOpenFilename("All Files (*.*), *.*")
          If VarFile <> False Then
            ActiveSheet.OLEObjects.Add( _
            Filename:=VarFile, _
            Link:=False, _
            DisplayAsIcon:=True).Select
            
        i = 1
        For Each shp In ActiveSheet.Shapes
            Select Case i
               Case 1
               Case 2
                  shp.Left = ActiveSheet.Shapes(i - 1).Left + ActiveSheet.Shapes(i - 1).Width
            End Select
            i = i + 1 ' I added this to see if it would work
        Next
           End If
           
      Ans = MsgBox("Do you want to add another photo file?", vbYesNo)
    Wend
End Sub

I tried different arrangements for the code, but nothing worked. I also added i=i+1, because without it, it wouldn't go passed Case 1.
Sorry, but I can't see why this is not working. Can you please help me again.

Thank you SO much!

 

[Moreco]

I was able to attach the files in a way that they don't get placed on top of each other, here is what I did. I hope this can help someone else:

Private Sub cmdPhoto_Click()
Dim VarFile As Variant, Ans As Variant
Dim col As Variant
    
    col = 3
    Sheet3.Cells(1, col).Select
    
    VarFile = Application.GetOpenFilename("All Files (*.*), *.*")
    If VarFile <> False Then
      ActiveSheet.OLEObjects.Add( _
        Filename:=VarFile, _
        Link:=False, _
        DisplayAsIcon:=True).Select
    End If
    
    Ans = MsgBox("Do you want to add another photo file?", vbYesNo)
    
    While Ans = vbYes
    col = col + 1
    Sheet3.Cells(1, col).Select
        VarFile = Application.GetOpenFilename("All Files (*.*), *.*")
          If VarFile <> False Then
            ActiveSheet.OLEObjects.Add( _
            Filename:=VarFile, _
            Link:=False, _
            DisplayAsIcon:=True).Select
        
           End If
           
      Ans = MsgBox("Do you want to add another photo file?", vbYesNo)
     Wend

End Sub

I still don't know how to attach a pdf or word file and have it show the name and extension like it does for txt files, so if any of you know please let me know.

Thank you so much!