Results 1 to 8 of 8

Deleting an image

Threaded View

  1. #1
    Forum Contributor
    Join Date
    06-12-2007
    Posts
    144

    Deleting an image

    I have created a macro that inserts some data and a drawing into a template and then saves it with a new name.

    But I am having a problem deleting the old drawing.

    Could the problem be that my drawing is a "shape" ?
    Or not really in the cell M1 as I intended?
    Or could I give the drawing a name when I insert it, then delete it by name next time around?

    Unfortunately my workbook is 150k so I cant upload it here, but I could email it to anyone who is interested.

    My code looks like this:

    Sub autofill1()
    
    Application.ScreenUpdating = False
    
    On Error GoTo error1:
    
    ' Prepare variables
    Dim p As Object, t As Double, l As Double, w As Double, h As Double
    Dim ProductCode
    Dim MaterialCode
    Dim DrawingCode
    Dim ProductFilename
    Dim NewFileName
    
    ' Remove existing data
    Sheets("Production Sheet").Activate
    Cells(5, 3) = ""
    Cells(9, 10) = ""
    Cells(5, 7) = ""
    
    
    'Remove old drawing
    Cells(1, "M") = ""
    'or
    Range("M1:W40").Select
    Selection.ClearContents
    
    
    
    ' Input the Product number
    ProductCode = InputBox("Please enter the Product code")
    If ProductCode = "" Then
      MsgBox ("Error - No Product Code")
      Exit Sub
    End If
    
    'Lookup the required info
    On Error GoTo error1:
    Sheets("Product List").Activate
    MaterialCode = Application.VLookup(ProductCode, Range("A1:M800"), 4, False)
    DrawingCode = Application.VLookup(ProductCode, Range("A1:M800"), 11, False)
    ProductFilename = "M:\Mining\Manufacturing\Production Control\BLADE PRODUCTS FOLDERS\VERTICAL\" & ProductCode & ".gif"
    NewFileName = "M:\Mining\Manufacturing\Production Control\Blade Production Sheets\" & ProductCode & ".xls"
    
    ' Put the data on the template
    Sheets("Production Sheet").Activate
    Cells(5, 3) = ProductCode
    Cells(9, 10) = MaterialCode
    Cells(5, 7) = DrawingCode
    
    ' Insert the drawing and resize it
    On Error GoTo nopic:
        Cells(1, "M").Select
        Set p = ActiveSheet.Pictures.Insert(ProductFilename)
        With p
            .Width = 505
            .Height = 795
        End With
       Set p = Nothing
    
    Application.ScreenUpdating = True
    
    ' Save As the product code
    On Error GoTo nosave:
        ActiveWorkbook.SaveAs Filename:=NewFileName _
            , FileFormat:=xlNormal, Password:="", WriteResPassword:="", _
            ReadOnlyRecommended:=False, CreateBackup:=False
    
    'End of main subroutine
    Exit Sub
    
    
    
    'Error Handling
    
    nopic:
    MsgBox ("No drawing was found. This Production Sheet was not saved.")
    Exit Sub
    
    nosave:
    MsgBox ("The Production Sheet could not be saved.")
    Exit Sub
    
    error1:
    MsgBox ("There was an error with the Product Code or WorkBook. This Production Sheet was not saved.")
    Exit Sub
    
    End Sub
    Last edited by treva26; 07-17-2007 at 04:17 AM.

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1