PDA

View Full Version : Merge two scripts



austenr
04-01-2006, 02:59 PM
At Malcombs suggestion I have started a new post to continue this. I need to reach three objectives.

1. Have a way to browse for a text file (.txt)
2. Upon selecting the text file run the scripts below so that an instance of Excel will be created, the headings will be created (see the scripts below).
3. Auto fit the columns.

Script 1 which creates the instance of Excel and reads in the text file:

Sub CreateInstanceAndFill()
Const xlFixed Width = 2
Set objExcel = CreateObject("Excel.Application")
objExcel.Visible = True
objExcel.Workbooks.OpenText _
"C:\Scripts\Test.txt",,,xlFixedWidth,,,,,,,,,Array(Array(0,1),Array(14,
1),Array(32,1))
End Sub
Script 2 which creates the headings:

Sub Sheet_Fill_Column_Headings()
Dim myarray As Variant
myarray = Array("Heading 1", "Heading 2", "Heading 3", "Heading 4",
"Heading 5")
Range("a1:e1").Value = myarray
End Sub

I know that I have to create the instance before you run the headings script, however, I an having a difficult time syncing them up. Any help would be appreciated. Thanks. :dunno

austenr
04-01-2006, 10:04 PM
I found a script that will browse for a file but when I click open after the file is selected it does nothing. Is there a reference I need to be setting or something? Script below:


Set objDialog = CreateObject("UserAccounts.CommonDialog")
objDialog.Filter = "Text Files|*.txt|All Files|*.*"
objDialog.FilterIndex = 1
objDialog.InitialDir = "C:"
intResult = objDialog.ShowOpen

If intResult = 0 Then
Wscript.Quit
Else
Wscript.Echo objDialog.FileName
End If

Norie
04-02-2006, 08:19 AM
Where are you actually running this code?

austenr
04-02-2006, 08:23 AM
Sorry, running it from inside Excel.

Norie
04-02-2006, 08:30 AM
Well 2 questions.:)

Why do you need a new instance of Excel?

Why not use GetOpenFilename?

austenr
04-02-2006, 09:48 AM
I do not really need a new instance. I tried using GetOpenFilename without much success. If you could post and example I would appreciate it.

Norie
04-02-2006, 10:17 AM
How did you try GetOpenFilename?

Try this.


Dim strTextFile As Variant
Dim myarray As Variant
ChDir "C:\Scripts\"

If TypeName(strTextFile) = "Boolean" Then Exit Sub

strTextFile = Application.GetOpenFilename("Text Files (*.txt), *.txt")
Workbooks.OpenText strTextFile, , , xlFixedWidth, , , , , , , , , Array(Array(0, 1), Array(14, 1), Array(32, 1))

Rows(1).Insert

myarray = Array("Heading 1", "Heading 2", "Heading 3", "Heading 4", "Heading 5")

Range("a1:e1").Value = myarray

austenr
04-02-2006, 11:25 AM
Thanks Norie. It kind of worked. The problem is that it created another instance and put the headings in it. If I can solve that problem, that is what I am looking for.

Norie
04-02-2006, 11:58 AM
That code doesn't create another instance of Excel.

austenr
04-02-2006, 02:43 PM
Well, I had the a workbook open Book1 and in that workbook was your code. I ran the code and it created a workbook with the name of the text file that was opened.

Norie
04-02-2006, 08:00 PM
What version of Excel are you using?

I assure you the code I posted won't create a new instance of Excel.

austenr
04-03-2006, 06:00 AM
oFFICE 2003

Norie
04-03-2006, 06:14 AM
austenr

There isn't anything in that code that could possibly create a new instance of Excel.

What makes you think a new instance is being created?

austenr
04-03-2006, 12:49 PM
I got this solved.

Norie
04-03-2006, 01:02 PM
How?

austenr
04-03-2006, 03:24 PM
Well, first of all, I would send you my file but with propritary information on it I can't. On my version of EXCEL it DOES open another workbook and names it the name of the text file you select. As for the rest of it, thank you for the code. With the exception of the new workbook being created, I managed to get it to do what I need it to. Thanks for your help. :hi:

Norie
04-03-2006, 04:30 PM
austenr

What version are you actually using?

If it's 2000 or beyond then there is an option under Tools>Options on the View tab Windows on taskbar.

If that is selected ,it might be that makes you think a new instance is being created.

geekgirlau
04-03-2006, 04:34 PM
Hi austenr,

Just to clarify: another instance of Excel refers to two copies of Microsoft Excel running simultaneously. Opening another workbook within Excel is not considered another instance - it is just taking advantage of the ability of a single instance of Excel to have multiple files open at once. I think the terminology was a source of confusion here!

Generally speaking opening any file will open another workbook - it won't open into an existing blank workbook if you have one open. When working with a text file, if you need the text inserted in an existing workbook, you would have to open the text file, copy the contents into your workbook, then close the text file.

austenr
04-04-2006, 08:59 AM
Hello Geekgirl,

Yes the terminology is the stumbling block here. Unfortunatly not everyone calls things the same even though the message they are trying to convey is still there. My apologies for confusing anyone. BTW, is ther a way to not open another workbook when you are trying to do what I wanted?

Norie
04-04-2006, 09:36 AM
austenr

What exactly do you mean?

austenr
04-04-2006, 05:52 PM
Norie,

What I mean to say is that people tend to call things different things. Depending on your level of expertise (or lack of), you might get a wide variety of answers to a question. I simply called the new workbook that was created when the text file was open an "instance" when in correct termonology, it was not. It actually was a new workbook created when the file was open.

Norie
04-04-2006, 08:06 PM
austenr

I'm still confused as to what you mean/want.:)

austenr
04-05-2006, 04:55 PM
Never mind.

geekgirlau
04-05-2006, 05:01 PM
Hi austenr,

The process you are describing will always open a new workbook - if you need the text inserted in an existing workbook, you would have to open the text file, copy the contents into your workbook, then close the text file.

austenr
04-06-2006, 02:55 PM
GG,

Yea I now know that to be true. Thanks