Why is this code not saving to folder??

[wondering101]

trying to get a worksheet to saveas .csv file to path when they hit macro button. however it opens TempWB and and does not save to path. Can someone tell me what I am missing here

Thanks
CJ

[B]
Sub saveSheetToEQScan()

    Dim myCSVFileName As String
    Dim tempWB As Workbook

    Application.DisplayAlerts = False
    On Error GoTo err

    ' myCSVFileName = ThisWorkbook.Path & "\" & "Scan" & VBA.Format(VBA.Now, "dd-MMM-yyyy hh-mm") & ".csv"
    myCSVFileName = "\\mlb-app2p\ShopEQ\EQScan\" & "Scan" & VBA.Format(VBA.Now, "dd-MMM-yyyy hh-mm") & ".csv"
    
    

    ThisWorkbook.Sheets("Export For CSV").Activate
    ActiveSheet.Copy
    Set tempWB = ActiveWorkbook

    With tempWB
    .SaveAs Filename:=myCSVFileName, FileFormat:=xlCSV, CreateBackup:=False
    .Close
    End With
err:
    Application.DisplayAlerts = True
End Sub
[Code]
[/B]

 

[Logit]

.
Here is the macro I use to save as .CSV :

Option Explicit


Sub test()
Dim TempWB As Workbook
   
ActiveSheet.Copy
Set TempWB = ActiveWorkbook


Dim fd As FileDialog
    Dim sPath As String
    Set fd = Application.FileDialog(msoFileDialogFolderPicker)
    
    If fd.Show = -1 Then
        sPath = fd.SelectedItems(1)
    End If
    'sPath now holds the path to the folder or nothing if the user clicked the cancel button
  
With TempWB
    .SaveAs Filename:=sPath & "\CSV-Exported", FileFormat:=xlCSVWindows, CreateBackup:=False
    
    'use below to try your asc format to see if it saves ok for you
    'SaveAs Filename:=sPath & "\CSV-Exported.asc",  CreateBackup:=False
    .Close
    End With


End Sub

 

[wondering101]

Logit
This is getting me closer with your code. once I pick the path it automatically saves the file there however I want to have a defined folder where it always goes. I don't want the user to decide, is that possible? is there a variable that determines the path maybe I have the language wrong and I am looking for a parameter. just not sure what to use to define a path. thanks for your help
CJ

 

[Logit]

.

Option Explicit




Sub test()
Dim TempWB As Workbook
Dim fd As String
Application.DisplayAlerts = False
Application.ScreenUpdating = False
ActiveSheet.Copy
Set TempWB = ActiveWorkbook


fd = "C:\Users\My\Desktop\Test"   'Edit path as required. Assumes the folder Test already exists on the desktop
'fd now holds the path to the folder
  
With TempWB
'CSV-Exported is the name of the saved CSV file. Edit name as required.
    .SaveAs Filename:=fd & "\CSV-Exported", FileFormat:=xlCSVWindows, CreateBackup:=False
    .Close
End With


Application.DisplayAlerts = True
Application.ScreenUpdating = True


End Sub

 

[wondering101]

thank you logit I made a few modifications, it seems to work not sure if I did it in the best way open to suggestions. thank you very much for your help
CJ

Sub test()
Dim TempWB As Workbook
Dim fd As String
Application.DisplayAlerts = False
Application.ScreenUpdating = False
ActiveSheet.Copy
Set TempWB = ActiveWorkbook


fd = "\\mlb-app2p\Shop EQ\EQ Scan"  'Edit path as required. Assumes the folder Test already exists on the desktop
'fd now holds the path to the folder
  
With TempWB
'CSV-Exported is the name of the saved CSV file. Edit name as required.
    .SaveAs Filename:=fd & "\Scan" & VBA.Format(VBA.Now, "dd-MMM-yyyy hh-mm"), FileFormat:=xlCSVWindows, CreateBackup:=False
    .Close
End With


Application.DisplayAlerts = True
Application.ScreenUpdating = True


End Sub
[Code]

 

[Logit]

.
Looks good to me. There may be a different way of doing it (Excel formulas and macros can be written in so many different ways and still accomplish the same thing) but ..... HEY ! It works.

Good show !

Glad to help.