Excel Merge Text files
Solved/Closed
madambath
Posts
19
Registration date
Friday May 7, 2010
Status
Member
Last seen
October 4, 2011
-
Sep 30, 2011 at 08:50 AM
madambath Posts 19 Registration date Friday May 7, 2010 Status Member Last seen October 4, 2011 - Oct 4, 2011 at 12:10 PM
madambath Posts 19 Registration date Friday May 7, 2010 Status Member Last seen October 4, 2011 - Oct 4, 2011 at 12:10 PM
Related:
- Excel Merge Text files
- Clear recent files windows 11 - Guide
- Excel free download - Download - Spreadsheets
- Excel marksheet - Guide
- Number to words in excel - Guide
- Recover my files - Download - Backup and recovery
5 responses
rizvisa1
Posts
4478
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
May 5, 2022
766
Oct 1, 2011 at 07:45 AM
Oct 1, 2011 at 07:45 AM
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
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
madambath
Posts
19
Registration date
Friday May 7, 2010
Status
Member
Last seen
October 4, 2011
Oct 1, 2011 at 10:18 AM
Oct 1, 2011 at 10:18 AM
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/
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/
rizvisa1
Posts
4478
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
May 5, 2022
766
Oct 1, 2011 at 06:08 PM
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
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
madambath
Posts
19
Registration date
Friday May 7, 2010
Status
Member
Last seen
October 4, 2011
Oct 2, 2011 at 11:06 AM
Oct 2, 2011 at 11:06 AM
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
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
rizvisa1
Posts
4478
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
May 5, 2022
766
Oct 3, 2011 at 06:35 AM
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 ?
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 ?
madambath
Posts
19
Registration date
Friday May 7, 2010
Status
Member
Last seen
October 4, 2011
Oct 3, 2011 at 02:02 PM
Oct 3, 2011 at 02:02 PM
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?
rizvisa1
Posts
4478
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
May 5, 2022
766
Oct 4, 2011 at 06:17 AM
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
Didn't find the answer you are looking for?
Ask a question
madambath
Posts
19
Registration date
Friday May 7, 2010
Status
Member
Last seen
October 4, 2011
Oct 4, 2011 at 12:10 PM
Oct 4, 2011 at 12:10 PM
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
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