Clever Geek Handbook

Excel VBA - Getting Runtime Error 1004 - Cannot Access File

I have a very simple macro. I essentially get a dump of data every month for a club that I volunteer for active members. I create several separate xls workbooks from this file which I then need to convert to .csv

Here's some sample code:

' convert Poker

Workbooks.Open fileName:= _
"/Users/birdsdeanger/Documents/CLUB/CSV Files/Current_Month/xlsx/Poker.xlsx"
ChDir _
"/Users/birdsdeanger/Documents/CLUB/CSV Files/Current_Month/csv"
ActiveWorkbook.SaveAs fileName:= _
"/Users/birdsdeanger/Documents/CLUB/CSV Files/Current_Month/csv/Poker" & 
".csv" _
, FileFormat:=xlCSV, CreateBackup:=False
ActiveWorkbook.Saved = True
ActiveWindow.Close


' convert Potluck

Workbooks.Open fileName:= _
"/Users/birdsdeanger/Documents/CLUB/CSV Files/Current_Month/xlsx/Potluck.xlsx"
ChDir _
"/Users/birdsdeanger/Documents/CLUB/CSV Files/Current_Month/csv"
ActiveWorkbook.SaveAs fileName:= _
"/Users/birdsdeanger/Documents/CLUB/CSV Files/Current_Month/csv/Potluck" & 
".csv" _
, FileFormat:=xlCSV, CreateBackup:=False
ActiveWorkbook.Saved = True
ActiveWindow.Close

' convert Pub_Night

Workbooks.Open fileName:= _
"/Users/birdsdeanger/Documents/CLUB/CSV
Files/Current_Month/xlsx/Pub_Night.xlsx"
ChDir _
"/Users/birdsdeanger/Documents/CLUB/CSV Files/Current_Month/csv"
ActiveWorkbook.SaveAs fileName:= _
"/Users/birdsdeanger/Documents/CLUB/CSV Files/Current_Month/csv/Pub_Night" &
".csv" _
, FileFormat:=xlCSV, CreateBackup:=False
ActiveWorkbook.Saved = True
ActiveWindow.Close

I ran this section of the macro and got the following error:

sample error msg

If I interrupt the macro at this point and manually save - as a csv file in the correct directory and then delete it and run the macro again ... the macro will create a csv in the right directory (poker) and bomb the next save as (potluck).

I'll repeat this process with the second file (potluck) (manually save as csv, delete the file from the directory) run the macro again and the first two files will be created just fine and it will bomb on the third (pub night) ...

Any suggestions as to what is going on or how I can fix the code? I have to create 18 of these files monthly and I would like to handle errors from my automation so I won't get my hair ripped out when I run this crazy thing.

+3


source to share


2 answers


I don't have an answer, but first of all I have to record a user-side macro that will do the same. Then I would compare my code with the code generated from the recorded macro to see if I could make any mistake or use the correct function or attributes for what I need.

I have been doing this for a long time and usually I find it wrong pretty quickly.



Good luck!

0


source


Export all sheets to split CSV files. Change accordingly!



Sub ExportSheetsToCSV()
    Dim xWs As Worksheet
    Dim xCSVFile As String

    For Each xWs In Application.ActiveWorkbook.Worksheets
       xWs.Copy
       xTextFile = CurDir & "\" & xWs.Name & ".csv"
       Application.ActiveWorkbook.SaveAs Filename: = xCSVFile, FileFormat: = xlCSV
       Application.ActiveWorkbook.Saved = True
       Application.ActiveWorkbook.Close
    Next
End Sub
0


source






More articles:


All Articles