converting xlsx to csv

P

pukeko

New Member
Joined
Oct 7, 2010
Messages
4
Hi
I have 214 xlsx files i need to convert to csv format for mapping in another program
ive been searching forums etc for a day and more and have got some code that looks as if it should work, but i'm pretty new to this so it could be totally wrong.
ultimately i want to code to get all of the files in the xls folder and make csv versions with the same filename.

anyway, its failing on Workbooks.Open Filename:=MyFileName
here is the code

Code: 
Sub LoopFiles()
    Dim MyFileName, MyPath As String
    Dim MyBook As Workbook
    Application.ScreenUpdating = False
    Application.DisplayAlerts = False
    MyPath = "D:\ArcGis_Projects\Elephants\csv\xls\"
    MyFileName = Dir(MyPath & "*.xlsx")
    Do Until MyFileName = ""
        Workbooks.Open Filename:=MyFileName
    ChDir "D:\ArcGis_Projects\Elephants\csv_dm"
    ActiveWorkbook.SaveAs Filename:=MyFileName, FileFormat:=xlCSV, CreateBackup:=False
    Loop
    Application.ScreenUpdating = True
    Application.DisplayAlerts = True
End Sub


thanks in advance for for any help! Im sure this is a very simple fix

Matt
 

Excel Facts

How to fill five years of quarters?
Type 1Q-2023 in a cell. Grab the fill handle and drag down or right. After 4Q-2023, Excel will jump to 1Q-2024. Dash can be any character.
Sort by date Sort by votes
Change to (and try):

Dim MyFileName As String, MyPath As String

before doing anything else...
 
Upvote 0
ah, good call.
changed that, but still stopping on the same piece of code.
Run time error 1004
balule161.xlsx could not be found.
its the rigby filename. and the location is correct.
also - im using office 2007

Code: 
Sub LoopFiles()
    Dim MyFileName As String, MyPath As String
    Dim MyBook As Workbook
    Application.ScreenUpdating = False
    Application.DisplayAlerts = False
    MyPath = "D:\ArcGis_Projects\Elephants\csv\xls\"
    MyFileName = Dir(MyPath & "*.xlsx")
    Do Until MyFileName = ""
        Workbooks.Open Filename:=MyFileName
    ChDir "D:\ArcGis_Projects\Elephants\csv_dm"
    ActiveWorkbook.SaveAs Filename:=MyFileName, FileFormat:=xlCSV, CreateBackup:=False
    Loop
    Application.ScreenUpdating = True
    Application.DisplayAlerts = True
End Sub
 
Upvote 0
I think you need to assign the opened Wb to an Object Variable


Do Until MyFileName = ""
Set sWBT = Workbooks.Open(MyPath & Filename)

then move on from there...(with needed changes)
 
Upvote 0
should it be Set sWBT = Workbooks.Open(MyPath & MyFileName) ?
do i need a Dim? for sWBT
anyhow. it is now stopping on the new line.
rt error 1004
Method 'Open' of object 'Workbooks' failed
 
Upvote 0
Try...

Code: 
Sub LoopFiles()

    Dim MyFileName As String, MyPath As String
    
    Application.ScreenUpdating = False
    Application.DisplayAlerts = False
    
    MyPath = "D:\ArcGis_Projects\Elephants\csv\xls\"
    
    MyFileName = Dir(MyPath & "*.xlsx")
    
    ChDir "D:\ArcGis_Projects\Elephants\csv_dm"
    
    Do Until MyFileName = ""
        Workbooks.Open Filename:=MyPath & MyFileName
        ActiveWorkbook.SaveAs Filename:=Left(MyFileName, InStr(1, MyFileName, ".xlsx") - 1), FileFormat:=xlCSV, CreateBackup:=False
        MyFileName = Dir
    Loop
    
    Application.ScreenUpdating = True
    Application.DisplayAlerts = True
    
End Sub
 
Upvote 0
yep, that works, i had 3 corupt xlsx files. once i moved them out the code worked fine.
cheers all! thanks for the assistance!
 
Upvote 0
You must log in or register to reply here.

Similar threads

P
  • Solved
Formatting csv file
Replies
6
Views
513
picklefactory
P
R
  • Question
VBA Script: Saving worksheet as CSV on MacOS
Replies
1
Views
846
temphelp
T
tzcoding
  • Solved
VBA Macro to Export Data from Excel Spreadsheet to CSV
Replies
1
Views
5K
Domenic
D
H
  • Solved
Macro to convert Files into CSV Files-limit selection when browsing
Replies
1
Views
223
howard
H
M
  • Question
Extracting & Manipulating Data from CSV
Replies
4
Views
568
rlv01
R

Forum statistics

Threads
1,214,400
Messages
6,119,292
Members
448,885
Latest member
LokiSonic

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top