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"
'(Step 2) Open Template
Set wkbTemplate = Workbooks.Open(PathSource & strTemplate, 0, False) 'Not read only
Set shtTemplate = wkbTemplate.Worksheets("QtrlyFinancial"
'(Step 3) Tx data
intRowStart = InputBox("Enter the Starting Row Number ie: 2", "Start Row", "2"
intRowEnd = InputBox("Enter the Ending Row Number", "Ending Row", "3"
For intRowNbr = RecordStart To RecordEnd
' obtaining info for the output filename
strRowNbr = Format(intRowNbr, "000"
'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"
.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)![[thumbsup] [thumbsup] [thumbsup]](/data/assets/smilies/thumbsup.gif)
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"
'(Step 2) Open Template
Set wkbTemplate = Workbooks.Open(PathSource & strTemplate, 0, False) 'Not read only
Set shtTemplate = wkbTemplate.Worksheets("QtrlyFinancial"
'(Step 3) Tx data
intRowStart = InputBox("Enter the Starting Row Number ie: 2", "Start Row", "2"
intRowEnd = InputBox("Enter the Ending Row Number", "Ending Row", "3"
For intRowNbr = RecordStart To RecordEnd
' obtaining info for the output filename
strRowNbr = Format(intRowNbr, "000"
'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"
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)
![[thumbsup] [thumbsup] [thumbsup]](/data/assets/smilies/thumbsup.gif)