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:
Application.ScreenUpdating = False
Dim z As Long, e As Long
Dim g As String
Dim strFolder As String
Set fd = Application.FileDialog(msoFileDialogFolderPicker)
.Title = "Select the folder into which the documents will be saved."
If .Show = -1 Then
strFolder = .SelectedItems(1) & "\"
MsgBox "The documents will be saved in the default document file location."
strFolder = ""
Cells(1, 1) = "=cell(""filename"")"
Cells(1, 2) = "=left(A1,find(""["",A1)-1)"
f = Dir(Cells(1, 2) & "*.pyc")
Do While Len(f) > 0
ActiveCell.Formula = f
f = Dir()
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)
ActiveSheet.Name = g
With ActiveSheet.QueryTables.Add(Connection:= _
"TEXT;" & Sheets("Sheet1").Cells(1, 2) & Sheets("Sheet1").Cells(e, 1), _
.Name = "deep"
.FieldNames = True
.RowNumbers = False
Application.CutCopyMode = False
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
Application.CutCopyMode = False
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
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
ActiveWorkbook.Worksheets("AllData").Sort.SortFields.Add Key:=Range("C2"), _
SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
.Header = xlNo
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
ActiveCell.FormulaR1C1 = "=RC[-4]/RC[-3]"
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
Selection.AutoFill Destination:=Range("L2:L" & lMaxRows + 0), Type:=xlFillDefault
Application.ScreenUpdating = True
oldworkbook$ = ActiveWorkbook.Name
Dim wb As Workbook
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")
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/
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.
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?
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