Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel,microsoft.public.excel.misc
|
|||
|
|||
Batch Job "Text to column" in excel
Chuck, you are absolutely right on with what i'm looking for.
A kind fellow named Dave Miller wrote a macro for me on another group, but i couldn't get it to work. If you need the directory, i could use: C:\repfiles and as for the parameters for text to colums: Delimited using Commas. here is the message from Mr. Miller: "James, -Open one of the workbooks that you would like to format -Record the macro you want to run on all of your workbooks -Open the VBE(alt + F11) -Copy the code you just recorded -Open a new workbook, paste the code below into a new Module -Replace 'Put your recorded macro here, with your recorded Macro -Press F5 and watch the magic. ================================================== == Sub FormatABunchOfFiles() Dim myBook As Workbook Dim sDir, sFile, sCurrent As String With Application .DisplayAlerts = False .EnableEvents = False .ScreenUpdating = False End With sCurrent = ActiveWorkbook.Name sDir = "YourDirectoryHere\" sFile = "*.xls" With Application.FileSearch .NewSearch .LookIn = sDir .Filename = sFile .MatchTextExactly = True .FileType = msoFileTypeAllFiles If .Execute < 0 Then Exit Sub For i = 1 To .FoundFiles.Count Set myBook = Workbooks.Open(.FoundFiles(i)) myBook.Sheets("Sheet1").Activate 'Put your recorded macro here myBook.Save myBook.Close Next i End With Windows(sCurrent).Activate End Sub "CLR" wrote in message ... What you want to do requires a custom macro. It would have to open each file in a directory, perform the TextToColumns function, and then save and close the file, (maybe to a new name)...then go on and do the next one untill all had been processed. To write such a macro, one would need to know the exact path and directory name, as well as the exact parameters you desired in the TextToColumns function. If you're up for some VBA, and want to provide the needed information.....post back and someone will help. Vaya con Dios, Chuck, CABGx3 "jjc" wrote: Hi everyone! I have a number of excel files in a folder that need "Text to Columns" in Column A. The files are all formatteed identically, with different data. Is there a solution for this? I'm using Excel 2003 on Windows XP. TIA, James |
#2
Posted to microsoft.public.excel,microsoft.public.excel.misc
|
|||
|
|||
Batch Job "Text to column" in excel
Hi James..........
I just now saw your response and now I gotta crash, but if no one has responded by morning, I'll take a look at it then........ Vaya con Dios, Chuck, CABGx3 "jjc" wrote in message ... Chuck, you are absolutely right on with what i'm looking for. A kind fellow named Dave Miller wrote a macro for me on another group, but i couldn't get it to work. If you need the directory, i could use: C:\repfiles and as for the parameters for text to colums: Delimited using Commas. here is the message from Mr. Miller: "James, -Open one of the workbooks that you would like to format -Record the macro you want to run on all of your workbooks -Open the VBE(alt + F11) -Copy the code you just recorded -Open a new workbook, paste the code below into a new Module -Replace 'Put your recorded macro here, with your recorded Macro -Press F5 and watch the magic. ================================================== == Sub FormatABunchOfFiles() Dim myBook As Workbook Dim sDir, sFile, sCurrent As String With Application .DisplayAlerts = False .EnableEvents = False .ScreenUpdating = False End With sCurrent = ActiveWorkbook.Name sDir = "YourDirectoryHere\" sFile = "*.xls" With Application.FileSearch .NewSearch .LookIn = sDir .Filename = sFile .MatchTextExactly = True .FileType = msoFileTypeAllFiles If .Execute < 0 Then Exit Sub For i = 1 To .FoundFiles.Count Set myBook = Workbooks.Open(.FoundFiles(i)) myBook.Sheets("Sheet1").Activate 'Put your recorded macro here myBook.Save myBook.Close Next i End With Windows(sCurrent).Activate End Sub "CLR" wrote in message ... What you want to do requires a custom macro. It would have to open each file in a directory, perform the TextToColumns function, and then save and close the file, (maybe to a new name)...then go on and do the next one untill all had been processed. To write such a macro, one would need to know the exact path and directory name, as well as the exact parameters you desired in the TextToColumns function. If you're up for some VBA, and want to provide the needed information.....post back and someone will help. Vaya con Dios, Chuck, CABGx3 "jjc" wrote: Hi everyone! I have a number of excel files in a folder that need "Text to Columns" in Column A. The files are all formatteed identically, with different data. Is there a solution for this? I'm using Excel 2003 on Windows XP. TIA, James |
#3
Posted to microsoft.public.excel,microsoft.public.excel.misc
|
|||
|
|||
Batch Job "Text to column" in excel
James,
This is the recorded macro and my macro put together: -Paste this into a module and let me know if it works. David Miller ============================================== Sub FormatABunchOfFiles() Dim myBook As Workbook Dim sDir, sFile, sCurrent As String With Application .DisplayAlerts = False .EnableEvents = False .ScreenUpdating = False End With sCurrent = ActiveWorkbook.Name sDir = "YourDirectoryHere\" sFile = "*.xls" With Application.FileSearch .NewSearch .LookIn = sDir .Filename = sFile .MatchTextExactly = True .FileType = msoFileTypeAllFiles If .Execute < 0 Then Exit Sub For i = 1 To .FoundFiles.Count Set myBook = Workbooks.Open(.FoundFiles(i)) myBook.Sheets("Sheet1").Activate Columns("A:A").Select Selection.TextToColumns Destination:=Range("A1"), DataType:=xlDelimited, _ TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=False, _ Semicolon:=False, Comma:=True, Space:=False, Other:=False, FieldInfo _ :=Array(Array(1, 1), Array(2, 1), Array(3, 1), Array(4, 1), Array(5, 1), _ Array(6, 1)), TrailingMinusNumbers:=True Columns("C:C").EntireColumn.AutoFit Rows("1:1").Select Selection.Insert Shift:=xlDown Range("A1") = "Us#" Range("B1") = "date" Range("C1") = "ubd" Range("D1") = "model" Range("E1") = "serial" Range("F1") = "quantity" Columns("E:E").Select Selection.Insert Shift:=xlToRight Range("E1") = "4 digit model" Columns("E:E").EntireColumn.AutoFit Range("E2").Select ActiveCell.FormulaR1C1 = "=RIGHT(RC[-1],4)" Selection.AutoFill Destination:=Range("E2:E43") Range("E2:E43").Select Range("H1") = "combo" Range("H2").Select ActiveCell.FormulaR1C1 = "=CONCATENATE(RC[-3],RC[-2])" Selection.AutoFill Destination:=Range("H2:H43") Columns("H:H").EntireColumn.AutoFit myBook.Save myBook.Close Next i End With Windows(sCurrent).Activate End Sub |
#4
Posted to microsoft.public.excel.misc,microsoft.public.excel
|
|||
|
|||
Batch Job "Text to column" in excel
Hi Dave........
It's good to see you. I was just starting to work on the problem when I saw your post pop up. I see your original macro looked fine, except I percieve the OP did not understand how to incorporate his Directory Name and specific code to perform his TextToColumns Operations into it. Now that you've taken care of that, I can go back to sleep<g.....Happy Holidays..... Vaya con Dios, Chuck, CABGx3 "Dave Miller" wrote: James, This is the recorded macro and my macro put together: -Paste this into a module and let me know if it works. David Miller ============================================== Sub FormatABunchOfFiles() Dim myBook As Workbook Dim sDir, sFile, sCurrent As String With Application .DisplayAlerts = False .EnableEvents = False .ScreenUpdating = False End With sCurrent = ActiveWorkbook.Name sDir = "YourDirectoryHere\" sFile = "*.xls" With Application.FileSearch .NewSearch .LookIn = sDir .Filename = sFile .MatchTextExactly = True .FileType = msoFileTypeAllFiles If .Execute < 0 Then Exit Sub For i = 1 To .FoundFiles.Count Set myBook = Workbooks.Open(.FoundFiles(i)) myBook.Sheets("Sheet1").Activate Columns("A:A").Select Selection.TextToColumns Destination:=Range("A1"), DataType:=xlDelimited, _ TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=False, _ Semicolon:=False, Comma:=True, Space:=False, Other:=False, FieldInfo _ :=Array(Array(1, 1), Array(2, 1), Array(3, 1), Array(4, 1), Array(5, 1), _ Array(6, 1)), TrailingMinusNumbers:=True Columns("C:C").EntireColumn.AutoFit Rows("1:1").Select Selection.Insert Shift:=xlDown Range("A1") = "Us#" Range("B1") = "date" Range("C1") = "ubd" Range("D1") = "model" Range("E1") = "serial" Range("F1") = "quantity" Columns("E:E").Select Selection.Insert Shift:=xlToRight Range("E1") = "4 digit model" Columns("E:E").EntireColumn.AutoFit Range("E2").Select ActiveCell.FormulaR1C1 = "=RIGHT(RC[-1],4)" Selection.AutoFill Destination:=Range("E2:E43") Range("E2:E43").Select Range("H1") = "combo" Range("H2").Select ActiveCell.FormulaR1C1 = "=CONCATENATE(RC[-3],RC[-2])" Selection.AutoFill Destination:=Range("H2:H43") Columns("H:H").EntireColumn.AutoFit myBook.Save myBook.Close Next i End With Windows(sCurrent).Activate End Sub |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Stop excel from dropping the 0 in the beginning of a number? | Setting up and Configuration of Excel | |||
formula structure building ? check under the excel forum.... | Excel Discussion (Misc queries) | |||
Batch file initiation from Excel macro? | Excel Discussion (Misc queries) | |||
Excel should support a proper inverse to "Text to columns" | Excel Discussion (Misc queries) | |||
Excel ignores "Text qualifiers" in Unicode files | Excel Discussion (Misc queries) |