$$Excel-Macros$$ Re: Macro help needed for converting all the xls files into csv placing the csv files
Thanks Mandeep But I dont to define the source path, rather browse and open the folder for converting the files. please help! On Wednesday, 26 November 2014 12:57:30 UTC+1, Mandeep Baluja wrote: Sub XLS_to_CSV() Dim wb As Workbook Dim strFile As String, strDir As String SourcePath = C:\Users\pc\Desktop\45.November.2014\ strFile = Dir(SourcePath *.xlsx) Do While strFile Set wb = Workbooks.Open(Filename:=SourcePath strFile, Local:=True) newpath = C:\Users\pc\Desktop\a\ k = Replace(wb.FullName, .xlsx, .csv) q = Replace(k, SourcePath, newpath) wb.SaveAs q wb.Close True Set wb = Nothing strFile = Dir Loop End Sub Regards, Mandeep Baluja Excel Specialist. https://www.linkedin.com/profile/view?id=312532939 https://www.facebook.com/VBAEXCELSQL?ref=hl On Wednesday, November 26, 2014 4:40:03 PM UTC+5:30, Muneera Shabana wrote: Good Morning, Hope you are having a great day! *Objective: I want to convert all the excel files (.xls,.xlsx etc) in a folder to .csv files (with original file names) place the csv file in another target folder.* I am not an excel expert, however I managed to write the below piece of code. It converts a single xl file to csv but places it in my desktop. Please help! Public Sub xls2csv() Dim varArrFile As Variant Dim intCtr As Integer Dim strFilesAs String Dim wb As Workbook varArrFile = Application.GetOpenFilename(*.xls,*.xlsx, , Select file(s) to convert, , True) If IsArray(varArrFile) = False Then MsgBox No file is selected, select a file to continue Exit Sub Else For intCtr = LBound(varArrFile) To UBound(varArrFile) Set wb = Workbooks.Open(varArrFile(intCtr)) wb.SaveAs ThisWorkbook.Path \ intCtr .CSV, 51 wb.Close Set wb = Nothing Next MsgBox The files are successfully converted into .csv format placed in target folder End If End Sub -- Are you =EXP(E:RT) or =NOT(EXP(E:RT)) in Excel? And do you wanna be? It’s =TIME(2,DO:IT,N:OW) ! Join official Facebook page of this forum @ https://www.facebook.com/discussexcel FORUM RULES 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad. 5) Jobs posting is not allowed. 6) Sharing copyrighted material and their links is not allowed. NOTE : Don't ever post confidential data in a workbook. Forum owners and members are not responsible for any loss. --- You received this message because you are subscribed to the Google Groups MS EXCEL AND VBA MACROS group. To unsubscribe from this group and stop receiving emails from it, send an email to excel-macros+unsubscr...@googlegroups.com. To post to this group, send email to excel-macros@googlegroups.com. Visit this group at http://groups.google.com/group/excel-macros. For more options, visit https://groups.google.com/d/optout.
$$Excel-Macros$$ Re: Macro help needed for converting all the xls files into csv placing the csv files
Replace source line with this Set j = Application.FileDialog(msoFileDialogFolderPicker) j.Show SourcePath = j.SelectedItems(1) On Wednesday, November 26, 2014 4:40:03 PM UTC+5:30, Muneera Shabana wrote: Good Morning, Hope you are having a great day! *Objective: I want to convert all the excel files (.xls,.xlsx etc) in a folder to .csv files (with original file names) place the csv file in another target folder.* I am not an excel expert, however I managed to write the below piece of code. It converts a single xl file to csv but places it in my desktop. Please help! Public Sub xls2csv() Dim varArrFile As Variant Dim intCtr As Integer Dim strFilesAs String Dim wb As Workbook varArrFile = Application.GetOpenFilename(*.xls,*.xlsx, , Select file(s) to convert, , True) If IsArray(varArrFile) = False Then MsgBox No file is selected, select a file to continue Exit Sub Else For intCtr = LBound(varArrFile) To UBound(varArrFile) Set wb = Workbooks.Open(varArrFile(intCtr)) wb.SaveAs ThisWorkbook.Path \ intCtr .CSV, 51 wb.Close Set wb = Nothing Next MsgBox The files are successfully converted into .csv format placed in target folder End If End Sub -- Are you =EXP(E:RT) or =NOT(EXP(E:RT)) in Excel? And do you wanna be? It’s =TIME(2,DO:IT,N:OW) ! Join official Facebook page of this forum @ https://www.facebook.com/discussexcel FORUM RULES 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad. 5) Jobs posting is not allowed. 6) Sharing copyrighted material and their links is not allowed. NOTE : Don't ever post confidential data in a workbook. Forum owners and members are not responsible for any loss. --- You received this message because you are subscribed to the Google Groups MS EXCEL AND VBA MACROS group. To unsubscribe from this group and stop receiving emails from it, send an email to excel-macros+unsubscr...@googlegroups.com. To post to this group, send email to excel-macros@googlegroups.com. Visit this group at http://groups.google.com/group/excel-macros. For more options, visit https://groups.google.com/d/optout.
$$Excel-Macros$$ Re: Macro help needed for converting all the xls files into csv placing the csv files
Hey Muneera, One last change has been done in this code where I mentioned * '*Change done here', basically it was not taking a Slash to get inside a directory. Now it's is working fine. Sub XLS_to_CSV() Dim wb As Workbook Dim SourcePath As String Dim strFile As String, strDir As String Dim newpath As String Dim k Dim q Dim j Set j = Application.FileDialog(msoFileDialogFolderPicker) j.Show SourcePath = j.SelectedItems(1) 'Change done here' SourcePath = SourcePath \ strFile = Dir(SourcePath *.xlsx) Do While strFile Set wb = Workbooks.Open(Filename:=SourcePath strFile, Local:=True) newpath = C:\Users\pc\Desktop\a\ k = Replace(wb.FullName, .xlsx, .csv) q = Replace(k, SourcePath, newpath) wb.SaveAs q wb.Close True Set wb = Nothing strFile = Dir Loop MsgBox The files are successfully converted into .csv format placed in target folder End Sub Regards, Mandeep Baluja Excel Specialist. https://www.linkedin.com/profile/view?id=312532939 https://www.facebook.com/VBAEXCELSQL?ref=hl On Wednesday, November 26, 2014 4:40:03 PM UTC+5:30, Muneera Shabana wrote: Good Morning, Hope you are having a great day! *Objective: I want to convert all the excel files (.xls,.xlsx etc) in a folder to .csv files (with original file names) place the csv file in another target folder.* I am not an excel expert, however I managed to write the below piece of code. It converts a single xl file to csv but places it in my desktop. Please help! Public Sub xls2csv() Dim varArrFile As Variant Dim intCtr As Integer Dim strFilesAs String Dim wb As Workbook varArrFile = Application.GetOpenFilename(*.xls,*.xlsx, , Select file(s) to convert, , True) If IsArray(varArrFile) = False Then MsgBox No file is selected, select a file to continue Exit Sub Else For intCtr = LBound(varArrFile) To UBound(varArrFile) Set wb = Workbooks.Open(varArrFile(intCtr)) wb.SaveAs ThisWorkbook.Path \ intCtr .CSV, 51 wb.Close Set wb = Nothing Next MsgBox The files are successfully converted into .csv format placed in target folder End If End Sub -- Are you =EXP(E:RT) or =NOT(EXP(E:RT)) in Excel? And do you wanna be? It’s =TIME(2,DO:IT,N:OW) ! Join official Facebook page of this forum @ https://www.facebook.com/discussexcel FORUM RULES 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad. 5) Jobs posting is not allowed. 6) Sharing copyrighted material and their links is not allowed. NOTE : Don't ever post confidential data in a workbook. Forum owners and members are not responsible for any loss. --- You received this message because you are subscribed to the Google Groups MS EXCEL AND VBA MACROS group. To unsubscribe from this group and stop receiving emails from it, send an email to excel-macros+unsubscr...@googlegroups.com. To post to this group, send email to excel-macros@googlegroups.com. Visit this group at http://groups.google.com/group/excel-macros. For more options, visit https://groups.google.com/d/optout.
$$Excel-Macros$$ Re: Macro help needed for converting all the xls files into csv placing the csv files
Sub CSV_to_XLS() Dim wb As Workbook Dim strFile As String, strDir As String SourcePath = C:\Users\pc\Desktop\45.November.2014\ strFile = Dir(SourcePath *.xlsx) Do While strFile Set wb = Workbooks.Open(Filename:=SourcePath strFile, Local:=True) newpath = C:\Users\pc\Desktop\a\ k = Replace(wb.FullName, .xlsx, .csv) q = Replace(k, SourcePath, newpath) wb.SaveAs q wb.Close True Set wb = Nothing strFile = Dir Loop End Sub Set source path and new path according to your convenience it will convert xlsx files to csv in new path folder. Regards, Mandeep Baluja Excel Specialist. https://www.linkedin.com/profile/view?id=312532939 https://www.facebook.com/VBAEXCELSQL?ref=hl On Wednesday, November 26, 2014 4:40:03 PM UTC+5:30, Muneera Shabana wrote: Good Morning, Hope you are having a great day! *Objective: I want to convert all the excel files (.xls,.xlsx etc) in a folder to .csv files (with original file names) place the csv file in another target folder.* I am not an excel expert, however I managed to write the below piece of code. It converts a single xl file to csv but places it in my desktop. Please help! Public Sub xls2csv() Dim varArrFile As Variant Dim intCtr As Integer Dim strFilesAs String Dim wb As Workbook varArrFile = Application.GetOpenFilename(*.xls,*.xlsx, , Select file(s) to convert, , True) If IsArray(varArrFile) = False Then MsgBox No file is selected, select a file to continue Exit Sub Else For intCtr = LBound(varArrFile) To UBound(varArrFile) Set wb = Workbooks.Open(varArrFile(intCtr)) wb.SaveAs ThisWorkbook.Path \ intCtr .CSV, 51 wb.Close Set wb = Nothing Next MsgBox The files are successfully converted into .csv format placed in target folder End If End Sub -- Are you =EXP(E:RT) or =NOT(EXP(E:RT)) in Excel? And do you wanna be? It’s =TIME(2,DO:IT,N:OW) ! Join official Facebook page of this forum @ https://www.facebook.com/discussexcel FORUM RULES 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad. 5) Jobs posting is not allowed. 6) Sharing copyrighted material and their links is not allowed. NOTE : Don't ever post confidential data in a workbook. Forum owners and members are not responsible for any loss. --- You received this message because you are subscribed to the Google Groups MS EXCEL AND VBA MACROS group. To unsubscribe from this group and stop receiving emails from it, send an email to excel-macros+unsubscr...@googlegroups.com. To post to this group, send email to excel-macros@googlegroups.com. Visit this group at http://groups.google.com/group/excel-macros. For more options, visit https://groups.google.com/d/optout.
$$Excel-Macros$$ Re: Macro help needed for converting all the xls files into csv placing the csv files
Sub XLS_to_CSV() Dim wb As Workbook Dim strFile As String, strDir As String SourcePath = C:\Users\pc\Desktop\45.November.2014\ strFile = Dir(SourcePath *.xlsx) Do While strFile Set wb = Workbooks.Open(Filename:=SourcePath strFile, Local:=True) newpath = C:\Users\pc\Desktop\a\ k = Replace(wb.FullName, .xlsx, .csv) q = Replace(k, SourcePath, newpath) wb.SaveAs q wb.Close True Set wb = Nothing strFile = Dir Loop End Sub Regards, Mandeep Baluja Excel Specialist. https://www.linkedin.com/profile/view?id=312532939 https://www.facebook.com/VBAEXCELSQL?ref=hl On Wednesday, November 26, 2014 4:40:03 PM UTC+5:30, Muneera Shabana wrote: Good Morning, Hope you are having a great day! *Objective: I want to convert all the excel files (.xls,.xlsx etc) in a folder to .csv files (with original file names) place the csv file in another target folder.* I am not an excel expert, however I managed to write the below piece of code. It converts a single xl file to csv but places it in my desktop. Please help! Public Sub xls2csv() Dim varArrFile As Variant Dim intCtr As Integer Dim strFilesAs String Dim wb As Workbook varArrFile = Application.GetOpenFilename(*.xls,*.xlsx, , Select file(s) to convert, , True) If IsArray(varArrFile) = False Then MsgBox No file is selected, select a file to continue Exit Sub Else For intCtr = LBound(varArrFile) To UBound(varArrFile) Set wb = Workbooks.Open(varArrFile(intCtr)) wb.SaveAs ThisWorkbook.Path \ intCtr .CSV, 51 wb.Close Set wb = Nothing Next MsgBox The files are successfully converted into .csv format placed in target folder End If End Sub -- Are you =EXP(E:RT) or =NOT(EXP(E:RT)) in Excel? And do you wanna be? It’s =TIME(2,DO:IT,N:OW) ! Join official Facebook page of this forum @ https://www.facebook.com/discussexcel FORUM RULES 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad. 5) Jobs posting is not allowed. 6) Sharing copyrighted material and their links is not allowed. NOTE : Don't ever post confidential data in a workbook. Forum owners and members are not responsible for any loss. --- You received this message because you are subscribed to the Google Groups MS EXCEL AND VBA MACROS group. To unsubscribe from this group and stop receiving emails from it, send an email to excel-macros+unsubscr...@googlegroups.com. To post to this group, send email to excel-macros@googlegroups.com. Visit this group at http://groups.google.com/group/excel-macros. For more options, visit https://groups.google.com/d/optout.
