Excel Merge Text files [Solved/Closed]

Posts
19
Registration date
Friday May 7, 2010
Last seen
October 4, 2011
- Sep 30, 2011 at 08:50 AM - Latest reply:
Posts
19
Registration date
Friday May 7, 2010
Last seen
October 4, 2011
- Oct 4, 2011 at 12:10 PM
Dear Forum Members,
As I am not a VB expert I have copied some codes and edited to suit for my purpose. My idea is to combine many .pyc files in a folder into one excel sheet. (.pyc files are output files from a lab machine, which is rather like a .txt file). My codes are working sometimes but sometimes not (the file has two Sheets, named "Sheet1" and "AllData"). When debug it gives a "Application-defined or object-defined error" at line "ActiveSheet.Name = g". I request your help to solve this issue. As mentioned I didn't have knowledge in working, please give an explanation. Regards, Pramod

The codes used are here below:

Sub pycfiles()
Application.ScreenUpdating = False
Dim z As Long, e As Long
Dim g As String
Dim strFolder As String
Set fd = Application.FileDialog(msoFileDialogFolderPicker)
With fd
.Title = "Select the folder into which the documents will be saved."
If .Show = -1 Then
strFolder = .SelectedItems(1) & "\"
Else
MsgBox "The documents will be saved in the default document file location."
strFolder = ""
End If
End With
Sheets("Sheet1").Select
Cells(1, 1) = "=cell(""filename"")"
Cells(1, 2) = "=left(A1,find(""["",A1)-1)"
Cells(2, 1).Select
f = Dir(Cells(1, 2) & "*.pyc")
Do While Len(f) > 0
ActiveCell.Formula = f
ActiveCell.Offset(1, 0).Select
f = Dir()
Loop
z = Cells(Rows.Count, 1).End(xlUp).Row
For e = 2 To z
g = Left(Sheets("Sheet1").Cells(e, 1), Len(Sheets("Sheet1").Cells(e, 1)) - 4)
Worksheets.Add
ActiveSheet.Name = g
With ActiveSheet.QueryTables.Add(Connection:= _
"TEXT;" & Sheets("Sheet1").Cells(1, 2) & Sheets("Sheet1").Cells(e, 1), _
Destination:=Sheets(g).Range("A1"))
.Name = "deep"
.FieldNames = True
.RowNumbers = False
.Refresh BackgroundQuery:=False
End With
Range("A1").Select
Selection.Copy
Range("C1").Select
ActiveSheet.Paste
Range("B3:B25").Select
Application.CutCopyMode = False
Selection.Copy
Range("D1").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=True
Range("A1").Select
Application.CutCopyMode = False
Columns("A:B").Select
Selection.Delete Shift:=xlToLeft
Range("A2").Select
Next e
Dim wrk As Workbook
Dim sht As Worksheet
Dim trg As Worksheet
Dim rng As Range
Dim colCount As Integer
Set wrk = ActiveWorkbook
Set trg = wrk.Worksheets("AllData")
Set sht = wrk.Worksheets(1)
colCount = sht.Cells(1, 255).End(xlToLeft).Column
For Each sht In wrk.Worksheets
If sht.Index = wrk.Worksheets.Count Then
Exit For
End If
Set rng = sht.Range(sht.Cells(2, 1), sht.Cells(65536, 1).End(xlUp).Resize(, colCount))
trg.Cells(65536, 1).End(xlUp).Offset(1).Resize(rng.Rows.Count, rng.Columns.Count).Value = rng.Value
Next sht
trg.Columns.AutoFit
Sheets("AllData").Select

Range("W:X,O:S,G:K,E:E").Select
Range("E1").Activate
Selection.Delete Shift:=xlToLeft
Rows("2:2").Select
Selection.Delete Shift:=xlUp
Range("C2").Select
ActiveWorkbook.Worksheets("AllData").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("AllData").Sort.SortFields.Add Key:=Range("C2"), _
SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
With ActiveWorkbook.Worksheets("AllData").Sort
.SetRange Range("A2:K7000")
.Header = xlNo
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
Range("L2").Select
ActiveCell.FormulaR1C1 = "=RC[-4]/RC[-3]"
Range("A2").Select
'Populate Formulas
On Error Resume Next
lMaxRows = 0
lStartRow = 0
lMaxRows = Sheets("AllData").Cells.Find("*", Cells(1, 1), SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
lStartRow = Sheets("AllData").Cells.Find("*", Cells(1, 1), SearchOrder:=xlByRows, SearchDirection:=xlNext).Row
On Error GoTo 0
If (lMaxRows - lStartRow > 0) Then
Sheets("AllData").Select
Range("L2:L2").Select
Selection.AutoFill Destination:=Range("L2:L" & lMaxRows + 0), Type:=xlFillDefault
End If
Range("A1").Select
Application.ScreenUpdating = True
oldworkbook$ = ActiveWorkbook.Name
Dim wb As Workbook
Sheets(Array("AllData", "Sheet1")).Select
Sheets("AllData").Activate
Sheets(Array("AllData")).Copy
Set wb = ActiveWorkbook
If wb.Saved = False Then
Select Case MsgBox("Save this File(GV-Sheet) in Current Well's Folder by Giveing Proper Name.", vbYesNo Or vbExclamation Or vbDefaultButton1, "J & R Solutions")
Case vbYes
wb.Close True
Case vbNo
wb.Close False
End Select
End If
Workbooks(oldworkbook$).Activate
ThisWorkbook.Close SaveChanges:=False
End Sub



See more 

8 replies

Posts
4481
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
January 6, 2016
- Oct 1, 2011 at 07:45 AM
0
Thank you
One Possible reason is

g = Left(Sheets("Sheet1").Cells(e, 1), Len(Sheets("Sheet1").Cells(e, 1)) - 4) is returning some thing that is not a valid sheet name

So when you do
ActiveSheet.Name = g
it errors out
Posts
19
Registration date
Friday May 7, 2010
Last seen
October 4, 2011
- Oct 1, 2011 at 10:18 AM
0
Thank you
Dear rizvisa1,
Thank you for the reply. As in the past I really expect you solve my problem. I am uploading a file with all relevant files. With this code my idea is to import all .pyc files in a folder into the original excel file in different worksheets, then copy paste Transpose to desired cells, delete unwanted cells and finally collect all worksheets into one worksheet and give instruction to the user to save and then exit. This Marco is working sometimes with me in .xls file and in .xlsm file, some time if it is in the same folder of .pyc file and sometimes in other folders.
I have uploaded the files in as Test.zip, in one of the free file hosting website: http://easy-share.com/ and the download link is: http://www.easy-share.com/8A54F98CEC2611E09676002481FAD55A/Test.zip
AND in: http://uploading.com/ link is: http://uploading.com/files/bad2df43/Test.zip/
Posts
4481
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
January 6, 2016
- Oct 1, 2011 at 06:08 PM
Are you saying that with this set of data, when you run the code, it gives you error ? The point I am not clear on is that are you saying that with same code and the files (that you have zipped up and uploaded), some time macro works and some times does not ? If the macro is working with this set of data, then I am afraid I will not be able to see why it does not work on other times. If one has a data that fails, one can see why it failed.
If this is not the data that fails, then what I would suggest is that, next time it fails, and highlights that line, see what is the value of

Sheets("Sheet1").Cells(e, 1)

Also how many pfy files there can be ? I have seen situation where adding too many sheets errors out for no apparent reason. Excel has inbuilt memory limitation too. In excel 2007 it is 2GB. So no matter how much ram you have on your PC, it limits itself to a max of 2GB
Posts
19
Registration date
Friday May 7, 2010
Last seen
October 4, 2011
- Oct 2, 2011 at 11:06 AM
0
Thank you
Dear rizvisa1,
Thank you for the reply. I am uploading a zipped file"GV-BG-678.zip", (download link: http://www.easy-share.com/283FCB74ED1011E09676002481FAD55A/GV-BG-678.zip) this has many .pyc files, a workable file (combinGVs-3.xls) and its output (BG-678_GV-Combined.xlsx). When I rename or copy the "combinGVs-3.xls" to other folders it will not work. I hope you may find some clues to solve this puzzle.
With regards,
Pramod Kumar
Posts
4481
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
January 6, 2016
- Oct 3, 2011 at 06:35 AM
Pradmod, I am a bit confuse here.
I initially thought your issue was that some time your macro works and some time it does not even if you dont change the file name or file locations. Is that the case ?
Or
Is your issue that your code does not work if you rename the main file or move its location ? If it is the change of the location, do you change both excel file location and pyc file location ?
Posts
19
Registration date
Friday May 7, 2010
Last seen
October 4, 2011
- Oct 3, 2011 at 02:02 PM
0
Thank you
Hi rizvisa1 appreciate your help and time spent. I want this Macro to work from any folder it may be saved as well as the .pyc files. Presently with these codes it is working from the same folder where the .pyc files are saved. Could you please modify this code to open .pyc files saved anywhere; not specific in Folder?
Posts
4481
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
January 6, 2016
- Oct 4, 2011 at 06:17 AM
Try with these changes. Changed and new lines are in bold
Cells(1, 1) = "=cell(""filename"")"
Cells(1, 2) = "=left(A1,find(""["",A1)-1)"

Set fd = Application.FileDialog(msoFileDialogFolderPicker)
With fd
    .Title = "Select the folder where pyc files are saved."
    If .Show = -1 Then
        sPYCFolder = .SelectedItems(1) & "\"
    Else
        sPYCFolder = Cells(1, 2)
        MsgBox "Directory '" & sPYCFolder & "' will be used to load the PYC files"
    End If
End With

Cells(2, 1).Select

f = Dir(sPYCFolder & "*.pyc")

Do While Len(f) > 0
ActiveCell.Formula = f
ActiveCell.Offset(1, 0).Select
f = Dir()
Loop
z = Cells(Rows.Count, 1).End(xlUp).Row
For e = 2 To z
g = Left(Sheets("Sheet1").Cells(e, 1), Len(Sheets("Sheet1").Cells(e, 1)) - 4)
Worksheets.Add
ActiveSheet.Name = g

With ActiveSheet.QueryTables.Add(Connection:= _
        "TEXT;" & sPYCFolder & Sheets("Sheet1").Cells(e, 1), _
        Destination:=Sheets(g).Range("A1"))
        .Name = "deep"
        .FieldNames = True
        .RowNumbers = False
        .Refresh BackgroundQuery:=False
    End With
Posts
19
Registration date
Friday May 7, 2010
Last seen
October 4, 2011
- Oct 4, 2011 at 12:10 PM
0
Thank you
Hi rizvisa1,
Immense thanks for your help, with your modification the code is working in my PC, I hope this will work in other PCs as well. I appreciate your dedication to solve others problems, keep the spirit continue forever.
Please change the status: Solved
With regards, Pramod Kumar