Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Excel VBA / add VBA SaveAs

Posted on 2014-09-23
2
Medium Priority
?
332 Views
Last Modified: 2014-09-23
Hi,

i need to change the path of the file from Desktop to variable. Save_file_dialog. I tried different variations wo success.
FileExtStr = ".csv": FileFormatNum = 6

Which of the VBA experts could help me?

Thanks in advance
Mandy

Sub csv_Export()
    Dim lastColumn As Integer
    Dim lastRow As Integer
    Dim strString As String
    Dim i As Integer, j As Integer
    Dim UD As String
    
    UD = CreateObject("WScript.Shell").specialfolders("Desktop")
    UD = UD & "\export.csv"
    Worksheets("TEST").Activate
    lastColumn = ActiveSheet.UsedRange.Column - 1 + ActiveSheet.UsedRange.Columns.Count
    lastRow = ActiveSheet.UsedRange.Rows(ActiveSheet.UsedRange.Rows.Count).Row

    Open UD For Output As #1

    For i = 1 To lastRow
        Cells(i, 1).Select
        strString = ""
        For j = 1 To lastColumn
            If j <> lastColumn Then
                strString = strString & Cells(i, j).Value & ";" ' Use semicolon instead of pipe.
            Else
                strString = strString & Cells(i, j).Value
            End If
        Next j
        If Len(Trim$(Replace(strString, ";", ""))) > 0 Then
        Print #1, strString
        End If
     Next i

    Close #1
End Sub

Open in new window

0
Comment
Question by:Mandy_
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
2 Comments
 
LVL 15

Accepted Solution

by:
Haris Djulic earned 2000 total points
ID: 40340136
Here is the code for option to prompt the user to chose the file name and location while the extension is fixed to CSV...

Sub csv_Export()
    Dim lastColumn As Integer
    Dim lastRow As Integer
    Dim strString As String
    Dim i As Integer, j As Integer
    Dim UD As String
    
    Dim filename As Variant
   'user will be prompted to choose file name and location while the extension is fixed
    filename = Application.GetSaveAsFilename(FileFilter:="CSV files (*.csv), *.csv")
    UD = filename
    
    Worksheets("TEST").Activate
    lastColumn = ActiveSheet.UsedRange.Column - 1 + ActiveSheet.UsedRange.Columns.count
    lastRow = ActiveSheet.UsedRange.Rows(ActiveSheet.UsedRange.Rows.count).Row

    Open UD For Output As #1

    For i = 1 To lastRow
        Cells(i, 1).Select
        strString = ""
        For j = 1 To lastColumn
            If j <> lastColumn Then
                strString = strString & Cells(i, j).Value & ";" ' Use semicolon instead of pipe.
            Else
                strString = strString & Cells(i, j).Value
            End If
        Next j
        If Len(Trim$(Replace(strString, ";", ""))) > 0 Then
        Print #1, strString
        End If
     Next i

    Close #1
End Sub

Open in new window

0
 
LVL 2

Author Closing Comment

by:Mandy_
ID: 40340919
perfect. Thank you so much
0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Do you use a spreadsheet like Microsoft's Excel?  Have you ever wanted to link out to a non excel file on your computer or network drive?  This is the way I found to do it!
You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.

670 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question