VBA saving to csv when the name of the workbook is a variable.

VectorW2

New Member
Joined
Sep 11, 2013
Messages
36
Office Version
  1. 365
  2. 2010
I have a piece of code that works perfectly; however, when I try to protect the code via VBAProject – Project Properties, the code errors at the CSV save point (bolded below). I have tried changing from activeworkbook to thisworkbook, but I have the same issue. Is there a way to protect the code and still change the workbook to csv? Thank you.

Case "Save":

Range("Q11").Copy

Range("Q11").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _

:=False, Transpose:=False

Range("A2").Select

If Range("Q8") = 9999 Then

Worksheets("G").Range("Q:Q").NumberFormat = "General"

Worksheets("G").Range("B:B").NumberFormat = "0000000"

ActiveWorkbook.SaveAs Filename:= _

"P:\Property\2023 B Upload \B9999\" & Range("Q11") & ".xlsm", _

FileFormat:=xlOpenXMLWorkbookMacroEnabled, CreateBackup:=False

For Each Sheet In ActiveWorkbook.Worksheets

If Sheet.Name = "Pre-calculated Copy" Then

Application.DisplayAlerts = False

Worksheets("Pre-calculated Copy").Delete

Application.DisplayAlerts = True

End If

Next Sheet

Sheets.Add After:=ActiveSheet

ActiveSheet.Name = "Upload Copy"

Worksheets("G").Range("A1:H" & Cells(Rows.Count, "B").End(xlUp).Row).Copy

Worksheets("Upload Copy").Range("A1").PasteSpecial Paste:=xlPasteFormats

Worksheets("Upload Copy").Range("A1").PasteSpecial Paste:=xlPasteValues

Application.CutCopyMode = False

ActiveWorkbook.Worksheets("Upload Copy").Select

Worksheets("Upload Copy").Range("B:B").NumberFormat = "0000000"

Worksheets("Upload Copy").Range("F:F").Select

Selection.Style = "Comma"

Selection.NumberFormat = "_(* #,##0_);_(* (#,##0);_(* ""-""??_);_(@_)"

ThisWorkbook.Save

ThisWorkbook.SaveAs Filename:= _

"P:\Property\2023 B Upload \B9999\" & Range("Q11") & ".csv", _

FileFormat:=xlCSVUTF8, CreateBackup:=False

ActiveWorkbook.Save


End If

If Range("Q8") = 7777 Then
 

Excel Facts

Which came first: VisiCalc or Lotus 1-2-3?
Dan Bricklin and Bob Frankston debuted VisiCalc in 1979 as a Visible Calculator. Lotus 1-2-3 debuted in the early 1980's, from Mitch Kapor.
The short answer is that this statement

VBA Code:
ThisWorkbook.SaveAs Filename:="P:\Property\2023 B Upload \B9999\" & Range("Q11") & ".csv", FileFormat:=xlCSVUTF8, CreateBackup:=False

strips the VBA macros (since a CSV file cannot have macros), which requires permission that is being blocked if you have the VBA project protected.

You could try this instead (not tested)
VBA Code:
                Selection.NumberFormat = "_(* #,##0_);_(* (#,##0);_(* ""-""??_);_(@_)"
                ThisWorkbook.Save
                'ThisWorkbook.SaveAs Filename:="P:\Property\2023 B Upload \B9999\" & Range("Q11") & ".csv", FileFormat:=xlCSVUTF8, CreateBackup:=False
                'New code
                ThisWorkbook.Worksheets("Upload Copy").Copy
                With ActiveWorkbook
                .SaveAs Filename:="P:\Property\2023 B Upload \B9999\" & Range("Q11") & ".csv", FileFormat:=xlCSVUTF8, CreateBackup:=False
                .Close False
                End With
                'ActiveWorkbook.Save
               End If

(Tip: For future posts , you should try to use code tags like I did above when posting your code. It makes it easier to read.)

 
Upvote 0
Solution
The short answer is that this statement

VBA Code:
ThisWorkbook.SaveAs Filename:="P:\Property\2023 B Upload \B9999\" & Range("Q11") & ".csv", FileFormat:=xlCSVUTF8, CreateBackup:=False

strips the VBA macros (since a CSV file cannot have macros), which requires permission that is being blocked if you have the VBA project protected.

You could try this instead (not tested)
VBA Code:
                Selection.NumberFormat = "_(* #,##0_);_(* (#,##0);_(* ""-""??_);_(@_)"
                ThisWorkbook.Save
                'ThisWorkbook.SaveAs Filename:="P:\Property\2023 B Upload \B9999\" & Range("Q11") & ".csv", FileFormat:=xlCSVUTF8, CreateBackup:=False
                'New code
                ThisWorkbook.Worksheets("Upload Copy").Copy
                With ActiveWorkbook
                .SaveAs Filename:="P:\Property\2023 B Upload \B9999\" & Range("Q11") & ".csv", FileFormat:=xlCSVUTF8, CreateBackup:=False
                .Close False
                End With
                'ActiveWorkbook.Save
               End If

(Tip: For future posts , you should try to use code tags like I did above when posting your code. It makes it easier to read.)

Thank you! The untested code worked perfectly! I had tried changing from activeworkbook to this workbook; prior to reaching out. However, that wasn’t enough. Same issue. I even tried the untested code you provided with select instead of copy. That too didn’t work. When I changed my code to exactly what you provided, it the template works perfectly even though it’s code is protected. Thank you very much! Rlv01 you are awesome! Thx again. 🤗
 
Upvote 0

Forum statistics

Threads
1,213,552
Messages
6,114,278
Members
448,559
Latest member
MrPJ_Harper

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