Automate importing macro into new spreadsheet 1

[InnSun]

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(&quot;A:A&quot.EntireColumn.AutoFit
<...more formatting directives...>
<... save spreadsheet to LAN location...>

The last thing that happens is a homemade function, &quot;IdNoPr&quot; invocation gets placed
in the first available column (AA), and propagated down to the end:

ActiveCell.FormulaR1C1 = &quot;=IdNoPr(RC25)&quot;
Range(&quot;AA2&quot.Select
Selection.Copy
Range(&quot;AA3&quot.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 = &quot;IdNoPr00&quot;
Public Function IdNoPr(EBCDec As Integer)
Dim I As String
I = &quot;N&quot;
If EBCDec = 64 Then
I = &quot;Y&quot;
' 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 = &quot;Y&quot;
< .. 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.

 

[Bowers74]

Just an ad-hoc solution, that might work. How about moving/copying the IdNoPr into the worksheet that contains the macro.

I hope this helps!



Peace!

Mike

Never say Never!!!
Nothing is impossible!!!

 

[InnSun]

Thanks Mike!

Ok &quot;worksheet that contains the macro&quot;: you mean the auto open macro, right?

I just ran this macro again. Three .xls files are currently open (at completion) in three windows:
[ul][li] personal.xls = always open with excel, contains a handful of general macros; the ones that work generally each start with &quot;sub&quot;.[/li]
[li]recv_ak3.xls = spreadsheet with the auto open macro.[/li]
[li]lisa.xls = the newly created spreadsheet of data. [/li][/ul]

IdNoPr is in both personal.xls & recv_ak3.xls but putting =IdNoPr($Y2) in cell $AA2 gives an error.

again, manually exporting to a .bas file on my C:\ drive and then importing into lisa.xls solves it.

Is this thing do-able?

regards johnny i
Weshecatweloo
(Let us always do good)
Shawnee

 

[SkipVought]

Hi,

Create a template (.xlt) that has the function.

In your auto_open

1. open the text file as per above
2. open template
3. copy the data to a sheet in the template
4. save as the template/data

Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com

 

[InnSun]

Thanks Skip!

Ok this is what I did although it may not be the most effecient:
Created a new template with a blank sheet and the function IdNoPr. Then, in Autpen:
[ul][li]Do everything as before with the text import. Save to .xls file on drive E:\. Close this .xls file.[/li]
[li]Create a new .xls file based on the new template. [/li]
[li]Import entire sheet from the just-saved .xls file on E:\[/li]
[li]Save this new .xls file to the W:\ drive[/li][/ul]

Anyway that works. Have a star.

regards