Save Excel Workbook to user's desktop in text format

[Aashna]

Hi there,

I've an Excel Workbook (SAP Upload File) that has an Export Button, on clicking it I want to ave a copy of that active worksheet to the user's desktop. After running the below code it is showing a compile error 'Expected End with'.
If someone could check this out would be awesome. Please let me know if you need any further information.

Thanks!
____________________________________________________________________________
Sub Export()
Dim FileExtStr As String
Dim FileFormatNum As Long
Dim Sourcewb As Workbook
Dim Destwb As Workbook
Dim sh As Worksheet
Dim DateString As String
Dim FolderName As String


With Application
.ScreenUpdating = False
.EnableEvents = False
End With

'Save file to users desktop
Dim TempFilePath As String
Dim TempFileName As String
Set Destwb = ActiveWorkbook

TempFilePath = "C:\Users\" & Environ$("Username") & "\Desktop\"
TempFileName = fName & "_allocation"
FileExtStr = ".txt": FileFormatNum = -4158

With Destwb
.SaveAs TempFilePath & TempFileName & FileExtStr, _
FileFormat:=FileFormatNum
On Error Resume Next
On Error GoTo 0
.Close savechanges:=False
End With
'Change all cells in the worksheet to values if you want
If Destwb.Sheets(1).ProtectContents = False Then
With Destwb.Sheets(1).UsedRange
.Cells.Copy
.Cells.PasteSpecial xlPasteValues
.Cells(1).Select
End With
Application.CutCopyMode = False



'Save the new workbook and close it
With Destwb
fName = Range("G1").Value

ActiveWorkbook.SaveAs Filename:="C:\Users\" & Environ$("Username") & "\Desktop\" & fName & "_Advanced_Ladder_upload" & "_R" & "_" & SectionYear & SectionMonth & SectionDay & SectionHour & SectionMinute & SectionSecond & "", _
FileFormat:=xlText, CreateBackup:=False




MsgBox "You can find the files in " & FolderName


With Application
.ScreenUpdating = True
.EnableEvents = True
.Calculation = xlCalculationAutomatic
End With
End Sub

 

[V_Malkoti]

You have an open With statement without matching End With.

'Save the new workbook and close it
[COLOR=#ff0000]With Destwb[/COLOR]
fName = Range("G1").Value

Either place an End With after this or remove this With statement altogether.

 

[Leith Ross]

Hello Aashna,

This types of errors are easily caught if you indent your code...

Sub Export()

    Dim FileExtStr As String
    Dim FileFormatNum As Long
    Dim Sourcewb As Workbook
    Dim Destwb As Workbook
    Dim sh As Worksheet
    Dim DateString As String
    Dim FolderName As String
    Dim TempFilePath As String
    Dim TempFileName As String


        With Application
            .ScreenUpdating = False
            .EnableEvents = False
        End With

        'Save file to users desktop
        Set Destwb = ActiveWorkbook

        TempFilePath = "C:\Users\" & Environ$("Username") & "\Desktop\"
        TempFileName = fName & "_allocation"
        FileExtStr = ".txt": FileFormatNum = -4158

        With Destwb
            .SaveAs TempFilePath & TempFileName & FileExtStr, FileFormat:=FileFormatNum
            .Close savechanges:=False
        End With
        
        'Change all cells in the worksheet to values if you want
        If Destwb.Sheets(1).ProtectContents = False Then
            With Destwb.Sheets(1).UsedRange
                .Cells.Copy
                .Cells.PasteSpecial xlPasteValues
                .Cells(1).Select
            End With
            
            Application.CutCopyMode = False

            'Save the new workbook and close it
            With Destwb
                fName = Range("G1").Value

                ActiveWorkbook.SaveAs Filename:="C:\Users\" & Environ$("Username") & "\Desktop\" _
                                                & fName & "_Advanced_Ladder_upload" & "_R" & "_" _
                                                & SectionYear & SectionMonth & SectionDay & _
                                                SectionHour & SectionMinute & SectionSecond & "", _
                                      FileFormat:=xlText, CreateBackup:=False

                MsgBox "You can find the files in " & FolderName
            End With

            With Application
                .ScreenUpdating = True
                .EnableEvents = True
                .Calculation = xlCalculationAutomatic
            End With
        End If
        
End Sub

 

[Aashna]

Thanks much for your response Leith & V_Malkoti. Appreciated.

Leith - It worked!

But there is a situation, after clicking on the Export button on the worksheet a dialog box pop up with a quote "Cannot run macro. The macro may not be available in this workbook or all macros may be disabled".
Do you guys have any idea about this, what can be the reason behind this message. And also, after running the code though the file is getting saved on desktop but the sheets go blank for which I have to close them and open again.

 

[V_Malkoti]

1. Does the file still have macros code after export? There may not be any code if you didn't save file as macro supported format (xls or xlsm).
2. Have you enabled macros in your workbook?
3. If you didn't see the yellow ribbon to enable it, then check your trust center settings (Options -> Trust Center -> Trust Center Settings -> Macro Settings)

 

[Aashna]

1. Yes the macro code exist after export.
2. Yes, macros are enabled

I am kinda finding it hard what might be going wrong.

 

[jolivanes]

If you right click on the Export Button and select "Assign Macro", does it refer to the right macro in the active workbook?

 

[Aashna]

I would prefer sending screenshots as it will be easier to understand the issue.

Is there a way I can post images under this thread?

 

[Aashna]

Sorry for these many questions, I am new to VBA.

I am receiving a compile error "Expected: list separator or" for the below code. Can the issue be resolved?
Your help will be much appreciated.

               m = MsgBox("File has been saved to your Desktop. If updates to data are required information should be reprocessed through this template. REPROCESSING OF THE SAME INFORMATION WILL OVERWRITE PREVIOUS FILE if it is not renamed or moved from Desktop.", vbOKOnly, TempFilePath & TempFileName & FileExtStr)

 

[Leith Ross]

Hello Aashna,,

The code syntax is correct. You should not be getting that error on that line.

I would save the workbook, close Excel, reopen Excel, and reload the workbook. This usually cures odd behaviour in your VBA project. If not then you may need to defragment your drive and registry.