Template Help

[fterry]

I have created a template for users to import some data. The user opens a form to select which files to import. I created a menu item for the form and the user clicks on the button to open the form. The macro that is attached to the button is simply frmMain.show.

My problem is that when the template is used once and is saved as another file the template is retaining the name of the previously saved file. When the template is re-opened to use again and the button to open the form is pressed the previous file is opened. It's very strange. I can not figure out where Excel is retaining the name of the file nor can I figure out why it is opening another file on the click of the button.

Any suggestions would be appreciated.

 

[fterry]

Okay, after the weekend I am refreshed and I figured out what is happening. The problem is I don't know how to fix it.

The macro that is run when the button is clicked is saved in the ThisWorkbook location in the template file. Once the template is used and a Save As command is performed the reference to the macro changes from ThisWorkbook to the saved file name. Therefore, when the template is used again it is looking in the previously saved file for the macro and it is opening it.

Why is the template saving the file name when it shouldn't be updated? Am I putting the macro in the wrong location to be run?

Thanks.

I have created a template for users to import some data. The user opens a form to select which files to import. I created a menu item for the form and the user clicks on the button to open the form. The macro that is attached to the button is simply frmMain.show.

My problem is that when the template is used once and is saved as another file the template is retaining the name of the previously saved file. When the template is re-opened to use again and the button to open the form is pressed the previous file is opened. It's very strange. I can not figure out where Excel is retaining the name of the file nor can I figure out why it is opening another file on the click of the button.

Any suggestions would be appreciated.

 

[phantom1975]

As always, it's nice to see folks from Arizona here! I feel all warm and fuzzy. Probably because I really need to shave my back! At any rate, you really ought to post the code that is in your ThisWorkbook module (please limit it to only the relevant lines.) I would also recommend that you post the macro (if any) that copies the workbook.

 

[fterry]

Okay. Here is the code from ThisWorkbook that is run when the button is clicked:

Private Sub OpenForm()
'
frmMain.Show
'
End Sub

The only other code in this location is using the Workbook_Open and Workbook_BeforeClose commands and they just change the toolbar visibility.

The rest of my coding takes place on the user form. This is the routine that copies the data from the other files:
Private Sub cmdImport_Click()
'for each file selected in the list box copy over the summary sheet - PayrollHours range
Dim sFileName As String, sEmpName As String
Dim lCurList As ListBox
Dim vItem As Variant, vList As Variant
Dim iCount As Integer, iTotal As Integer, iNameRows As Integer
Dim sPayrollFile As String
Dim sRange As String, sDir As String
Dim iRow As Integer, iSel As Integer
Dim bSelected As Boolean

sPayrollFile = ActiveWorkbook.Name
ActiveWorkbook.Sheets("Payroll").Activate
vList = lstFiles
ChDir Me.txtDirectory.Text
iTotal = lstFiles.ListCount - 1

sDir = Me.txtDirectory
iRow = ActiveCell.Row
sRange = "A" & iRow
Range(sRange).Activate
For iSel = 0 To iTotal
If lstFiles.Selected(iSel) = True Then
bSelected = True
End If
If bSelected = True Then Exit For
Next

If bSelected = False Then
MsgBox "You must select at least one file to import.", vbOKOnly
Exit Sub
End If

For iCount = 0 To iTotal
If lstFiles.Selected(iCount) = True Then
Application.DisplayAlerts = False
sFileName = lstFiles.List(iCount)
Workbooks.Open sDir & "\" & sFileName
Workbooks(sFileName).Activate
sEmpName = Range("F1").Value
Range("PayrollHours").Copy
Workbooks(sPayrollFile).Activate
Selection.PasteSpecial Paste:=xlValues
For iNameRows = iRow To (iRow + 33)
Range("G" & iNameRows).Activate
ActiveCell.Value = sEmpName
Next
iRow = iRow + 34
sRange = "A" & iRow
Range(sRange).Activate
Workbooks(sFileName).Close False
End If
Next
Application.DisplayAlerts = True
'send to sub to delete the 0 hour rows
DeleteZeros

Thanks for any suggestions.