1. Welcome to Chandoo.org Forums. Short message for you

    Hi Guest,

    Thanks for joining Chandoo.org forums. We are here to make you awesome in Excel. Before you post your first question, please read this short introduction guide. When posting or responding to questions please remember our values at Chandoo.org are: Humility, Passion, Fun, Awesomeness, Simplicity, Sharing Remember that we have people here for whom English is not there first language and we need to allow for this in our dealings.

    Yours,
    Chandoo
  2. Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  3. When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

how to create a command button that copy and create a notepad file

Discussion in 'Ask an Excel Question' started by trustmeasfrnd, Feb 23, 2014.

  1. trustmeasfrnd

    trustmeasfrnd New Member

    Messages:
    11
    hi ,
    i am new and i was trying to learn macro thing so that i can do one simple thing(for me its hard doing in vba). i wanted to copy sheet1 from excel to notepad file (test.txt) and then rename it to test.doc .i tried to record macro and i was able to record the macro for only doing copying but how to write it in other file and saving it then renaming is not recorded. how can it be recorded in macro?. any idea.

    regards,
    Abhisekh
  2. Debraj

    Debraj Excel Ninja

    Messages:
    2,132
    Hi Frnd!

    Did you tried to record the macro.. by saving the file as a CSV file..
    give it a try.. may be works.. :)
  3. trustmeasfrnd

    trustmeasfrnd New Member

    Messages:
    11

    debraj,
    i tried but i was only able to record whatever i was doing on excel sheet in macro when i copied the data to note pad it was not recorded in macro.
  4. shrivallabha

    shrivallabha Excel Ninja

    Messages:
    1,884
    Could you explain your exact requirement?
    Excel Sheet1 --> Txt File --> Word Document

    Does txt file serve some purpose?
  5. trustmeasfrnd

    trustmeasfrnd New Member

    Messages:
    11
    text file can't be formatted and can be stored,word file can be formatted when required.
  6. shrivallabha

    shrivallabha Excel Ninja

    Messages:
    1,884
    OK. So will it be fine to save the contents of Excel file to Word document directly?

    It will be good if we can see your first sheet (dummy) data which represents your actual case and one word document as you'd expect it to be post copy paste. It will be useful if there's some sequence to be followed.

    Disclaimer: I have not tried this before so expect some time before I can reply if you choose to post sample files.
  7. trustmeasfrnd

    trustmeasfrnd New Member

    Messages:
    11
    Shrivallabha,
    please find attached file as requested

    Attached Files:

  8. Lymm

    Lymm Member

    Messages:
    170
    Hi, How do you want this information presented in word? You may needto supply a word doc so we know what to do with your data.
  9. shrivallabha

    shrivallabha Excel Ninja

    Messages:
    1,884
    So you are in some Structural Engineering field?

    Copy the code below and paste it in VBE Window and then run the macro Export_To_Word through macro dialog box by choosing "Run Macro" or alternatively pressing ALT+F8 (which I think you know):
    Code (vb):

    Public Sub Export_To_Word()
    Dim wdApp As Object
    Dim wdDoc As Object
    Dim wkSht As Worksheet

    '\\ Stay on any sheet from which you want to copy data
    Set wkSht = ActiveSheet
    wkSht.UsedRange.Copy

    '\\ Start word and create new document to paste data
    On Error Resume Next
    Set wdApp = GetObject(, "Word.Application")
    If wdApp Is Nothing Then
      Set wdApp = CreateObject("Word.Application")
      wdApp.Visible = True
    End If
    Set wdDoc = wdApp.Documents.Add

    '\\ Paste Data from Excel
    wdDoc.Range.PasteExcelTable False, False, True

    '\\ Stop Excel's cut copy mode
    Application.CutCopyMode = False

    MsgBox "Copy to Word Finished!", vbInformation, "Copy to Word"
    End Sub
     
  10. trustmeasfrnd

    trustmeasfrnd New Member

    Messages:
    11
    hmm, i tried but its just open a word file and nothing gets copied there, its basically blank.
  11. trustmeasfrnd

    trustmeasfrnd New Member

    Messages:
    11
    i want raw data... nothing special
  12. shrivallabha

    shrivallabha Excel Ninja

    Messages:
    1,884
    Here's what I got. The attached file contains macro I've posted as it is and the output.

    The word file is not saved automatically. You will have to save it.

    Attached Files:

    Thomas Kuriakose likes this.
  13. trustmeasfrnd

    trustmeasfrnd New Member

    Messages:
    11
    Hmm, on my computer its not working may be due to version,(i have Excel 2010.)
    and what should i replace so that it gets copied in notebook because i think in word it gets copied in table format.
  14. shrivallabha

    shrivallabha Excel Ninja

    Messages:
    1,884
    I use Excel 2010 at home. So the code I posted should work for you.

    You've not posted expected output (Lymm has requested twice and me once). Unless you tell us how it should look we won't know. Care to post output samples?
  15. trustmeasfrnd

    trustmeasfrnd New Member

    Messages:
    11
    ok,
    i am attaching output should look like this.

    Attached Files:

  16. shrivallabha

    shrivallabha Excel Ninja

    Messages:
    1,884
    Thank you for uploading data.

    I am uploading workbook with one button to export data. On pressing this button, it should create two output files for you with the same names in the folder where you'll keep this excel file e.g. if file name is input.xlsm then output files will be input.txt and input.docx respectively.

    Here's the code for those who can't download file.
    Code (vb):

    Option Explicit
    Private Sub cmdExport_Click()
    Dim lngLastRow As Long, lngLastCol As Long, i As Long, j As Long
    Dim varOutput() As String
    Dim strLine As String, strOutput As String, strFile As String, strFilePath
    Dim intFile As Integer
    Dim wdApp As Word.Application
    Dim wdDoc As Word.Document

    '\\ Get Grid Data
    lngLastRow = Cells(Rows.Count, "A").End(xlUp).Row
    lngLastCol = Cells.Find("*", [A1], xlFormulas, xlPart, xlByColumns, xlPrevious).Column

    '\\ To load output we use Array which we'll use in creating output files
    ReDim varOutput(lngLastRow - 1)

    '\\ Loop through data grid
    For i = 1 To lngLastRow
      strLine = ""
      For j = 1 To lngLastCol
      If Len(Cells(i, j).Value) <> 0 Then
      If Len(strLine) = 0 Then
      strLine = Cells(i, j).Value
      Else
      strLine = strLine & vbTab & Cells(i, j).Value
      End If
      End If
      varOutput(i - 1) = strLine
      Next j
    Next i

    '\\ Combine output
    strOutput = Join(varOutput, vbCrLf)

    '\\ Get FileName and write output to it
    strFile = Split(ThisWorkbook.Name, ".")(0)
    strFilePath = ThisWorkbook.Path & Application.PathSeparator & strFile & ".txt"
    intFile = FreeFile
    Open strFilePath For Output As #intFile
    Print #intFile, strOutput
    Close #intFile

    '\\ Open the file created in notepad
    Shell "notepad.exe " & Chr(34) & strFilePath & Chr(34), vbNormalFocus

    '\\ Start word and create new document to paste data
    On Error Resume Next
    Set wdApp = GetObject(, "Word.Application")
    If wdApp Is Nothing Then
      Set wdApp = CreateObject("Word.Application")
      wdApp.Visible = True
    End If

    '\\ Create New document
    On Error Resume Next
    strFilePath = ThisWorkbook.Path & Application.PathSeparator & strFile & ".docx"
    Set wdDoc = wdApp.Documents.Open(strFilePath)
    On Error GoTo 0
    If Not wdDoc Is Nothing Then
      wdDoc.Range.Delete
    Else
      Set wdDoc = wdApp.Documents.Add
    End If

    '\\ Save data to document
    wdDoc.Range.Text = strOutput
    wdDoc.SaveAs2 strFilePath

    End Sub

    Attached Files:

    Thomas Kuriakose likes this.
  17. trustmeasfrnd

    trustmeasfrnd New Member

    Messages:
    11
    Thank you its working.
    one more thing when we use to work in excel some times we have to print pages in specific cell can it be done using vba.
  18. shrivallabha

    shrivallabha Excel Ninja

    Messages:
    1,884
    Great! Thanks for the feedback.

    Almost anything that you can do natively can be done with VBA. You could record the macro for printing and see if you could get something useful there.
  19. indra urip widodo

    indra urip widodo New Member

    Messages:
    2
    Hallo,
    Saya indra dari indonesia, hari ini saya ingin menanyakan masalah materi macro. dalam hal ini saya ingin menanyakan bagaimana caranya membentuk file Txt/notepad dari data excel dan setiap baris data terbentuk file txt/notepad misalkan data pada excel ada 10 baris maka terbentuk pula 10 file txt/notepad.

    Misalnya:


    Terimakasih banyak atas perhatiannya.

    Attached Files:

    Last edited: Jun 13, 2018
  20. NARAYANK991

    NARAYANK991 Excel Ninja

    Messages:
    16,619
    Courtesy AlanSidman :

    I am from indonesia, today i want to ask macro material problem. in this case I want to ask how to form file Txt / notepad from data excel and every row of data formed file txt / notepad let data in excel there are 10 rows hence also form 10 file txt / notepad.

    Thank you very much for your attention.

    Hi ,

    I am not sure members of this forum will be able to understand your requirement only from this description.

    If you can upload an Excel workbook with data in it , and if you can provide an output file in text format , showing how the Excel worksheet data has been converted into the output file in text format , it will help.

    It will be better if you start a new thread for this question , since it will be viewed by more members.

    Narayan
  21. indra urip widodo

    indra urip widodo New Member

    Messages:
    2
    Hallo,

    Berikut saya kirimkan file outputnya.

    Terimakasih sebelumnya.

    Salam,
    Indra.

    Attached Files:

  22. NARAYANK991

    NARAYANK991 Excel Ninja

    Messages:
    16,619
    Hi ,

    These are all text files ; I assume they are output files.

    Can you upload the corresponding Excel workbook , so that we can get a better idea of the data layout in the worksheet , and how it corresponds to the data layout in the output text file ?

    Narayan

Share This Page