I can start with the file open, but I want to save an Excel file as a text
file and then after running a batch file in a DOS window, I want to open the
text file back into Excel. I now know how to make the batch file and to do
the rest manually, and it is working quite well, but I'd like to automate it
because it is a lot of steps!

This may give you some ideas (or maybe not...).

Option Explicit
Sub testme()

Dim wks As Worksheet
Dim newWks As Worksheet

Set wks = Worksheets("Sheet1")
wks.Copy 'to a new workbook
Set newWks = ActiveSheet

With newWks.Parent
.SaveAs Filename:="C:\temp\myfile.txt", FileFormat:=xlText
.Close savechanges:=False
End With

Shell "Your bat file name here"

'how long does your .bat file take to execute?
Application.Wait Now + TimeSerial(0, 0, 10) '10 seconds

'record a macro when you open the text file
Workbooks.OpenText Filename:="C:\temp\myfile.txt", ....

Set newWks = ActiveSheet

wks.UsedRange.Clear

newWks.UsedRange.Copy _
Destination:=wks.Range("a1")

wks.UsedRange.Columns.AutoFit

newWks.parent.close savechanges:=false
kill "c:\temp\myfile.txt"

End Sub


denny wrote:

I can start with the file open, but I want to save an Excel file as a text
file and then after running a batch file in a DOS window, I want to open the
text file back into Excel. I now know how to make the batch file and to do
the rest manually, and it is working quite well, but I'd like to automate it
because it is a lot of steps!

--

Dave Peterson

Where do you put that? In the place where you edit macros?

"Dave Peterson" wrote:

This may give you some ideas (or maybe not...).

Option Explicit
Sub testme()

Dim wks As Worksheet
Dim newWks As Worksheet

Set wks = Worksheets("Sheet1")
wks.Copy 'to a new workbook
Set newWks = ActiveSheet

With newWks.Parent
.SaveAs Filename:="C:\temp\myfile.txt", FileFormat:=xlText
.Close savechanges:=False
End With

Shell "Your bat file name here"

'how long does your .bat file take to execute?
Application.Wait Now + TimeSerial(0, 0, 10) '10 seconds

'record a macro when you open the text file
Workbooks.OpenText Filename:="C:\temp\myfile.txt", ....

Set newWks = ActiveSheet

wks.UsedRange.Clear

newWks.UsedRange.Copy _
Destination:=wks.Range("a1")

wks.UsedRange.Columns.AutoFit

newWks.parent.close savechanges:=false
kill "c:\temp\myfile.txt"

End Sub


denny wrote:

I can start with the file open, but I want to save an Excel file as a text
file and then after running a batch file in a DOS window, I want to open the
text file back into Excel. I now know how to make the batch file and to do
the rest manually, and it is working quite well, but I'd like to automate it
because it is a lot of steps!

--

Dave Peterson

Yes, in the place where you edit macros. (in a general module)

--
Regards,
Tom Ogilvy


"denny" wrote in message
...
Where do you put that? In the place where you edit macros?

"Dave Peterson" wrote:

This may give you some ideas (or maybe not...).

Option Explicit
Sub testme()

Dim wks As Worksheet
Dim newWks As Worksheet

Set wks = Worksheets("Sheet1")
wks.Copy 'to a new workbook
Set newWks = ActiveSheet

With newWks.Parent
.SaveAs Filename:="C:\temp\myfile.txt", FileFormat:=xlText
.Close savechanges:=False
End With

Shell "Your bat file name here"

'how long does your .bat file take to execute?
Application.Wait Now + TimeSerial(0, 0, 10) '10 seconds

'record a macro when you open the text file
Workbooks.OpenText Filename:="C:\temp\myfile.txt", ....

Set newWks = ActiveSheet

wks.UsedRange.Clear

newWks.UsedRange.Copy _
Destination:=wks.Range("a1")

wks.UsedRange.Columns.AutoFit

newWks.parent.close savechanges:=false
kill "c:\temp\myfile.txt"

End Sub


denny wrote:

I can start with the file open, but I want to save an Excel file as a
text
file and then after running a batch file in a DOS window, I want to
open the
text file back into Excel. I now know how to make the batch file and
to do
the rest manually, and it is working quite well, but I'd like to
automate it
because it is a lot of steps!

--

Dave Peterson

Just to add to Tom's response...

If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

And you may want to break each portion of your process down into smaller pieces.

Record a macro when you import the file. When you have that working, you can
merge the code into your real macro or just call that routine.

denny wrote:

Where do you put that? In the place where you edit macros?

"Dave Peterson" wrote:

This may give you some ideas (or maybe not...).

Option Explicit
Sub testme()

Dim wks As Worksheet
Dim newWks As Worksheet

Set wks = Worksheets("Sheet1")
wks.Copy 'to a new workbook
Set newWks = ActiveSheet

With newWks.Parent
.SaveAs Filename:="C:\temp\myfile.txt", FileFormat:=xlText
.Close savechanges:=False
End With

Shell "Your bat file name here"

'how long does your .bat file take to execute?
Application.Wait Now + TimeSerial(0, 0, 10) '10 seconds

'record a macro when you open the text file
Workbooks.OpenText Filename:="C:\temp\myfile.txt", ....

Set newWks = ActiveSheet

wks.UsedRange.Clear

newWks.UsedRange.Copy _
Destination:=wks.Range("a1")

wks.UsedRange.Columns.AutoFit

newWks.parent.close savechanges:=false
kill "c:\temp\myfile.txt"

End Sub


denny wrote:

I can start with the file open, but I want to save an Excel file as a text
file and then after running a batch file in a DOS window, I want to open the
text file back into Excel. I now know how to make the batch file and to do
the rest manually, and it is working quite well, but I'd like to automate it
because it is a lot of steps!

--

Dave Peterson


--

Dave Peterson

Yes, I'm pretty new to macros. That sight has a lot on it!. I can record
macros a bit, but know very little about the language. What does the "dim"
mean? and what does it do? After I caught on that I needed to be using the
personal section rather than the individual workbook section for saving my
macros, I made some sort of "beginning" of progress in saving and opening of
several files, but it didn't look at all like yours...but I couldn't quite
understand what spots to change to make yours do something, anything...just
to see what it did.

I want to start with a file called "sample.xls". Save it as "findtest.txt".
Close "findtest.txt". Go into DOS, execute a batch file called
"findtest.bat" which so far, is basically the FIND command and one word e.g.
"findtest heart". It then saves those results to "found.txt" That is then
the file I OPEN in Excel. I want to open "Sample.xls" again and copy the data
from "Found.txt" into it. (That way I can have color and columns and titles
preset. Sometime "found.txt" must be shut so Excel doesn't object the next
time I search for a different word.

Thanks for your help. Is this your job, or just your fun?


"Dave Peterson" wrote:



If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

Dim tells excel that you want to DIMension a variable--to declare it as a
certain type.

Dim wks as worksheet
means that wks is going to represent some worksheet.

Once I set that to a real worksheet:
Set wks = Workbooks("sample.xls").Worksheets("Sheet1")
Then I can use that variable to represent this:
Workbooks("sample.xls").Worksheets("Sheet1")


I'm not sure if this will get you any closer, but....

Option Explicit
Sub testme()

Dim wks As Worksheet
Dim newWks As Worksheet

'what worksheet do you start with?
'I used Sheet1
Set wks = Workbooks("sample.xls").Worksheets("Sheet1")
wks.Copy 'to a new workbook
Set newWks = ActiveSheet

With newWks.Parent
.SaveAs Filename:="C:\temp\findtest.txt", FileFormat:=xlText
.Close savechanges:=False
End With

'not sure what's in findtest.bat or where it's located
Shell "C:\findtest.bat"

'how long does your .bat file take to execute?
Application.Wait Now + TimeSerial(0, 0, 10) '10 seconds

'record a macro when you open the text file
'not sure where found.txt is, either.
Workbooks.OpenText Filename:="C:\temp\found.txt", ....

Set newWks = ActiveSheet

wks.UsedRange.Clear

newWks.UsedRange.Copy _
Destination:=wks.Range("a1")

wks.UsedRange.Columns.AutoFit

newWks.Parent.Close savechanges:=False
Kill "c:\temp\found.txt"
Kill "c:\temp\findtest.txt"

End Sub




denny wrote:

Yes, I'm pretty new to macros. That sight has a lot on it!. I can record
macros a bit, but know very little about the language. What does the "dim"
mean? and what does it do? After I caught on that I needed to be using the
personal section rather than the individual workbook section for saving my
macros, I made some sort of "beginning" of progress in saving and opening of
several files, but it didn't look at all like yours...but I couldn't quite
understand what spots to change to make yours do something, anything...just
to see what it did.

I want to start with a file called "sample.xls". Save it as "findtest.txt".
Close "findtest.txt". Go into DOS, execute a batch file called
"findtest.bat" which so far, is basically the FIND command and one word e.g.
"findtest heart". It then saves those results to "found.txt" That is then
the file I OPEN in Excel. I want to open "Sample.xls" again and copy the data
from "Found.txt" into it. (That way I can have color and columns and titles
preset. Sometime "found.txt" must be shut so Excel doesn't object the next
time I search for a different word.

Thanks for your help. Is this your job, or just your fun?

"Dave Peterson" wrote:


If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm


--

Dave Peterson

Thanks for your help so far. I'm only gaining a little ground at a time (I'm
a slow learner). I recorded some steps and they work...up to the point of
the Dos window appearing....???? Help again, please.

Sub SortInDos()
'
' SortInDos Macro
' Close_Sort_Open
'
' Keyboard Shortcut: Ctrl+Shift+S
'
ActiveWindow.WindowState = xlMinimized
ActiveWorkbook.SaveAs Filename:="C:\My Documents\findtest.txt",
FileFormat _
:=xlText, CreateBackup:=False
ActiveWorkbook.Close

Shell ("c:\windows\command.com")
'It works up to here. The Dos batch file is as follows:
rem prompt $
rem @ echo off
find "%1" /c c:\mydocu~1\findtest.txt c:\mydocu~1\found1.txt
find "%1" c:\mydocu~1\findtest.txt c:\mydocu~1\found2.txt
copy c:\mydocu~1\found1.txt+c:\mydocu~1\found2.txt c:\mydocu~1\found.txt
type c:\mydocu~1\found.txt
rem cls

So, it has to be written in dos as "findtest Heart", (without the quotes and
with whatever word you are searching for. I still want to make it not case
sensitive and able to search several words. Below here is sort of what I
want, but since it didn't work beyond here, I didn't really check it too well
beyond here.

Application.Wait Now + TimeSerial(0, 0, 30)
Workbooks.OpenText Filename:="C:\mydocuments\found.txt"

Range("A6:B6").Select
Range("B6:B6").Activate
Range(Selection, Selection.End(xlToRight)).Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy
Range("A6").Select
Range(Selection, Selection.End(xlToRight)).Select
Range(Selection, Selection.End(xlDown)).Select
Application.CutCopyMode = False
Selection.Copy
Workbooks.Open Filename:="C:\My Documents\SampleTest.xls", UpdateLinks:=0
Sheets("Sampletesr(2)").Select
Range("A1").Select
ActiveSheet.Paste
With ActiveWindow
.Top = 71.5
.Left = 79
End With
Windows("found.txt").Activate
ActiveWindow.Close
End Sub

I didn't make my bat file like yours, but this may get you further along:

Option Explicit
Sub testme()

Dim myBatFile As String
Dim myStr As String
Dim myCommand As String

myBatFile = "C:\my documents\excel\myfile.bat"
myStr = "hello"

myCommand = Chr(34) & myBatFile & Chr(34) & " " & myStr

Shell Environ("comspec") & " /k " & myCommand, vbMaximizedFocus

End Sub

Change the /k to /c to dismiss that DOS window when it's done.

denny wrote:

Thanks for your help so far. I'm only gaining a little ground at a time (I'm
a slow learner). I recorded some steps and they work...up to the point of
the Dos window appearing....???? Help again, please.

Sub SortInDos()
'
' SortInDos Macro
' Close_Sort_Open
'
' Keyboard Shortcut: Ctrl+Shift+S
'
ActiveWindow.WindowState = xlMinimized
ActiveWorkbook.SaveAs Filename:="C:\My Documents\findtest.txt",
FileFormat _
:=xlText, CreateBackup:=False
ActiveWorkbook.Close

Shell ("c:\windows\command.com")
'It works up to here. The Dos batch file is as follows:
rem prompt $
rem @ echo off
find "%1" /c c:\mydocu~1\findtest.txt c:\mydocu~1\found1.txt
find "%1" c:\mydocu~1\findtest.txt c:\mydocu~1\found2.txt
copy c:\mydocu~1\found1.txt+c:\mydocu~1\found2.txt c:\mydocu~1\found.txt
type c:\mydocu~1\found.txt
rem cls

So, it has to be written in dos as "findtest Heart", (without the quotes and
with whatever word you are searching for. I still want to make it not case
sensitive and able to search several words. Below here is sort of what I
want, but since it didn't work beyond here, I didn't really check it too well
beyond here.

Application.Wait Now + TimeSerial(0, 0, 30)
Workbooks.OpenText Filename:="C:\mydocuments\found.txt"

Range("A6:B6").Select
Range("B6:B6").Activate
Range(Selection, Selection.End(xlToRight)).Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy
Range("A6").Select
Range(Selection, Selection.End(xlToRight)).Select
Range(Selection, Selection.End(xlDown)).Select
Application.CutCopyMode = False
Selection.Copy
Workbooks.Open Filename:="C:\My Documents\SampleTest.xls", UpdateLinks:=0
Sheets("Sampletesr(2)").Select
Range("A1").Select
ActiveSheet.Paste
With ActiveWindow
.Top = 71.5
.Left = 79
End With
Windows("found.txt").Activate
ActiveWindow.Close
End Sub

--

Dave Peterson

All my files are in My Documents, except for the source one which the
hotspot refers to. I will enclose a text file so you can see if everything
works, minus the hot spots (tho I think they are a really neat trick to be
able to preserve in DOS. The key is Put an €˜ apostrophe in from of the
formula. And then paste special AS A FORMULA. The apostrophe disappears and
the formula becomes a hot spot!!!
&&&&&&&&&&&&&&&&&&&&&&&&
Reference Subject Sub-Tital Comment
Sub Topic
I Cor 13:1 Love Hyprocrites Prayers Consistency ='[Bible by
Ref and Subject.xls]Sheet1'!$A$12 "Though I speak with the tongues of men and
angels, and have not charity, I am become as a sounding brass, or a tinkling
symbol. It doesn't matter, how profound or spiritual or pious I sound. I
may be able to give good sermons or pray prayers with nice sounding words.
Even if I have a great following from men, but in God's sight it is just
empty noise if I don't know how to be kind, considerate of the needs of
others, understanding, and forgiving. That applies to children who are nice
to everyone else, but not to their parents, "
I Cor 13:2 (1) Love Knowledge Hypocrites ='C:\_All
Mine\Bible\[Personal_Illus.xls]Sheet1'!$A$8 Pride/ Building kingdoms/ Giving
God glory ='[Bible by Ref and Subject.xls]Sheet1'!$A$13 "And though I have
the gift of prophecy, and understand all mysteries, and all knowledge, and
though I have all faith, so that I could remove mountains, and have not
charity, I am nothing.
* Some
people know how to interpret Scripture and make it plain for others, but they
are always pointing a finger and criticizing others (supposedly based on
Scripture). No one can ever correct them in their own life, or even say an
opposing view to their Scriptural point of view without getting their head
bit off (=a sharp know-it-all answer or a repromand) God says that that kind
of Bible knowledge is nothing."
I Cor 13:2 (2) Love Knowledge Hypocrites Prayer ='C:\_All
Mine\Bible\[Personal_Illus.xls]Sheet1'!$A$8 Pride/ Building kingdoms/ Giving
God glory ='[Bible by Ref and Subject.xls]Sheet1'!$A$14 "*Some people know
all the little details and minute answers to answer every trivia question in
the Bible, but there is no evidence of sweetness, thankfulness, and kindness,
as though God's Word has done any changing in their lives. God says that
kind of knowledge, though it sounds good is nothing."
I Cor 13:3 Love Commitment ='[Bible by Ref and
Subject.xls]Sheet1'!$A$17 "I've always been taught that love is
commitment€¦but I've also heard it said, ""What is love?"" This passage seems
to say, If there is commitment and right action, but no heart to go with it,
all the right action is useless. Another passage shows the same kind of
balance when it says, God desires mercy and not sacrifice--though He had told
His people it was an absolute to sacrifice. He is saying, Faith and works
must go together, keeping the law must always be combined with mercy, and
doing what is right must come from the heart. ""As a man thinketh in his
heart, so is he""( ). One cannot choose the opposite extreme either and
say, ""God sees my heart, my intentions, and that is all that matters."""
I Cor 13:3 Love Giving to poor ='[Bible by Ref and
Subject.xls]Sheet1'!$A$18 "And though I bestow all my goods to feed the
poor...and have not charity, it profiteth me nothing€¦Do I just do kindnesses
to others to be thanked and then I am mad if no one appreciates what I did
for them? Then I didn't really do it out of love for them or the Lord. I've
heard people say they were never going to help people again, because they
only end up burnt each time. Another wrong atitude would be, ""She should do
this or that for me after all I've done for her"". We are to give, expecting
nothing in return."
I Cor 13:3 Love Giving to poor Attitude in Giving ='[Bible by
Ref and Subject.xls]Sheet1'!$A$19 "And though I bestow all my goods to feed
the poor...and have not charity, it profiteth me nothing€¦That would apply to
giving to far away missions, but not doing anything for the poor neighbor
next door, especially if it meant you would have to associate with the
""scum"" (hypies), or sit next to you in church, or inconvenience you in some
way."
I Cor 13:4a Love Patience ='[Bible by Ref and
Subject.xls]Sheet1'!$A$20 "Charity suffereth long (Love is patient)--even
when an employee,child or spouse keeps making the same mistakes, even money
mistakes"
I Cor 13:4a Love Patience ='[Bible by Ref and
Subject.xls]Sheet1'!$A$21 "Love is patient--even when others do not think as
fast as you do (Don't interupt and finish their sentences. Don't take over
and do jobs for them just because they cannot measure up to your standard.
Don't fire them without explaining well and giving them lots of chances. Let
them try on their own if they want to. If you always have to do it because
you are in a hurry or want it done just right, they can get to feeling like
they can't do anything."

&&&&&&&&&&&&&&&&&&&&&&&&
Sub SortInDos()
'
' SortInDos Macro
' Close_Sort_Open
'
' Keyboard Shortcut: Ctrl+Shift+S
'
ActiveWindow.WindowState = xlMinimized
ActiveWorkbook.SaveAs Filename:="C:\My Documents\findtest.txt",
FileFormat _
:=xlText, CreateBackup:=True
ActiveWorkbook.Close

'Shell ("c:\windows\command.com")
Shell Environ("comspec") & " /k " & myCommand, vbMaximizedFocus
End Sub
This one works with shortcut key. Then follows the Dos batch file.
prompt $searchword
@ echo off
find "%1" /I /c c:\mydocu~1\findtest.txt c:\mydocu~1\found1.txt
find "%1" /I c:\mydocu~1\findtest.txt c:\mydocu~1\found2.txt
copy c:\mydocu~1\found1.txt+c:\mydocu~1\found2.txt c:\mydocu~1\found.txt
type c:\mydocu~1\found.txt
cls
&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&
The next one works when you step into it, but not with the shortcut key
Can you make any sense of what I have done and improve upon it? I kind of
got lost in the other instructions so I just kept messing with recording.
One important thing was to save the macro as the Personal workbook.

Sub NewSearch()
'
' NewSearch Macro
' Import Search Into Excel
'
' Keyboard Shortcut: Ctrl+Shift+N
'
Workbooks.OpenText Filename:="C:\My Documents\found.txt",
Origin:=xlWindows _
, StartRow:=1, DataType:=xlDelimited, TextQualifier:=xlDoubleQuote, _
ConsecutiveDelimiter:=False, Tab:=True, Semicolon:=False,
Comma:=False _
, Space:=False, Other:=False, FieldInfo:=Array(Array(1, 1), Array(2,
1), _
Array(3, 1), Array(4, 1), Array(5, 1), Array(6, 1))
Range("A5").Select
Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select
Selection.Copy
ActiveWorkbook.Close
Workbooks.Open Filename:="C:\My Documents\Pattern.xls"
Range("A3").Select
Selection.PasteSpecial Paste:=xlFormulas, Operation:=xlNone,
SkipBlanks:= _
False, Transpose:=False

Columns("A:A").Select
With Selection
.HorizontalAlignment = xlGeneral
.VerticalAlignment = xlBottom
.WrapText = True
.Orientation = 0
.AddIndent = False
.ShrinkToFit = False
.MergeCells = False
End With
With Selection.Interior
.ColorIndex = 41
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
End With
Columns("B:B").Select
With Selection
.HorizontalAlignment = xlGeneral
.VerticalAlignment = xlBottom
.WrapText = True
.Orientation = 0
.AddIndent = False
.ShrinkToFit = False
.MergeCells = False
End With
With Selection.Interior
.ColorIndex = 37
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
End With
Columns("C:C").Select
With Selection.Interior
.ColorIndex = 35
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
End With
Columns("E:E").Select
With Selection.Interior
.ColorIndex = 36
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
End With
Columns("F:F").Select
With Selection
.HorizontalAlignment = xlGeneral
.VerticalAlignment = xlBottom
.WrapText = True
.Orientation = 0
.AddIndent = False
.ShrinkToFit = False
.MergeCells = False
End With
Range("A3").Select
End Sub

&&&&&&
Sometimes the Personal.xls pops up for some unknown reason???
I know I haven't accomplished understanding all you gave me before, but
maybe you can perfect this that I have accomplished and understood Thanks

Lots of times, if you remove the shift key from the shortcut key combination,
the code will work.

But I didn't take the time to try it in your case.

denny wrote:

All my files are in My Documents, except for the source one which the
hotspot refers to. I will enclose a text file so you can see if everything
works, minus the hot spots (tho I think they are a really neat trick to be
able to preserve in DOS. The key is Put an €˜ apostrophe in from of the
formula. And then paste special AS A FORMULA. The apostrophe disappears and
the formula becomes a hot spot!!!
&&&&&&&&&&&&&&&&&&&&&&&&
Reference Subject Sub-Tital Comment
Sub Topic
I Cor 13:1 Love Hyprocrites Prayers Consistency ='[Bible by
Ref and Subject.xls]Sheet1'!$A$12 "Though I speak with the tongues of men and
angels, and have not charity, I am become as a sounding brass, or a tinkling
symbol. It doesn't matter, how profound or spiritual or pious I sound. I
may be able to give good sermons or pray prayers with nice sounding words.
Even if I have a great following from men, but in God's sight it is just
empty noise if I don't know how to be kind, considerate of the needs of
others, understanding, and forgiving. That applies to children who are nice
to everyone else, but not to their parents, "
I Cor 13:2 (1) Love Knowledge Hypocrites ='C:\_All
Mine\Bible\[Personal_Illus.xls]Sheet1'!$A$8 Pride/ Building kingdoms/ Giving
God glory ='[Bible by Ref and Subject.xls]Sheet1'!$A$13 "And though I have
the gift of prophecy, and understand all mysteries, and all knowledge, and
though I have all faith, so that I could remove mountains, and have not
charity, I am nothing.
* Some
people know how to interpret Scripture and make it plain for others, but they
are always pointing a finger and criticizing others (supposedly based on
Scripture). No one can ever correct them in their own life, or even say an
opposing view to their Scriptural point of view without getting their head
bit off (=a sharp know-it-all answer or a repromand) God says that that kind
of Bible knowledge is nothing."
I Cor 13:2 (2) Love Knowledge Hypocrites Prayer ='C:\_All
Mine\Bible\[Personal_Illus.xls]Sheet1'!$A$8 Pride/ Building kingdoms/ Giving
God glory ='[Bible by Ref and Subject.xls]Sheet1'!$A$14 "*Some people know
all the little details and minute answers to answer every trivia question in
the Bible, but there is no evidence of sweetness, thankfulness, and kindness,
as though God's Word has done any changing in their lives. God says that
kind of knowledge, though it sounds good is nothing."
I Cor 13:3 Love Commitment ='[Bible by Ref and
Subject.xls]Sheet1'!$A$17 "I've always been taught that love is
commitment€¦but I've also heard it said, ""What is love?"" This passage seems
to say, If there is commitment and right action, but no heart to go with it,
all the right action is useless. Another passage shows the same kind of
balance when it says, God desires mercy and not sacrifice--though He had told
His people it was an absolute to sacrifice. He is saying, Faith and works
must go together, keeping the law must always be combined with mercy, and
doing what is right must come from the heart. ""As a man thinketh in his
heart, so is he""( ). One cannot choose the opposite extreme either and
say, ""God sees my heart, my intentions, and that is all that matters."""
I Cor 13:3 Love Giving to poor ='[Bible by Ref and
Subject.xls]Sheet1'!$A$18 "And though I bestow all my goods to feed the
poor...and have not charity, it profiteth me nothing€¦Do I just do kindnesses
to others to be thanked and then I am mad if no one appreciates what I did
for them? Then I didn't really do it out of love for them or the Lord. I've
heard people say they were never going to help people again, because they
only end up burnt each time. Another wrong atitude would be, ""She should do
this or that for me after all I've done for her"". We are to give, expecting
nothing in return."
I Cor 13:3 Love Giving to poor Attitude in Giving ='[Bible by
Ref and Subject.xls]Sheet1'!$A$19 "And though I bestow all my goods to feed
the poor...and have not charity, it profiteth me nothing€¦That would apply to
giving to far away missions, but not doing anything for the poor neighbor
next door, especially if it meant you would have to associate with the
""scum"" (hypies), or sit next to you in church, or inconvenience you in some
way."
I Cor 13:4a Love Patience ='[Bible by Ref and
Subject.xls]Sheet1'!$A$20 "Charity suffereth long (Love is patient)--even
when an employee,child or spouse keeps making the same mistakes, even money
mistakes"
I Cor 13:4a Love Patience ='[Bible by Ref and
Subject.xls]Sheet1'!$A$21 "Love is patient--even when others do not think as
fast as you do (Don't interupt and finish their sentences. Don't take over
and do jobs for them just because they cannot measure up to your standard.
Don't fire them without explaining well and giving them lots of chances. Let
them try on their own if they want to. If you always have to do it because
you are in a hurry or want it done just right, they can get to feeling like
they can't do anything."

&&&&&&&&&&&&&&&&&&&&&&&&
Sub SortInDos()
'
' SortInDos Macro
' Close_Sort_Open
'
' Keyboard Shortcut: Ctrl+Shift+S
'
ActiveWindow.WindowState = xlMinimized
ActiveWorkbook.SaveAs Filename:="C:\My Documents\findtest.txt",
FileFormat _
:=xlText, CreateBackup:=True
ActiveWorkbook.Close

'Shell ("c:\windows\command.com")
Shell Environ("comspec") & " /k " & myCommand, vbMaximizedFocus
End Sub
This one works with shortcut key. Then follows the Dos batch file.
prompt $searchword
@ echo off
find "%1" /I /c c:\mydocu~1\findtest.txt c:\mydocu~1\found1.txt
find "%1" /I c:\mydocu~1\findtest.txt c:\mydocu~1\found2.txt
copy c:\mydocu~1\found1.txt+c:\mydocu~1\found2.txt c:\mydocu~1\found.txt
type c:\mydocu~1\found.txt
cls
&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&
The next one works when you step into it, but not with the shortcut key
Can you make any sense of what I have done and improve upon it? I kind of
got lost in the other instructions so I just kept messing with recording.
One important thing was to save the macro as the Personal workbook.

Sub NewSearch()
'
' NewSearch Macro
' Import Search Into Excel
'
' Keyboard Shortcut: Ctrl+Shift+N
'
Workbooks.OpenText Filename:="C:\My Documents\found.txt",
Origin:=xlWindows _
, StartRow:=1, DataType:=xlDelimited, TextQualifier:=xlDoubleQuote, _
ConsecutiveDelimiter:=False, Tab:=True, Semicolon:=False,
Comma:=False _
, Space:=False, Other:=False, FieldInfo:=Array(Array(1, 1), Array(2,
1), _
Array(3, 1), Array(4, 1), Array(5, 1), Array(6, 1))
Range("A5").Select
Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select
Selection.Copy
ActiveWorkbook.Close
Workbooks.Open Filename:="C:\My Documents\Pattern.xls"
Range("A3").Select
Selection.PasteSpecial Paste:=xlFormulas, Operation:=xlNone,
SkipBlanks:= _
False, Transpose:=False

Columns("A:A").Select
With Selection
.HorizontalAlignment = xlGeneral
.VerticalAlignment = xlBottom
.WrapText = True
.Orientation = 0
.AddIndent = False
.ShrinkToFit = False
.MergeCells = False
End With
With Selection.Interior
.ColorIndex = 41
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
End With
Columns("B:B").Select
With Selection
.HorizontalAlignment = xlGeneral
.VerticalAlignment = xlBottom
.WrapText = True
.Orientation = 0
.AddIndent = False
.ShrinkToFit = False
.MergeCells = False
End With
With Selection.Interior
.ColorIndex = 37
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
End With
Columns("C:C").Select
With Selection.Interior
.ColorIndex = 35
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
End With
Columns("E:E").Select
With Selection.Interior
.ColorIndex = 36
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
End With
Columns("F:F").Select
With Selection
.HorizontalAlignment = xlGeneral
.VerticalAlignment = xlBottom
.WrapText = True
.Orientation = 0
.AddIndent = False
.ShrinkToFit = False
.MergeCells = False
End With
Range("A3").Select
End Sub

&&&&&&
Sometimes the Personal.xls pops up for some unknown reason???
I know I haven't accomplished understanding all you gave me before, but
maybe you can perfect this that I have accomplished and understood Thanks

--

Dave Peterson

Thanks for the idea. It didn't happen to work this time, but I made another
macro to run that macro and that worked. What happens when you use up all
the Cntl and Shift options? Are there any other combinations?

Is it just a "fact" that if my batch file has to have a search word added to
it that I have to use 3 separate steps? A macro, a batch file, another
macro? In one of your examples you had a time waiting period...but you also
said to make another macro. Can that waiting period be used to decrease the
macro to one?

I think I would make a toolbar that allowed access to the macro.

denny wrote:

Thanks for the idea. It didn't happen to work this time, but I made another
macro to run that macro and that worked. What happens when you use up all
the Cntl and Shift options? Are there any other combinations?

--

Dave Peterson

If you're lucky and you know that your bat file always takes less than x
seconds, you could add application.wait (x + 30% seconds)
(or whatever you feel comfortable with.)

But there are other ways.

http://support.microsoft.com/?kbid=214248
XL2000: How to Force Macro Code to Wait for Outside Procedure
or

Here's a link to a nice ShellAndWait function that does that.
http://groups.google.com/groups?thre...%40tkmsftngp03

denny wrote:

Is it just a "fact" that if my batch file has to have a search word added to
it that I have to use 3 separate steps? A macro, a batch file, another
macro? In one of your examples you had a time waiting period...but you also
said to make another macro. Can that waiting period be used to decrease the
macro to one?

--

Dave Peterson

I now see...or I think I see the value of your code over simply recording a
macro...IT ENABLES ONE TO DIRECT EXACTLY WHICH FILE is to be acted upon,
instead of just the next open file and then doing the wrong thing because you
had the wrong file open. I am progressing in combining things from your code
and mine...but I have run amuck in 2 places,..both about closing a file, I
thingk. One I'll get to after you help me on this one.

After I have brought my found.txt back in and copied and pasted it into my
premade pattern...I tried to close the found.txt file. I didn't need to save
anything. I just needed to kill it...get it gone!, but it objected. It
said, The sheet you are copying has cells that contain more than 255
characters. To copy all the characters, copy the cells to a new sheet
instead of copying the entire sheet. This is the line in macro that it
objected to and three before. If it is not enough, let me know and I will
include more.

Application.CutCopyMode = False
Selection.ClearContents
ActiveWorkbook.Close

Set newWks = Workbooks("pattern.txt").Worksheets("Sheet1")

Another thing I'd like to know is, Is there any way to answer the "yes or
no" questions within the macro itself ...because if you happen to be careless
or forgetful and click on the wrong answer the macro stops.

Try putting some long text in a cell in a worksheet.

Then ctrl-click and drag that worksheet tab to the right (copying the
worksheet).

You'll see that error. But to be honest, I've never seen that error in
code--for me, the worksheet has always copied and the cells have been truncated.

I'm not sure how you got that error to show up.

It sounds like you're doing it manually????? I don't see anything in any of
those lines that would cause that error.

Anyway...

This worked for me to keep the cells intact.

Dim fWks As Worksheet
Dim tWks As Worksheet

Set fWks = Worksheets("sheet1")
fWks.Copy 'copy to a new workbook

Set tWks = ActiveSheet
tWks.UsedRange.Copy _
Destination:=fWks.UsedRange

denny wrote:

I now see...or I think I see the value of your code over simply recording a
macro...IT ENABLES ONE TO DIRECT EXACTLY WHICH FILE is to be acted upon,
instead of just the next open file and then doing the wrong thing because you
had the wrong file open. I am progressing in combining things from your code
and mine...but I have run amuck in 2 places,..both about closing a file, I
thingk. One I'll get to after you help me on this one.

After I have brought my found.txt back in and copied and pasted it into my
premade pattern...I tried to close the found.txt file. I didn't need to save
anything. I just needed to kill it...get it gone!, but it objected. It
said, The sheet you are copying has cells that contain more than 255
characters. To copy all the characters, copy the cells to a new sheet
instead of copying the entire sheet. This is the line in macro that it
objected to and three before. If it is not enough, let me know and I will
include more.

Application.CutCopyMode = False
Selection.ClearContents
ActiveWorkbook.Close

Set newWks = Workbooks("pattern.txt").Worksheets("Sheet1")

Another thing I'd like to know is, Is there any way to answer the "yes or
no" questions within the macro itself ...because if you happen to be careless
or forgetful and click on the wrong answer the macro stops.

--

Dave Peterson

I'll be getting back to you as soon as I can show you my progress. Some
things are kind of manual because they are done with the macro recorder
instead of the code. Many times when I try to use the code, it tells me
there is a "with" that needs to be finished or some other instruction that I
don't know its partner or how to make it accepted. You can help me with that
maybe after you see my final product...but first of all,
1. IS THERE A WAY...in the macro... TO ANSWER THE YES AND NO WINDOWS that
come up, say when you are saving a file and it asks if it is okay to save it
on top of another?
2.The only solution I have found to my problem to saving big cells is to not
try and save it as a text file at all. SAVE IT AS AN XLS FILE, and that will
make the text file disappear...but it is chopping my big cells down smaller.
IS THERE SIMPLY A LIMIT (ABOUT 242) FOR THE TEXT IN THE CELLS FOR TEXT AND
FOR COPYING. Excel holds a lot more, tho' it has a limit too.
Thanks for your continued help. I'll let you know when it all works right.

I seemed to lose some info in the big cells if I saved it as a book too (the
first way you showed me). Saving it directly at .csv works, but I get the
closing screen that is a nuisance. to say yes or no

"denny" wrote:


I'll be getting back to you as soon as I can show you my progress. Some
things are kind of manual because they are done with the macro recorder
instead of the code. Many times when I try to use the code, it tells me
there is a "with" that needs to be finished or some other instruction that I
don't know its partner or how to make it accepted. You can help me with that
maybe after you see my final product...but first of all,
1. IS THERE A WAY...in the macro... TO ANSWER THE YES AND NO WINDOWS that
come up, say when you are saving a file and it asks if it is okay to save it
on top of another?
2.The only solution I have found to my problem to saving big cells is to not
try and save it as a text file at all. SAVE IT AS AN XLS FILE, and that will
make the text file disappear...but it is chopping my big cells down smaller.
IS THERE SIMPLY A LIMIT (ABOUT 242) FOR THE TEXT IN THE CELLS FOR TEXT AND
FOR COPYING. Excel holds a lot more, tho' it has a limit too.
Thanks for your continued help. I'll let you know when it all works right.

Lots of times when you get a missing "end with" statement, it's because you're
missing an "end if" that should come before it. You may want to look there.

You can use:

application.displayalerts = false
'your code to save it
application.displayalerts = true

I've never seen saving a workbook (as a real .xls file) chop any data in a cell.

denny wrote:

I'll be getting back to you as soon as I can show you my progress. Some
things are kind of manual because they are done with the macro recorder
instead of the code. Many times when I try to use the code, it tells me
there is a "with" that needs to be finished or some other instruction that I
don't know its partner or how to make it accepted. You can help me with that
maybe after you see my final product...but first of all,
1. IS THERE A WAY...in the macro... TO ANSWER THE YES AND NO WINDOWS that
come up, say when you are saving a file and it asks if it is okay to save it
on top of another?
2.The only solution I have found to my problem to saving big cells is to not
try and save it as a text file at all. SAVE IT AS AN XLS FILE, and that will
make the text file disappear...but it is chopping my big cells down smaller.
IS THERE SIMPLY A LIMIT (ABOUT 242) FOR THE TEXT IN THE CELLS FOR TEXT AND
FOR COPYING. Excel holds a lot more, tho' it has a limit too.
Thanks for your continued help. I'll let you know when it all works right.

--

Dave Peterson