Macro Has File Name In Menu

ScatmanKyle

Board Regular
Joined
Oct 26, 2015
Messages
61
Office Version
  1. 365
Platform
  1. Windows
Hi again!

I noticed one of my macros seems to have the file name in it when I try to assign it, as if it belonged to a separate workbook.

The macro essentially runs through specific sheets (based on index) and saves them to a chosen folder as separate csvs. When I run it through this, the date section of the name messes up, but when I run it directly from the macro screen, it works fine.

1611606406505.png



VBA Code:
Sub CSV_Export()

Dim ws As Worksheet
Dim strFileName As String
Dim strFolderPath As String
Dim strShtType As String
Dim strFullPath As String
Dim strReplace As String
Dim fso As Object

Set fso = CreateObject("Scripting.Filesystemobject")

'Lets you choose the folder to save the csv files in
With Application.FileDialog(msoFileDialogFolderPicker)
    If .Show = -1 Then
        strFolderPath = .SelectedItems(1)
    Else
        Exit Sub
    End If
End With

'Runs the code for the csv tabs only (tab positions 11 through 15)
For Each ws In ThisWorkbook.Worksheets
    Select Case ws.Index
        Case 11 To 15
            'Skips tabs with no invoice data
            If ws.Range("A16") = "" Then
                GoTo NextCase
            Else
                'Stores the name and directory the file will be saved as
                strFileName = "CDS " & Format(Month(Range("C4")), "00") & Format(Day(Range("C4")), "00") & Year(Range("C4")) & Right(ws.Name, Len(ws.Name) - 3)
                strFullPath = strFolderPath & "\" & strFileName & ".csv"
                
                'Checks if a file of the same name already exists in the chosen folder. If yes, offers the option to replace it. If no, it continues to the next tab
                If Dir(strFullPath) <> "" Then
                    strReplace = MsgBox("File " & strFileName & ".csv already exists in folder. Do you want to replace it?", vbQuestion + vbYesNo + vbDefaultButton2, "File Already Exists")
                    If strReplace = vbYes Then
                        GoTo SaveFile
                    Else
                        GoTo NextCase
                    End If
                Else
SaveFile:
                    ws.Copy
                    Application.DisplayAlerts = False
                    Application.ActiveWorkbook.SaveAs Filename:=strFullPath, FileFormat:=xlCSV, CreateBackup:=False, local:=True
                    Application.ActiveWorkbook.Saved = True
                    Application.DisplayAlerts = True
                    Application.ActiveWorkbook.Close False
                End If
            End If
    End Select

NextCase:
Next ws

End Sub
 

Some videos you may like

Excel Facts

VLOOKUP to Left?
Use =VLOOKUP(A2,CHOOSE({1,2},$Z$1:$Z$99,$Y$1:$Y$99),2,False) to lookup Y values to left of Z values.

t0ny84

Board Regular
Joined
Jul 6, 2020
Messages
134
Office Version
  1. 365
  2. 2016
  3. 2013
Platform
  1. Windows
  2. Mobile
  3. Web
Hi ScatmanKyle,

As you have Macros In (at the bottom of the Assign Macro screen) it would be picking up all macros in all open Excel files. Should CSV_Export code be in "CDS Macro V10.xlsm" or the workbook you are currently working (if not "CSD Macro V10.xlsm"?
I would recommend confirming the code is in the correct workbook then closing all open Excel documents and and reopen your main Excel Workbook and check if you still get the same showing in the Assign Macro screen.
 

Watch MrExcel Video

Forum statistics

Threads
1,128,125
Messages
5,628,851
Members
416,344
Latest member
Maug2004

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
Top