$$Excel-Macros$$ Converting excel files to .txt format with delimiter comma

[Muneera Shabana]

Hello there,

I have written a macro to convert xls files to text file, however I have 2 
problems here:
1. My code doesnot show xlsx format to pick up from the source folder
2. I am not sure when to add the code to have delimiter "comma" in my text 
file.

I am not an expert, so seeking support from you guys, please help!!




Sub XLS_to_TXT()

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)
 
 SourcePath = SourcePath & "\"

'// SourcePath = "H:\Macros\"

strFile = Dir(SourcePath & "*.xlsx")

Do While strFile <> ""

Set wb = Workbooks.Open(Filename:=SourcePath & strFile, Local:=True)
   
newpath = "H:\Macros\csv\"

 k = Replace(wb.FullName, ".xlsx", ".txt")

 q = Replace(k, SourcePath, newpath)

 wb.SaveAs q
   
 wb.Close True
  
Set wb = Nothing

strFile = Dir

Loop

MsgBox "The files are successfully converted into text format & placed in 
target folder"

Application.Wait (Now + TimeValue("00:00:02"))

ActiveWorkbook.FollowHyperlink newpath

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

[Muneera Shabana]

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: Introduce Yourself !!

[Muneera Shabana]

Hello buddies,
 
This is Muneera Khan from Amsterdam, the Netherlands Currently working 
as Business Process Analyst for Market Risk Management at ING Bank.
 
I love museums, travelling, painting, arts & fashion!
 
I highly appreciate all the contributers here providing very good 
supportAnd I am here to grasp as much as knowledge I can from all the 
excel gurus out there.
 
Fijne Dag!

-- 
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$$ Macro help needed for converting all the xls files into csv & placing the csv files

[Muneera Shabana]

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.