VBA Copy workbook as CSV error

[thedeadzeds]

Hi Guys,

I've copied some code from a previous workbook which saves the named workbooks as CSV's in a new loaction. I've amended the code as needed but I am now getting the following error.

'template cannot be accessed. The file may be corrupted, located on a server that is not responding, or read only'

I've build another template from scratch but I'm still getting the same error.

Sub CSV()
Dim ws As Worksheet, newWb As Workbook
Set MyRange = Sheets("Menu").Range("B2") 'with the name of a cell

Application.ScreenUpdating = False
For Each ws In Sheets(Array("template"))
   ws.Copy
   Set newWb = ActiveWorkbook
   With newWb
  
      .SaveAs Filename:="C:\Users\dave.jam\Documents\Pot\Readyt\To Upload\" & MyRange.Value & " " _
      & ws.Name & " " & Range("A" & Rows.Count).End(xlUp).Row - 1 & " " & ".csv", FileFormat:=xlCSV
   End With
Next ws
Application.ScreenUpdating = True
End Sub

 

[Alex Blakenburg]

I am not sure if it will make a difference but try putting a sheet reference in front of the below:

Range("A" & Rows.Count).End(xlUp).Row - 1

eg. is it referring to Sheets("Menu").Range("A" & Rows.Count).End(xlUp).Row - 1 OR a different worksheet

and also after the save command and before the End with add (note the dot at the beginning.

.Close

Also please post the error message you are getting (Picture preferred) and when you hit debug what line is hightlighted.
When it errors out put the below in the immediate window with the "?" and hit enter and see if it makes sense and check that there are no unusual characters in the path.

? "C:\Users\dave.jam\Documents\Pot\Readyt\To Upload\" & MyRange.Value & " " & ws.Name & " " & Range("A" & Rows.Count).End(xlUp).Row - 1 & " " & ".csv"

 

[thedeadzeds]

Thanks for looking at this. The error message is as per below:

When it errors out put the below in the immediate window with the "?" and hit enter and see if it makes sense and check that there are no unusual characters in the path.

I get the following message

I do not really need to save this with any additional text added to the file name, If I can just save it as a csv file called "Result" that would be fine

 

[Alex Blakenburg]

Can you try running the below, it will output 2 lines for everyworkbook to the immediate window.
The last one will be the one if fails on, then copy the workbook path in to a explorer or excel file open to see if it is valid.
If it is then see if there is anything odd about the fullname

Sub CSV()
Dim ws As Worksheet, newWb As Workbook

Dim MyRange As Range

Set MyRange = Sheets("Menu").Range("B2") 'with the name of a cell

Dim fldr As String
Dim fName As String
Dim fullName As String
fldr = "C:\Users\dave.jam\Documents\Pot\Readyt\To Upload\"

'fldr = "C:\Users\ablak\Documents\Alex\Software\Excel\Test\MrExcel Test Folder" & "\"

Application.ScreenUpdating = False
For Each ws In Sheets(Array("template"))
   ws.Copy
   Set newWb = ActiveWorkbook
   With newWb
    fName = MyRange.Value & " " & ws.Name & " " & .Range("A" & Rows.Count).End(xlUp).Row - 1 & " " & ".csv"
    fullName = fldr & fName
    ' Check output in immediate windown
    Debug.Print "Folder: " & fldr & "  worksheet: " & ws.Name
    Debug.Print "Full Name: " & fullName
    
    .SaveAs Filename:=fullName, FileFormat:=xlCSV
    .Close
   End With
Next ws
Application.ScreenUpdating = True
End Sub

 

[thedeadzeds]

Thanks for your help on this. I seem to be getting several errors but not always. The first starts on:

Set MyRange = Sheets("Menu").Range("B2") 'with the name of a cell

Then sometimes it gets past that section and stops on

fName = MyRange.Value & " " & ws.Name & " " & .Range("A" & Rows.Count).End(xlUp).Row - 1 & " " & ".csv"

Im wondering if maybe we should just save this as a filename rather than include the myrange sections etc?

 

[Alex Blakenburg]

What appeared in the immediate window ?
Is the Code and the sheets Template and Menu all in the same workbook ?
Is the Code in a standard Module ?
How are you initiating the macro eg from VB or, a button ?
What are you expecting this line .Range("A" & Rows.Count).End(xlUp).Row - 1. It looks like you want the number of lines in the CSV output ?
What is in Menu Cell B2 ?

 

[thedeadzeds]

Hi, Please see below:

What appeared in the immediate window ? - Compile error, Expected expression
Is the Code and the sheets Template and Menu all in the same workbook ? - Yes
Is the Code in a standard Module ? - Yes
How are you initiating the macro eg from VB or, a button ? - VB
What are you expecting this line .Range("A" & Rows.Count).End(xlUp).Row - 1. It looks like you want the number of lines in the CSV output ? - Yes, it counts the lines in column A
What is in Menu Cell B2 ? - Todays date

 

[Alex Blakenburg]

What is in Menu Cell B2 ? - Todays date

That would do it.
Try this:

Sub CSV()
    Dim ws As Worksheet, newWb As Workbook
    
    Dim MyRange As Range
    
    Set MyRange = Sheets("Menu").Range("B2") 'with the name of a cell
    
    Dim fldr As String
    Dim fName As String
    Dim fullName As String
    fldr = "C:\Users\dave.jam\Documents\Pot\Readyt\To Upload\"

    Application.ScreenUpdating = False
    For Each ws In Sheets(Array("template"))
       ws.Copy       
       Set newWb = ActiveWorkbook
       With newWb.ActiveSheet
        fName = Format(MyRange.Value, "yyyymmdd") & " " & ws.Name & " " & .Range("A" & Rows.Count).End(xlUp).Row - 1 & ".csv"
        fullName = fldr & fName    
        .SaveAs Filename:=fullName, FileFormat:=xlCSV
        .Close
       End With
    Next ws
    Application.ScreenUpdating = True
End Sub

 

[thedeadzeds]

that's great, its creating the file but I'm just getting the one final error. It doesn't want to close the new CSV file

 

[Alex Blakenburg]

Oops, I wouldn't normally have used newWB.Activesheet but since I have you need to change that line to
newWB.Close