Save Excel Workbook to user's desktop in text format

Aashna

New Member
Joined
Jun 17, 2015
Messages
19
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

Well-known Member
Joined
Jun 10, 2015
Messages
898
You have an open With statement without matching End With.

Code:
'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

Well-known Member
Joined
Mar 17, 2008
Messages
1,871
Office Version
2010, 2007
Platform
Windows
Hello Aashna,

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

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

New Member
Joined
Jun 17, 2015
Messages
19
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

Well-known Member
Joined
Jun 10, 2015
Messages
898
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

New Member
Joined
Jun 17, 2015
Messages
19
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

Well-known Member
Joined
Sep 5, 2004
Messages
1,417
If you right click on the Export Button and select "Assign Macro", does it refer to the right macro in the active workbook?
 

Aashna

New Member
Joined
Jun 17, 2015
Messages
19
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

New Member
Joined
Jun 17, 2015
Messages
19
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.

Code:
               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

Well-known Member
Joined
Mar 17, 2008
Messages
1,871
Office Version
2010, 2007
Platform
Windows
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.
 

Forum statistics

Threads
1,082,345
Messages
5,364,817
Members
400,814
Latest member
gangstar67

Some videos you may like

This Week's Hot Topics

  • populate from drop list with multiple tables
    Hi All, i have a drop list that displays data, what i want is when i select one of those from the list to populate text from different tables on...
  • Find list of words from sheet2 in sheet1 before a comma and extract text vba
    Hi Friends, Trying to find the solution on my task. But did not find suitable one to the need. Here is my query and sample file with details...
  • Dynamic Formula entry - VBA code sought
    Hello, really hope one of you experts can help with this - i've spent hours on this and getting no-where. .I have a set of data (more rows than...
  • Listbox Header
    Have a named range called "AccidentsHeader" Within my code I have: [CODE]Private Sub CommandButton1_Click() ListBox1.RowSource =...
  • Complex Heat Map using conditional formatting
    Good day excel world. I have a concern. Below link have a list of countries that carries each country unique data. [URL...
  • Conditional formatting
    Hi good morning, hope you can help me please, I have cells P4:P54 and if this cell is equal to 1 then i want row O to say "Fully Utilised" and to...
Top