Add vb code to a new workbook using a macro

**Alagard** · 12-15-2010, 12:55 PM

    ChDir "L:\tapeaudit"
    Workbooks.OpenText Filename:="L:\tapeaudit\tlmsbiwk.txt", Origin:=437, _
        StartRow:=1, DataType:=xlDelimited, TextQualifier:=xlDoubleQuote, _
        ConsecutiveDelimiter:=False, Tab:=False, Semicolon:=False, Comma:=True _
        , Space:=False, Other:=False, FieldInfo:=Array(Array(1, 9), Array(2, 2), _
        Array(3, 1)), TrailingMinusNumbers:=True
    Range("D1").Select
    ActiveCell.FormulaR1C1 = "=IF(COUNTIF(R1C2:R2000C2,RC[-1]),"""",""NOPE"")"
    Range("D1").Select
    Selection.AutoFill Destination:=Range("D1:D2000"), Type:=xlFillDefault
    Range("D1:D2000").Select
    ActiveWorkbook.SaveAs Filename:="L:\tapeaudit\tlmsbiwk.xls", FileFormat:= _
        xlNormal, Password:="", WriteResPassword:="", ReadOnlyRecommended:=False _
        , CreateBackup:=False
End Sub

This is the code I am using now. I am creating a new workbook with the data from this text file and inputting the formulas that I need. The issue that I am having is that I want to be able to add the code below to the new sheet on it's creation and I have not been able to get it to work. Thanks for your help.

Option Explicit

Private Declare Function sndPlaySound32 Lib "winmm.dll" _
    Alias "sndPlaySoundA" (ByVal lpszSoundName As String, _
    ByVal uFlags As Long) As Long

Private Sub Worksheet_Change(ByVal Target As Range)
Dim Cell As Range

    If Not Intersect(Target, Range("B:B")) Is Nothing Then
        For Each Cell In Intersect(Target, Range("B:B"))
            If Range("D" & Cell.Row) = "NOPE" Then
                Call sndPlaySound32("C:\windows\media\notify.wav", 1)
                Exit Sub
            End If
        Next Cell
    End If

End Sub

**Leith Ross** · 12-15-2010, 09:18 PM

Hello Alagard,

This macro will copy the contents of a text file to the active sheet code module of the workbook. You will need to copy your macro and save it as a text file. Change the file path in the macro to this location. Call the macro before you close the workbook and save it.

Sub AddMacroToWorkbook()

  Dim MacroFile As String
  Dim TextFile As Object
  Dim VBmod As Object
  Dim VBproj As Object
  Dim Wkb As Workbook
  
    MacroFile = "C:\Sound Macro.txt"     'Change this path and file name to match your file
    Set Wkb = ActiveWorkbook
      
      Set VBproj = Wkb.VBProject
      Set VBmod = VBproj.VBComponents(ActiveSheet.Name).CodeModule
    
      VBmod.AddFromFile MacroFile
        
End Sub

**Alagard** · 12-16-2010, 01:17 PM

I run into an issue here. I am creating the new worksheet in my macro and filling it with data and formulas at that step. Looking at this, can I incorporate this into the code at the beginning to do this all in one step?

**Leith Ross** · 12-16-2010, 01:25 PM

Hello Alagard,

I have added the macro call to your code. You will still need to copy and paste your sound macro into Notepad and save it. Once you have done that, you will need to change the path in the macro I wrote to your file's location.

    ChDir "L:\tapeaudit"
    Workbooks.OpenText Filename:="L:\tapeaudit\tlmsbiwk.txt", Origin:=437, _
        StartRow:=1, DataType:=xlDelimited, TextQualifier:=xlDoubleQuote, _
        ConsecutiveDelimiter:=False, Tab:=False, Semicolon:=False, Comma:=True _
        , Space:=False, Other:=False, FieldInfo:=Array(Array(1, 9), Array(2, 2), _
        Array(3, 1)), TrailingMinusNumbers:=True
    AddMacroToWorkbook
    Range("D1").Select
    ActiveCell.FormulaR1C1 = "=IF(COUNTIF(R1C2:R2000C2,RC[-1]),"""",""NOPE"")"
    Range("D1").Select
    Selection.AutoFill Destination:=Range("D1:D2000"), Type:=xlFillDefault
    Range("D1:D2000").Select
    ActiveWorkbook.SaveAs Filename:="L:\tapeaudit\tlmsbiwk.xls", FileFormat:= _
        xlNormal, Password:="", WriteResPassword:="", ReadOnlyRecommended:=False _
        , CreateBackup:=False
End Sub

**Alagard** · 12-16-2010, 01:37 PM

I get an error when I try to run that.

Programmatic access to Visual Basic Project is not trusted

the debugger then highlights this line
Set VBproj = Wkb.VBProject

Thanks for the help.

**Leith Ross** · 12-16-2010, 01:43 PM

Hello Alagard,

If you are running this code on you r own machine, you should not be getting this error. However, if you are attempting to change another workbook on someone else's machine you will.

**Alagard** · 12-16-2010, 01:53 PM

sure the workbook and all of the files are on a shared drive.

**Alagard** · 12-16-2010, 02:27 PM

So I checked the box for trusted access and that step goes okay however now the next step gives an error

Run-time error '9':
Subscript out of range
Set VBmod = VBproj.VBComponents(ActiveSheet.Name).CodeModule

**Leith Ross** · 12-16-2010, 06:05 PM

Hello Alagard,

The error indicates the name of the module was not found. Which doesn't make sense because a workbook must have at least one worksheet in it and it is looking for the Active Sheet. Can you post the workbook for review?