• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 243
  • Last Modified:

Creating a control to create a flat text file using data from worksheet column...

How do I add a control button to an Excel Worksheet that will do the following:

When pressed, a flat text file called "mysettings.text" will be created and saved to "C:/anydirectory" using the contents from all cells in column G that have data in it.

Joel
0
Joel_Sisko
Asked:
Joel_Sisko
  • 5
  • 2
1 Solution
 
Curt LindstromCommented:
0
 
Curt LindstromCommented:
Maybe the following macro will do the job. Insert this macro in a module and then save your file before trying since the original file will close without being saved when you run the macro.

    Sub copy_text_in_G()
   
    Dim Default_directory As String, wb As Workbook, Wbname As String
   
    Application.ScreenUpdating = False
    Wbname = "Textfile.csv"
    Default_directory = "C:/MyDir" 'Your default directory
    Sheets("Sheet1").Select
    Range("G:G").Select
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=True, Transpose:=False
    Set wb = ActiveWorkbook
    On Error Resume Next
    Range("G1:G65536").SpecialCells(xlCellTypeBlanks).EntireRow.Delete
    wb.SaveAs Default_directory & "/" & Wbname, FileFormat:=xlCSV, CreateBackup:=False
    wb.Close True
    Application.ScreenUpdating = True
   
    End Sub

BR,
Curt
0
 
Curt LindstromCommented:
I forgot the bit about a command button.
Use the control toolbox. Select a command button. Draw button on sheet1. Right click button and View code. Copy the sub like this

Private Sub CommandButton1_Click()

    Dim Default_directory As String, wb As Workbook, Wbname As String
   
    Application.ScreenUpdating = False
    Wbname = "Mysettings.csv"
    Default_directory = "C:/anydirectory" 'Your default directory
    Sheets("Sheet1").Select
    Range("G:G").Select
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=True, Transpose:=False
    Set wb = ActiveWorkbook
    On Error Resume Next
    Range("G1:G65536").SpecialCells(xlCellTypeBlanks).EntireRow.Delete
    wb.SaveAs Default_directory & "/" & Wbname, FileFormat:=xlCSV, CreateBackup:=False
    wb.Close True
    Application.ScreenUpdating = True
   
End Sub

Save file before trying

BR,
Curt
0
Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

 
Joel_SiskoAuthor Commented:
Ok the entire worksheet is being copied to the text file, Columns A-G.

Joel
0
 
Curt LindstromCommented:
Maybe this is what you want? Copies only column G without the spaces.

Private Sub CommandButton1_Click()

    Dim Default_directory As String, wb As Workbook, Wbname As String
   
    Application.ScreenUpdating = False
    Wbname = "Mysettings.csv"
    Default_directory = "C:/anydirectory" 'Your default directory
    Sheets("Sheet1").Select
    Set wb = ActiveWorkbook
    On Error Resume Next
    Range("G1:G65536").SpecialCells(xlCellTypeBlanks).EntireRow.Delete
    On Error GoTo 0
    Range("A:F").EntireColumn.Delete
    wb.SaveAs Default_directory & "/" & Wbname, FileFormat:=xlCSV, CreateBackup:=False
    wb.Close True
    Application.ScreenUpdating = True
   
End Sub

Save file before trying

BR,
Curt
0
 
Joel_SiskoAuthor Commented:
Worked better than a champ!!!!!!!!
0
 
Curt LindstromCommented:
Glad that I could help you! Thanks for the grade!

BR,
Curt
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: Ruby Fundamentals

This course will introduce you to Ruby, as well as teach you about classes, methods, variables, data structures, loops, enumerable methods, and finishing touches.

  • 5
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now