Macro issue
 

I setup a Macro that opens up a bunch of text file and imports them int
a CSV file and then data I need is copy and pasted into specific cells


Now the problem is that everytime i run the Macro, i get over 31 ope
CSV files. Is there a way i can add something to the script that wil
close the file automatically for me, but leave my main XLS file open?

--
Message posted from http://www.ExcelForum.com

Macro issue
 

Alex

presumably you are going around a loop to open and process the files? Why
not record a macro to close one of the CSV files and then incorporate this
code into your loop after you have processed the data ?

If you post an extract from your code (the loop to open and process the csv
files) I'm sure someone will draft some code for you.

Regards

Trevor


"alexm999 " wrote in message
...
I setup a Macro that opens up a bunch of text file and imports them into
a CSV file and then data I need is copy and pasted into specific cells.


Now the problem is that everytime i run the Macro, i get over 31 open
CSV files. Is there a way i can add something to the script that will
close the file automatically for me, but leave my main XLS file open??


---
Message posted from http://www.ExcelForum.com/

Macro issue
 

Alex,

Something like

Workbooks.Open Filename:="C:\myfile.txt"
' do your thing
Activeworkbook.Close

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"alexm999 " wrote in message
...
I setup a Macro that opens up a bunch of text file and imports them into
a CSV file and then data I need is copy and pasted into specific cells.


Now the problem is that everytime i run the Macro, i get over 31 open
CSV files. Is there a way i can add something to the script that will
close the file automatically for me, but leave my main XLS file open??


---
Message posted from http://www.ExcelForum.com/

Macro issue
 

Here's the code:

what can i add to get the file 2.txt to close at the end?

Sub macro1()
Workbooks.OpenText Filename:="E:\UDC\2.TXT", Origin:=xlWindows,
StartRow _
:=7, DataType:=xlDelimited, TextQualifier:=xlDoubleQuote, _
ConsecutiveDelimiter:=True, Tab:=True, Semicolon:=False,
Comma:=False, _
Space:=True, Other:=False, FieldInfo:=Array(Array(1, 1),
Array(2, 1), Array(3 _
, 1), Array(4, 1), Array(5, 1), Array(6, 1), Array(7, 1),
Array(8, 1))
Range("F13").Select
Selection.Copy
Windows("DAILY OPERATIONS_2004.xls").Activate
Range("C8").Select
ActiveSheet.Paste
Range("E8").Select
Windows("2.TXT").Activate
Range("F14").Select
Application.CutCopyMode = False
Selection.Copy
Windows("DAILY OPERATIONS_2004.xls").Activate
ActiveSheet.Paste
Range("J8").Select
Windows("2.TXT").Activate
Range("E17").Select
Application.CutCopyMode = False
Selection.Copy
Windows("DAILY OPERATIONS_2004.xls").Activate
ActiveSheet.Paste
Range("K8").Select
Windows("2.TXT").Activate
Range("G18").Select
Application.CutCopyMode = False
Selection.Copy
Windows("DAILY OPERATIONS_2004.xls").Activate
ActiveSheet.Paste
ActiveWindow.LargeScroll ToRight:=2
Range("AI8").Select
Windows("2.TXT").Activate
Range("F18").Select
Application.CutCopyMode = False
Selection.Copy
Windows("DAILY OPERATIONS_2004.xls").Activate
ActiveSheet.Paste


---
Message posted from http://www.ExcelForum.com/

Macro issue
 

Alex,

Try this

Sub macro1()
Dim oThisFile As Workbook '<<<<<<<<<<<<<<<<<<<< New code
Workbooks.OpenText Filename:="E:\UDC\2.TXT", Origin:=xlWindows,
Set oThisFile As ActiveWorkbook '<<<<<<<<<<<<<<<<<<<< New code
StartRow _
:=7, DataType:=xlDelimited, TextQualifier:=xlDoubleQuote, _
ConsecutiveDelimiter:=True, Tab:=True, Semicolon:=False,
Comma:=False, _
Space:=True, Other:=False, FieldInfo:=Array(Array(1, 1),
Array(2, 1), Array(3 _
, 1), Array(4, 1), Array(5, 1), Array(6, 1), Array(7, 1),
Array(8, 1))
Range("F13").Select
Selection.Copy
Windows("DAILY OPERATIONS_2004.xls").Activate
Range("C8").Select
ActiveSheet.Paste
Range("E8").Select
Windows("2.TXT").Activate
Range("F14").Select
Application.CutCopyMode = False
Selection.Copy
Windows("DAILY OPERATIONS_2004.xls").Activate
ActiveSheet.Paste
Range("J8").Select
Windows("2.TXT").Activate
Range("E17").Select
Application.CutCopyMode = False
Selection.Copy
Windows("DAILY OPERATIONS_2004.xls").Activate
ActiveSheet.Paste
Range("K8").Select
Windows("2.TXT").Activate
Range("G18").Select
Application.CutCopyMode = False
Selection.Copy
Windows("DAILY OPERATIONS_2004.xls").Activate
ActiveSheet.Paste
ActiveWindow.LargeScroll ToRight:=2
Range("AI8").Select
Windows("2.TXT").Activate
Range("F18").Select
Application.CutCopyMode = False
Selection.Copy
Windows("DAILY OPERATIONS_2004.xls").Activate
ActiveSheet.Paste

oThisFile.Close '<<<<<<<<<<<<<<<<<<<< New code

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"alexm999 " wrote in message
...
Here's the code:

what can i add to get the file 2.txt to close at the end?