Tx Data Wkbk to Wkbk (like Mail Merge) - RT Error 1004

[zollo9999]

Hi (I'm running XL-97 on Win NT)

I could describe my goal to be something like doing a mail merge but I need to transfer the data from one workbook to another workbook (which I then SaveCopyAs with a new name).

I'm getting an error in my code at the line in blue and bold font shown below

The error message is:
Runtime Error 1004
Method 'Range' of Object '_Worksheet' failed.

I can't see what is wrong with the way I've accessed the Range of the worksheet object.

Also clicking "Help" results in my help file saying:
"The topic does not exist" "Contact my vendor to get an updated help file (129)" So far I haven't found anything on the internet (regarding the error or a new help file). Do I really need a new help file?

The end result will be 160 individual wkbks which are emailed to my clients, only once every year or two (thankfully). They use the wkbk to report back to me up to 4 times a year.

My parmeters mean that I can't build an e-commerce solution.


Here is my code.
(This is a VBA-Macro. I haven't placed the code in a module)
Thanks in advance, any help would be appreciated.


Sub CreateForm()

' VARIABLES for Files
Dim PathSource As String 'Path to data & template
Dim PathOp As String 'Path to new workbooks

Dim strTemplate As String
Dim strDatafile As String
Dim strNewWorkbookName As String

Dim wkbDatafile As Workbook
Dim wkbTemplate As Workbook
Dim shtDatafile As Worksheet
Dim shtTemplate As Worksheet

PathSource = "S:\~\Finance\"
PathOp = PathSource
PathSource = PathSource & "MergeForm\"
PathOp = PathOp & "MergeOutput\"
strDatafile = "SvcQtrlyDataMaster.xls"
strTemplate = "SvcQtrlyFinTemplate.xls"

' Variables for the Processing of a Range of Rows/Records
Dim intRowStart As Integer
Dim intRowEnd As Integer
' Variable to Increment through the Rows
Dim intRowNbr As Integer
Dim strRowNbr As String ' Used for the Output File Name

' Constants for Record Columns
' (Would an array be easier?)
Const strProgramCodeCol As String = "A"
Const strFileIdCol As String = "B"
Const strPimsIdCol As String = "C"
Const strOrgNameCol As String = "D"
Const strProjectNameCol As String = "E"
Const strAulCol As String = "F"
Const strRegionCol As String = "G"

' Variables for VALUES from obtained from datafile cells
Dim strRegionVal As String
Dim strProgramCodeVal As String
Dim strFileIdVal As String
Dim strPimsIdVal As String
Dim strOrgNameVal As String
Dim strProjectNameVal As String
Dim intAulVal As Integer

Application.ScreenUpdating = False

'(Step 1) Open Datafile
Set wkbDatafile = Workbooks.Open(PathSource & strDatafile, 0, True) 'Read only
Set shtDatafile = wkbDatafile.Worksheets("Svcs-Master&quot

'(Step 2) Open Template
Set wkbTemplate = Workbooks.Open(PathSource & strTemplate, 0, False) 'Not read only
Set shtTemplate = wkbTemplate.Worksheets("QtrlyFinancial&quot

'(Step 3) Tx data

intRowStart = InputBox("Enter the Starting Row Number ie: 2", "Start Row", "2&quot
intRowEnd = InputBox("Enter the Ending Row Number", "Ending Row", "3&quot

For intRowNbr = RecordStart To RecordEnd

' obtaining info for the output filename
strRowNbr = Format(intRowNbr, "000&quot

'THIS IS WHERE THE ERROR OCCURS !!!!

strProgramCodeVal = shtDatafile.Range(strProgramCodeCol & strRowNbr).Value

strFileIdVal = wkbDatafile.Range(strFileIdCol & strRowNbr).Value
strRegionVal = wkbDatafile.Range(strRegionCol & strRowNbr).Value

' transferring info to template wkbook
wkbTemplate.Range(M5).Value = strProgramCodeVal
wkbTemplate.Range(M7).Value = strFileIdVal
wkbTemplate.Range(M9).Value = wkbDatafile.Worksheets("Svcs-Master&quot.Range(strPimsIdCol & strRowNbr).Value
wkbTemplate.Range(C5).Value = wkbDatafile.Range(strOrgNameCol & strRowNbr).Value
wkbTemplate.Range(C7).Value = wkbDatafile.Range(strProjectNameCol & strRowNbr).Value
wkbTemplate.Range(M19).Value = wkbDatafile.Range(strAulCol & strRowNbr).Value
wkbTemplate.Range(M11).Value = strRegionVal

' Step 4
' Create New Workbook name.
' Example strNewWorkbookName = "NEN-CRC-001.xls"

strNewWorkbookName = strRegionVal & "-" & strProgramCodeVal & "-" & strFileIdVal & ".xls"

strNewWorkbookName = PathOp & strNewWorkbookName
wkbTemplate.SaveCopyAs (strNewWorkbookName)

Next intRowNbr

Application.ScreenUpdating = True

End Sub


Please excuse my ignorance. I have a poor conceptual grasp of the Excel Object Model. I'm OK with VBA with MS-Access, but I'm trying to learn Excel VBA from the help files and object browser.

Thanks to everyone that can provide input.
If I can't get a simple fix, I'll grab one of the Wrox books on this topic.

Zollo9999 A+
(Very Part-Time Programmer)

 

[dan1967]

You might want to try the following for that line:

strProgramCodeVal = Sheets(shtDatafile).Range(strProgramCodeCol & strRowNbr).Value

You were entering the variable that holds the sheet name, without letting Excel know this is a sheet name.

Dan.

 

[zollo9999]

Thanks Dan.
I'll give it a go.
I thought that as 'shtDatafile' was already defined as a worksheet that that would be a sufficent reference for XL to figure it out.

I take it that "Sheets" is the collection of all sheets which contains my worksheet.

I've seen this in passing but my XL-VBA help file shows the object heirarchy to be: Workbooks -> Worksheets.

If you are correct, it seems that the object heirarchy should be: Workbooks -> Sheets.

I'll be back in a few hours and let you know how it goes.

regards

Zollo9999 A+
(Very Part-Time Programmer)

 

[dan1967]

The object WorkSheets works just as well. They both return the same values in this scenario.

Dan.