Please help with a slight problem automating a process for my user. Currently, with one click of an icon, they can open a text file ftp'd from the Mainframe, have it put into
excel columns, and have it "made attractive" with text formats and colors. There is one part that I cant automate, though, involving a function.
The auto open macro starts like this:
Attribute VB_Name = "AK3_Recv"
Sub Autpen()
Attribute Autpen.VB_Description = "Macro recorded 11/01/2001 by John"
'Attribute Autpen.VB_ProcData.VB_Invoke_Func = " \n14"
ChDir "I:\FTP root"
Workbooks.OpenText Filename:="I:\FTP root\Employer_Addresses\Lisa.txt", Origin:=xlWindows, _
StartRow:=1, DataType:=xlFixedWidth, FieldInfo:=Array(Array(0, 2), Array(1, _
<...more positioning directives...>_
Array(209, 1), Array(216, 2), Array(226, 2), Array(240, 1), Array(243, 9))
Columns("A:A".EntireColumn.AutoFit
<...more formatting directives...>
<... save spreadsheet to LAN location...>
The last thing that happens is a homemade function, "IdNoPr" invocation gets placed
in the first available column (AA), and propagated down to the end:
ActiveCell.FormulaR1C1 = "=IdNoPr(RC25)"
Range("AA2".Select
Selection.Copy
Range("AA3".Select
Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select
Selection.PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
The placing of this function works fine. The problem is that, although the function
lives in personal.xls, which is always open with excel, it is not known to the current
(new) spreadsheet. This gives a NAME? error. It is necessary each time new data is
brought in to manually export the function from personal.xls (in the visual basic editor)
and then import it into the current sheet. Is there any way to :
1) make the current spreadsheet look in personal.xls for functions
OR
2) automate importing the function/macro into a newly created spreadsheet ??
TIA
I'm not a vb programmer. Mostly I start the macro recorder, type stuff, stop recording,
then edit what is needed (and I can figure out).
BTW The function starts out like this:
Attribute VB_Name = "IdNoPr00"
Public Function IdNoPr(EBCDec As Integer)
Dim I As String
I = "N"
If EBCDec = 64 Then
I = "Y"
' Disallow everything else < 75 which is not 64 (x'40) Allow 75. 76< 77( 78+ 79| 80&
ElseIf EBCDec > 74 Then
If EBCDec < 81 Then
I = "Y"
< .. and so on ..>
IdNoPr = I
End Function
What it does is identifies all database keys that contain non-enterable EBCDIC characters, i.e., that can't be entered in a CICS session. Part of a cleanup effort.
excel columns, and have it "made attractive" with text formats and colors. There is one part that I cant automate, though, involving a function.
The auto open macro starts like this:
Attribute VB_Name = "AK3_Recv"
Sub Autpen()
Attribute Autpen.VB_Description = "Macro recorded 11/01/2001 by John"
'Attribute Autpen.VB_ProcData.VB_Invoke_Func = " \n14"
ChDir "I:\FTP root"
Workbooks.OpenText Filename:="I:\FTP root\Employer_Addresses\Lisa.txt", Origin:=xlWindows, _
StartRow:=1, DataType:=xlFixedWidth, FieldInfo:=Array(Array(0, 2), Array(1, _
<...more positioning directives...>_
Array(209, 1), Array(216, 2), Array(226, 2), Array(240, 1), Array(243, 9))
Columns("A:A".EntireColumn.AutoFit
<...more formatting directives...>
<... save spreadsheet to LAN location...>
The last thing that happens is a homemade function, "IdNoPr" invocation gets placed
in the first available column (AA), and propagated down to the end:
ActiveCell.FormulaR1C1 = "=IdNoPr(RC25)"
Range("AA2".Select
Selection.Copy
Range("AA3".Select
Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select
Selection.PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
The placing of this function works fine. The problem is that, although the function
lives in personal.xls, which is always open with excel, it is not known to the current
(new) spreadsheet. This gives a NAME? error. It is necessary each time new data is
brought in to manually export the function from personal.xls (in the visual basic editor)
and then import it into the current sheet. Is there any way to :
1) make the current spreadsheet look in personal.xls for functions
OR
2) automate importing the function/macro into a newly created spreadsheet ??
TIA
I'm not a vb programmer. Mostly I start the macro recorder, type stuff, stop recording,
then edit what is needed (and I can figure out).
BTW The function starts out like this:
Attribute VB_Name = "IdNoPr00"
Public Function IdNoPr(EBCDec As Integer)
Dim I As String
I = "N"
If EBCDec = 64 Then
I = "Y"
' Disallow everything else < 75 which is not 64 (x'40) Allow 75. 76< 77( 78+ 79| 80&
ElseIf EBCDec > 74 Then
If EBCDec < 81 Then
I = "Y"
< .. and so on ..>
IdNoPr = I
End Function
What it does is identifies all database keys that contain non-enterable EBCDIC characters, i.e., that can't be entered in a CICS session. Part of a cleanup effort.