Master spreadsheet

[Nawtiehope]

Hello,
I have a master spreadsheet with a lot of VBA code I learned from MrExcel (thank you very much). I want to copy the spreadsheet for a new project. Some of the spreadsheet code stays within the new spreadsheet even after it has been renamed. Some codes does not. Some of the VBA refer back to the master sheet. How do I have a lot of code that will stay within a specific spreadsheet no matter what I name it?

 

[ashakantasharma]

I think all VBA code which have outside path needs to be simplfied and made internal paths to avoid losing them...

 

[Nawtiehope]

I think all VBA code which have outside path needs to be simplfied and made internal paths to avoid losing them...

The path would be internal for the master spreadsheet. But when the spreadsheet is copied and the file name changed then some of code maintains the same path, back to the master spreadsheet. I hope I'm saying that right.

 

[Nawtiehope]

Sub Button12_Click()

    Dim objOutlook As Object, objMail As Object
   
    Set objOutlook = CreateObject(Class:="Outlook.Application")
    Set objMail = objOutlook.createitem(0)
   
    With objMail
        .TO = Range("D1")
        .Subject = "PROJECT WAIVER/RELEASE ATTACHED FOR:"
        .HTMLBody = fncRangeToHtml("Waiver_WkSht", "A51:I85")
        .display
       
    End With
   
    Set objMail = Nothing
    Set objOutlook = Nothing
   
End Sub

 
Private Function fncRangeToHtml( _
    strWorksheetName As String, _
    strRangeAddress As String) As String
   
    Dim objFilesytem As Object, objTextstream As Object, objShape As Shape
    Dim strFilename As String, strTempText As String
    Dim blnRangeContainsShapes As Boolean
   
    strFilename = Environ$("temp") & "\" & _
        Format(Now, "dd-mm-yy_h-mm-ss") & ".htm"
       
    ThisWorkbook.PublishObjects.Add( _
        SourceType:=xlSourceRange, _
        FileName:=strFilename, _
        Sheet:=strWorksheetName, _
        Source:=strRangeAddress, _
        HtmlType:=xlHtmlStatic).Publish True
       
    Set objFilesytem = CreateObject("Scripting.FileSystemObject")
    Set objTextstream = objFilesytem.GetFile(strFilename).OpenAsTextStream(1, -2)
    strTempText = objTextstream.ReadAll
    objTextstream.Close
   
    For Each objShape In Worksheets(strWorksheetName).Shapes
        If Not Intersect(objShape.TopLeftCell, Worksheets( _
            strWorksheetName).Range(strRangeAddress)) Is Nothing Then
           
            blnRangeContainsShapes = True
            Exit For
           
        End If
    Next
   
    If blnRangeContainsShapes Then _
        strTempText = fncConvertPictureToMail(strTempText, Worksheets(strWorksheetName))
   
    fncRangeToHtml = strTempText
    fncRangeToHtml = Replace(fncRangeToHtml, "align=center x:publishsource=", "align=left x:publishsource=")
   
    Set objTextstream = Nothing
    Set objFilesytem = Nothing
   
    Kill strFilename
   
End Function

Public Function fncConvertPictureToMail(strTempText As String, objWorksheet As Worksheet) As String
      
    Dim strTemp As String
    Dim lngPathLeft As Long
   
    lngPathLeft = InStr(1, strTempText, HTM_START)
   
    strTemp = Mid$(strTempText, lngPathLeft, InStr(lngPathLeft, strTempText, ">") - lngPathLeft)
    strTemp = Replace(strTemp, HTM_START & Chr$(34), "")
    strTemp = Replace(strTemp, HTM_END & Chr$(34), "")
    strTemp = strTemp & "/"
   
    strTempText = Replace(strTempText, strTemp, Environ$("temp") & "\" & strTemp)
    fncConvertPictureToMail = strTempText
   
End Function

  End With

End Sub