PDA

View Full Version : [SOLVED] copy specific worksheet in xlsx as text file



cmccabe1
12-29-2015, 09:34 AM
I am trying to convert all ~200 xlsx files txt using the below vb. In each .xlsx file there is a worksheet with "analysis" in the name that I am trying to copy to Const Filepath So in that directory there will be 200 txt files all from the "analysis" sheet in the .xlsx files. The vb runs but nothing happens and I need some expert help getting this to work. I have been trying for days with no luck. Thank you :).

VB


Private Sub CommandButton1_Click()
Dim fName As String, fPath As String, wb As Workbook


Const Filepath As String = "C:\Users\cmccabe\Desktop\folder"


fPath = "C:\Users\cmccabe\Desktop\epilepsy\"
fName = Dir(fPath & "*analysis*.xlsx")
Do While fName <> ""
Set wb = Workbooks.Open(fPath & fName)
wb.SaveAs Left(fName, Len(fName) - 5) & ".txt", FileFormat:=xlText
fName = Dir
Filename = Filepath & "\" & Left(oFile, InStrRev(oFile, ".")) & "txt"


Open Filename For Output Access Write As #1
Print #1, Text
Close #1
Loop
End Sub

Kevin#
12-30-2015, 08:07 AM
Hi CMC
Try this
I have amended as follows
- added the Filepath to the line beginning wb.SaveAs...(you may find that the text files were being saved to your default folder)
- added a line to Close each newly created text file
- removed the last 4 lines of code

kevin



Private Sub CommandButton1_Click()
Dim fName As String, fPath As String, wb As Workbook
Const Filepath As String = "C:\Users\cmccabe\Desktop\folder"
fPath = "C:\Users\cmccabe\Desktop\epilepsy\"
fName = Dir(fPath & "*analysis*.xlsx")
Do While fName <> ""
Set wb = Workbooks.Open(fPath & fName)
wb.SaveAs Filepath & "\" & Left(fName, Len(fName) - 5) & ".txt", FileFormat:=xlText
wb.Close SaveChanges:=False
fName = Dir
Loop


End Sub

cmccabe1
12-30-2015, 10:01 AM
That works for 1/4 of the files... is there a way that another command button can be used that is similar except that for a different file name (sample) and if it is that file name it opens and copies a specific worksheet (test) in that .xlsx to the new directory? Thank you :).



Private Sub CommandButton1_Click()
Dim fName As String, fPath As String, wb As Workbook
Const Filepath As String = "C:\Users\cmccabe\Desktop\folder"
fPath = "C:\Users\cmccabe\Desktop\epilepsy\"
fName = Dir(fPath & "*sample*.xlsx")
wname = Dir(fPath & "*sample*.xlsx & "sheets("test")")
Do While fName <> ""
Set wb = Workbooks.Open(fPath & wName)
wb.SaveAs Filepath & "\" & Left(fName, Len(fName) - 5) & ".txt", FileFormat:=xlText
wb.Close SaveChanges:=False
fName = Dir
Loop


End Sub

Kevin#
12-30-2015, 10:40 AM
You are welcome

Just to make sure I understand your question I have 3 questions

Are there several files with "sample" in their name?
Is there is a worksheet called "test" in each of these files?
Do you want to save each worksheet called "test" as a separate text file to C:\Users\cmccabe\Desktop\folder ?

cmccabe1
12-30-2015, 11:34 AM
Are there several files with "sample" in their name?
Yes, there are ~125


Is there is a worksheet called "test" in each of these files
Yes, the worksheet test appears somewhere in each of these files ( could be *test or *test* or test*)


Do you want to save each worksheet called "test" as a separate text file to C:\Users\cmccabe\Desktop\folder
Yes, each new test worksheet will be saved in the new directory

Thank you very much for your help :).

Kevin#
12-30-2015, 03:51 PM
Try this

VBA will overwrite previous versions of the text files - is this what you want?
(Application.DisplayAlerts set to FALSE)

The code assumes that there is always a sheet named "test" in each "sample".xlsx file. If a workbook is opened that does not contain this sheet, VBA will stop running, leaving the workbook open. The code will fail at this line:

Set ws = Sheets("test")
How do yo want to handle this?
(could force a blank worksheet to be created OR check all files before saving any of the "test" sheets OR set up error handling so that the error is ignored and VBA moves onto next file etc...)

Kevin



Private Sub CommandButton2_Click()


Application.DisplayAlerts = False
Dim fName As String, fPath As String, wb As Workbook
Dim ws As Worksheet
Const Filepath As String = "C:\Users\cmccabe\Desktop\folder"
fPath = "C:\Users\cmccabe\Desktop\epilepsy\"
fName = Dir(fPath & "*sample*.xlsx")
Do While fName <> ""
Set wb = Workbooks.Open(fPath & fName)
Set ws = Sheets("test")
ws.Copy
wb.SaveAs Filepath & "\" & Left(fName, Len(fName) - 5) & ".txt", FileFormat:=xlText

wb.Close SaveChanges:=False
ActiveWorkbook.Close SaveChanges:=False

fName = Dir
Loop
Application.DisplayAlerts = True

End Sub


PS - there are 2 files to close each time - the VBA creates an extra one for each "test" sheet

cmccabe1
12-30-2015, 04:20 PM
If the sheet "test" is not found then maybe ignoring the error is best. Good catch on the display alerts, I do not want to replace or remove any files. Thanks again :).

Kevin#
12-31-2015, 01:12 AM
Hi CMC
Attached is amended code which includes a check to see if sheet "test" exists. If it does not exist, a message box appears containing the name of the .xlsx file and then the VBA closes the file and carries on as before.

The lines Application.ScreenUpdating = False/True have been added to stop the screen refreshing until the end

You said
I do not want to replace or remove any files
So I have removed the lines Application.DisplayAlerts = False/True - so you will now get a message asking you to confirm that you want to overwrite an existing TEXT file.

Kevin


Private Sub CommandButton2_Click()

Application.ScreenUpdating = False
Dim fName As String, fPath As String, wb As Workbook
Dim ws As Worksheet
Const Filepath As String = "C:\Users\cmccabe\Desktop\folder"
fPath = "C:\Users\cmccabe\Desktop\epilepsy\"
fName = Dir(fPath & "*sample*.xlsx")

Do While fName <> ""
Set wb = Workbooks.Open(fPath & fName)
'test for existence of sheet named "test"
test = 0
For Each ws In Worksheets
If ws.Name = "test" Then
test = 1
Else
'do nothing
End If
Next
If test = 0 Then
'close the workbook if "test" does not exist
MsgBox wb.Name & vbNewLine & "does not contain sheet TEST"
ActiveWorkbook.Close SaveChanges:=False
Else
'save sheet "test" as a text file (ie if "test" does exist)
Set ws = Sheets("test")
ws.Copy
wb.SaveAs Filepath & "\" & Left(fName, Len(fName) - 5) & ".txt", FileFormat:=xlText
wb.Close SaveChanges:=False
ActiveWorkbook.Close SaveChanges:=False
End If
fName = Dir
Loop
Application.ScreenUpdating = True

End Sub

Kevin#
12-31-2015, 04:02 AM
Here is a more elegant (ie faster!) way of checking for existence of sheet "test".
The line If Evaluate("ISREF('test'!A1)") simply verifies the existence of cell A1 in sheet named "test"(previous solution looped through all the sheets in workbook looking for sheet named "test")



Private Sub CommandButton2_Click()

Application.ScreenUpdating = False
Dim fName As String, fPath As String, wb As Workbook
Dim ws As Worksheet
Const Filepath As String = "C:\Users\cmccabe\Desktop\folder"
fPath = "C:\Users\cmccabe\Desktop\epilepsy\"
fName = Dir(fPath & "*sample*.xlsx")

Do While fName <> ""
Set wb = Workbooks.Open(fPath & fName)
'test for existence of sheet named "test"
If Evaluate("ISREF('test'!A1)") Then
'save sheet "test" as a text file (ie if "test" does exist)
Set ws = Sheets("test")
ws.Copy
wb.SaveAs Filepath & "\" & Left(fName, Len(fName) - 5) & ".txt", FileFormat:=xlText
wb.Close SaveChanges:=False
ActiveWorkbook.Close SaveChanges:=False
Else
'close the workbook if "test" does not exist
MsgBox wb.Name & vbNewLine & "does not contain sheet TEST"
ActiveWorkbook.Close SaveChanges:=False
End If
fName = Dir
Loop
Application.ScreenUpdating = True
End Sub

cmccabe1
12-31-2015, 08:49 AM
if the word "test" appears somewhere in the name of the worksheet (sample_test or test_sample), can a wildcard be used?


[If Evaluate("ISREF('*test*'!A1)") Then

Thank you :).

Kevin#
12-31-2015, 09:13 AM
No - we cannot use wildcards here.
And that could be dangerous - "test" could appear inadvertently elsewhere as part of a different sheet name - and that would lead to errors in identifying the correct sheet!
Question - do ALL the "test" sheet names that you need to save as text files either start with "test" OR end with "test" with no other possibilities? And is there only one sheet in each workbook fulfilling that criteria?

cmccabe1
12-31-2015, 09:26 AM
For the sheet names in ~ half "test" appears at the beginning but for the other half "test" appears at the end.

Maybe two separate VBA's? Thank you very much :)

Kevin#
12-31-2015, 09:44 AM
I will think about this overnight (after celebrating the new year!) and get back to you tomorrow.

It is easy to search for text strings within Sheet Names using VBA, but we need to be careful not to inadvertently allow the wrong sheet to be selected. And there are other complications like upper case/lower case etc to build in.

Question Are all the sheets called either "sample_test" or "test_sample"?
If so, the solution is easy - we search for both one after the other within the same macro

HAPPY NEW YEAR :friends::beerchug:

kevin

cmccabe1
12-31-2015, 09:58 AM
HAPPY NEW YEAR and enjoy your evening of celebration :)

I think those are the only possibilities but I have to double check as the data is very unorganized and hard to read. Thanks again :)

edit: it looks like there is a number in front of the text that changes in each sheet. 1234test_sample or 4567sample_test

Kevin#
12-31-2015, 10:20 AM
ok.
So can you confirm that
- the workbooks all contain EITHER the string "test_sample" or "sample_test"
- there is only ONE sheet in each workbook containing that string
- the letters are consistently lower case

and now the beer really is calling very loudly.......:drunkard:

cmccabe1
12-31-2015, 10:28 AM
so the strings are either "test_sample" or "sample_test" with a number in front that changes each file
there is only 1 worksheet with that string in it
looks like the case is not consistent as sometimes it is test_sample and other times it is Test_Sample... the workbook name is also not consistent (Sample sometimes other times sample).

I'm with you on the beer calling :) Have a great night and happy new year!

Kevin#
01-01-2016, 02:25 AM
Code modified to search for strings "test_sample" or "sample_test" in worksheet name (regardless of case)

How long does this take to run?



Private Sub CommandButton2_Click()
Application.ScreenUpdating = False
Dim fName As String, fPath As String, wb As Workbook
Dim ws As Worksheet
Dim sName As String
Const Filepath As String = "C:\Users\cmccabe\Desktop\folder"
fPath = "C:\Users\cmccabe\Desktop\epilepsy\"
fName = Dir(fPath & "*sample*.xlsx")
Do While fName <> ""

Set wb = Workbooks.Open(fPath & fName)
'test for existence of sheet with name including string "test_sample" or "sample_test"
test = 0
For Each ws In Worksheets
If LCase(ws.Name) Like "*test_sample*" Or LCase(ws.Name) Like "*sample_test*" Then
test = 1
sName = ws.Name
Else
'do nothing
End If
Next
If test = 0 Then
MsgBox wb.Name & vbNewLine & "does not contain sheet test_sample or sample_test"
ActiveWorkbook.Close SaveChanges:=False
Else
Set ws = Sheets(sName)
ws.Copy
wb.SaveAs Filepath & "\" & Left(fName, Len(fName) - 5) & ".txt", FileFormat:=xlText
wb.Close SaveChanges:=False
ActiveWorkbook.Close SaveChanges:=False
End If
fName = Dir
Loop
Application.ScreenUpdating = True
End Sub

cmccabe1
01-02-2016, 11:45 AM
Happy New Year and Thank you very much, that worked perfectly and took less than 5 minutes :).

Kevin#
01-02-2016, 03:12 PM
Glad all working well.
If you want to get rid of the inconsistencies (viz upper and lower case sheet names) , try this which converts all worksheet names to lower case.
Suggest you copy the original files to a test folder to experiment!



Sub RenameAllSheetsToLowerCase()
Application.ScreenUpdating = False
Dim fName As String, fPath As String, wb As Workbook, ws As Worksheet
fPath = "C:\Users\cmccabe\Desktop\epilepsy\" 'MODIFY to relevant folder***
fName = Dir(fPath & "*sample*.xlsx") 'MODIFY to relevant file name ***
Do While fName <> ""
Set wb = Workbooks.Open(fPath & fName)
For Each ws In Worksheets
ws.Name = LCase(ws.Name)
Next
wb.Close SaveChanges:=True
fName = Dir
Loop
Application.ScreenUpdating = True
End Sub

cmccabe1
01-06-2016, 10:42 AM
In the attached file (all 133 other files are similar) can only column headers Chr, Start, End, Ref, Alt be extracted from each worksheet. All others are not needed. The problem is that those headers appear in different spots but the header name is always that. The vba as is works great and creates a text file from the entire xlsx. So in the modified version instead of saving the entire xlsx as a text it would only save those 5 column headers for each of the 133 files. Thank you :).

Kevin#
01-06-2016, 02:06 PM
does not sound too tricky. will post a suggestion tomorrow.

snb
01-06-2016, 03:13 PM
Sub M_snb()
sn=split(createobject("wscript.shell").exec("cmd /c dir C:\Users\cmccabe\Desktop\epilepsy\*analysis*.xlsx /b/s").stdout.readall,vbcrlf)

for j=0 to ubound(sn)-1
with getobject(sn(j))
for each sh in .sheets
for j= sh.rows(1).specialcells(2).count to 1 step -1
if instr("|chr|start|end|ref|alt","|" & lcase(sh.cells(1,j)) & "|") =0 then sh.columns(j).delete
next
next
.saveas replace(sn(j),".xlsx",".txt",xltext
.close 0
end with
next
End Sub

Kevin#
01-07-2016, 04:47 AM
@snb that is an elegant bit of coding!:wizard:

cmccabe1
01-07-2016, 09:55 AM
So something like:

VB


Private Sub CommandButton1_Click()
Application.ScreenUpdating = False
Dim fName As String, fPath As String, wb As Workbook
Dim ws As Worksheet
Dim sName As String
Const Filepath As String = "C:\Users\test\Desktop\epilepsy\folder"
fPath = "C:\Users\test\Desktop\epilepsy\"
fName = Dir(fPath & "*NextGene*.xlsx")
Do While fName <> ""

Set wb = Workbooks.Open(fPath & fName)
'test for existence of sheet with name including string "matched" or "annovar"
test = 0
For Each ws In Worksheets
If LCase(ws.Name) Like "*matched_hg19annovar" Or LCase(ws.Name) Like "*annovar*" Then
test = 1
sName = ws.Name
Else
'do nothing
End If
Next
If test = 0 Then
MsgBox wb.Name & vbNewLine & "does not contain sheet in conditions"
ActiveWorkbook.Close SaveChanges:=False
Else
Set ws = Sheets(sName)
ws.Copy
wb.SaveAs Filepath & "\" & Left(fName, Len(fName) - 5) & ".txt", FileFormat:=xlText
wb.Close SaveChanges:=False
ActiveWorkbook.Close SaveChanges:=False
End If
fName = Dir
Loop
Application.ScreenUpdating = True


'find and copy only 5 fields
sn=split(createobject("wscript.shell").exec("cmd /c dir C:\Users\test\Desktop\epilepsy\*NextGene*.xlsx /b/s").stdout.readall,vbcrlf)

For j=0 To ubound(sn)-1
With getobject(sn(j))
For Each sh In .sheets
For j= sh.rows(1).specialcells(2).count To 1 Step -1
If instr("|chr|start|end|ref|alt","|" & lcase(sh.cells(1,j)) & "|") =0 Then sh.columns(j).delete
Next
Next
.saveas replace(sn(j),".xlsx",".txt",xltext
.close 0
End With
Next
End Sub


Thank you very much for your help:).

snb
01-08-2016, 01:54 AM
@cc

No. My code does everything; it replaces your previous code for 100%. Do not add anything to my code.

cmccabe1
01-08-2016, 06:50 AM
Ok, I guess I am just confused as to how the specific worksheets are searched (the case is not consistent):


'test for existence of sheet with name including string "matched" or "annovar"

In the workbooks with the name (the case is not consistent):


fName = Dir(fPath & "*NextGene*.xlsx")

And then the 5 fields saved as a text file in a new directory:


Const Filepath As String = "C:\Users\test\Desktop\epilepsy\folder"


I will give the code a try this morning. I probably just don't understand as I am learning vba. Thank you very much :).

I get a for control variable in use error on the line in () and the saveas is red, but I don't think it gets there. Thank you :).


Sub M_snb()
sn=split(createobject("wscript.shell").exec("cmd /c dir C:\Users\cmccabe\Desktop\epilepsy\*analysis*.xlsx /b/s").stdout.readall,vbcrlf)

For j=0 To ubound(sn)-1
With getobject(sn(j))
For Each sh In .sheets
For j= sh.rows(1).specialcells(2).count To 1 Step -1 (For control variable already in use)
If instr("|chr|start|end|ref|alt","|" & lcase(sh.cells(1,j)) & "|") =0 Then sh.columns(j).delete
Next
Next
.saveas replace(sn(j),".xlsx",".txt",xltext (red)
.close 0
End With
Next
End Sub

Kevin#
01-10-2016, 10:09 AM
An alternative way to get there

MUST run macro from sheet containing the data
The macro does the following
- assign "search words" in array
- "Match" function finds columns
- save column numbers to separate array
- "Union" function groups columns into single range
- copy & paste range to new temporary sheet
- check included for existence of that worksheet first
(using ISRef - is one of easiest ways)
YOU NEED TO PUT IN YOUR CODE TO SAVE THE WORKSHEET where indicated
- finally temporary worksheet is deleted


Sub SearchForColumnHeaders()
'ENSURE that DATA containing COLUMN HEADINGS searched for are in ActiveSheet

Dim ws1 As Worksheet
Set ws1 = ActiveSheet
Dim sArray() As Variant, cArray() As Variant
Dim CopyRange As Range
sArray = Array("Chr", "Start", "End", "Ref", "Alt") ' assign search words to array
ReDim cArray(UBound(sArray))
For i = 0 To UBound(sArray)
'use Match function to find column numbers in row1 in activesheet
cArray(i) = Application.Match(sArray(i), ws1.Rows("1:1"), 0)
Next i
'use Union function to group the columns
Set CopyRange = Application.Union(Columns(cArray(0)), Columns(cArray(1)), Columns(cArray(2)), Columns(cArray(3)), Columns(cArray(4)))
'test for existence of temporary sheet
If Evaluate("ISREF('TempSheet'!A1)") Then
'sheet already exists so delete it without prompts
Application.DisplayAlerts = False
Worksheets("TempSheet").Delete
Application.DisplayAlerts = True
Else
' sheet does NOT exist
End If
'create TempSheet
Worksheets.Add.Name = "TempSheet"
'paste grouped range into tempsheet
CopyRange.Copy Destination:=Worksheets("TempSheet").Range("A1") 'copy columns to new temp sheet
'----------------------------------------


'INSERT PREVIOUS CODE HERE TO SAVE THE WORKSHEET IN FORMAT YOU WANT....."


'----------------------------------------


'and delete the temporary sheet without prompts


Application.DisplayAlerts = False
Worksheets("tempSheet").Delete
Application.DisplayAlerts = True
End Sub

snb
01-10-2016, 10:50 AM
.saveas replace(sn(j),".xlsx",".txt"),xltext (red)

cmccabe1
01-10-2016, 11:14 AM
Thank you both :)

@Kevin I am not sure what you mean by
'INSERT PREVIOUS CODE HERE TO SAVE THE WORKSHEET IN FORMAT YOU WANT....."

Below is how I read it:

1. create 133 individual sheets with the below vba:


Private Sub CommandButton2_Click()
Application.ScreenUpdating = False
Dim fName As String, fPath As String, wb As Workbook
Dim ws As Worksheet
Dim sName As String
Const Filepath As String = "C:\Users\cmccabe\Desktop\folder"
fPath = "C:\Users\cmccabe\Desktop\epilepsy\"
fName = Dir(fPath & "*sample*.xlsx")
Do While fName <> ""

Set wb = Workbooks.Open(fPath & fName)
'test for existence of sheet with name including string "test_sample" or "sample_test"
test = 0
For Each ws In Worksheets
If LCase(ws.Name) Like "*test_sample*" Or LCase(ws.Name) Like "*sample_test*" Then
test = 1
sName = ws.Name
Else
'do nothing
End If
Next
If test = 0 Then
MsgBox wb.Name & vbNewLine & "does not contain sheet test_sample or sample_test"
ActiveWorkbook.Close SaveChanges:=False
Else
Set ws = Sheets(sName)
ws.Copy
wb.SaveAs Filepath & "\" & Left(fName, Len(fName) - 5) & ".txt", FileFormat:=xlText
wb.Close SaveChanges:=False
ActiveWorkbook.Close SaveChanges:=False
End If
fName = Dir
Loop
Application.ScreenUpdating = True
End Sub

2. combine all 133 sheets into one with perl (have the code to use already written)

3. Then in that combined sheet run the below vba:


Sub SearchForColumnHeaders()
'ENSURE that DATA containing COLUMN HEADINGS searched for are in ActiveSheet

Dim ws1 As Worksheet
Set ws1 = ActiveSheet
Dim sArray() As Variant, cArray() As Variant
Dim CopyRange As Range
sArray = Array("Chr", "Start", "End", "Ref", "Alt") ' assign search words to array
ReDim cArray(UBound(sArray))
For i = 0 To UBound(sArray)
'use Match function to find column numbers in row1 in activesheet
cArray(i) = Application.Match(sArray(i), ws1.Rows("1:1"), 0)
Next i
'use Union function to group the columns
Set CopyRange = Application.Union(Columns(cArray(0)), Columns(cArray(1)), Columns(cArray(2)), Columns(cArray(3)), Columns(cArray(4)))
'test for existence of temporary sheet
If Evaluate("ISREF('TempSheet'!A1)") Then
'sheet already exists so delete it without prompts
Application.DisplayAlerts = False
Worksheets("TempSheet").Delete
Application.DisplayAlerts = True
Else
' sheet does NOT exist
End If
'create TempSheet
Worksheets.Add.Name = "TempSheet"
'paste grouped range into tempsheet
CopyRange.Copy Destination:=Worksheets("TempSheet").Range("A1") 'copy columns to new temp sheet
'----------------------------------------

(modified version of this?)
Application.DisplayAlerts = False Dim fName As String, fPath As String, wb As Workbook
Dim ws As Worksheet
Const Filepath As String = "C:\Users\cmccabe\Desktop\folder" (where the save the parsed 5 field file)
fPath = "C:\Users\cmccabe\Desktop\epilepsy\" (where the combined data is located)
fName = Dir(fPath & "*sample*.xlsx") (name of combined file)
Do While fName <> ""
Set wb = Workbooks.Open(fPath & fName)
Set ws = Sheets("test")
ws.Copy
wb.SaveAs Filepath & "\" & Left(fName, Len(fName) - 5) & ".txt", FileFormat:=xlText

wb.Close SaveChanges:=False
ActiveWorkbook.Close SaveChanges:=False

fName = Dir
Loop
Application.DisplayAlerts = True


'----------------------------------------


'and delete the temporary sheet without prompts


Application.DisplayAlerts = False
Worksheets("tempSheet").Delete
Application.DisplayAlerts = True
End Sub

I will try it out tomorrow :)

Kevin#
01-10-2016, 03:13 PM
I have been very ill for a few days and today noticed that you had just updated the thread.
Trying to catch up on far too many things at once, I looked much too quickly at the thread and only looked at first page (did not notice that there was a 2nd page!)
So post 27 replies to post #20!
Oops - Apologies - you have moved on a long way since then.

Never mind - some of the bits of my code could be of interest again
I particularly like : IF Evaluate("ISREF('TempSheet'!A1)") Then to check if a worksheet exists. This checks if A1 is a valid reference, and avoids any error handling etc

Sorry to confuse things!:blush

cmccabe1
01-10-2016, 03:29 PM
No worries and I hope that you are feeling better. Thank you for your help :)

I will give it a go tomorrow and post back with the results.

cmccabe1
01-11-2016, 05:10 PM
The vba combines all 132 text files into one large file. Can each file be parsed for the 5 fields and then be saved? Thank you :).

snb
01-12-2016, 12:52 AM
The vba combines all 132 text files into one large file

That can be done in 1 line of code:


Sub M_snb()
shell "cmd /c copy G:\OF\*.txt G:\allfiles.txt"
End Sub