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