+ Reply to Thread
Results 1 to 2 of 2

Using Send Keys to move data from Excel to Notepad and save as CSV

  1. #1
    Registered User
    Join Date
    10-21-2013
    Location
    Sydney
    MS-Off Ver
    Excel 2007
    Posts
    6

    Using Send Keys to move data from Excel to Notepad and save as CSV

    Hi All,

    I am currently working on a macro which brings data in from an external worksheet, manipulates it and saves it as a CSV. One of the problems I encountered was that some of the text was in Thai language characters and this showed up as question marks in the CSV output. I managed to find a way around this through using SendKeys to open notepad, paste data and save as, however when it got to the save as window I was required to manually:
    • Write .csv at the end of the file name
    • Select All Files as the Save As Type
    • Select UTF-8 as the Encoding

    And Here is my Code:

    Please Login or Register  to view this content.
    Any advice on how I could automate these three commands would be much appreciated!

    Thanks in advance

    Will

  2. #2
    Registered User
    Join Date
    10-21-2013
    Location
    Sydney
    MS-Off Ver
    Excel 2007
    Posts
    6

    Re: Using Send Keys to move data from Excel to Notepad and save as CSV

    I've managed to crack it, in case there's anyone else out there with a similar problem I've attached the code below:

    PHP Code: 
    Dim FPath As String
    Dim FPath2 
    As String
    Dim FPath3 
    As String
    Dim FPath4 
    As String

    With Application
    .FileDialog(msoFileDialogFolderPicker)
    .
    Show
    FPath3 
    = .SelectedItems(1)
    End With

    FPath 
    InputBox("Please Enter the name of your file")
    FPath2 FPath ".csv"

    SendKeys "% n"True
    Shell 
    "notepad.exe"vbNormalFocus
    SendKeys 
    "^V"True
    SendKeys 
    "%FA"True
    Application
    .Wait Now TimeValue("00:00:02")
    SendKeys FPath2True
    SendKeys 
    "%D"True
    SendKeys FPath3
    True
    SendKeys 
    "~"True
    Application
    .Wait Now TimeValue("00:00:02")
    SendKeys "%TU"True
    SendKeys 
    "{Down}"True
    SendKeys 
    "{Down}"True
    SendKeys 
    "~"True
    Application
    .Wait Now TimeValue("00:00:02")
    SendKeys "%E"True
    SendKeys 
    "{Down}"True
    SendKeys 
    "{Down}"True
    SendKeys 
    "{Down}"True
    SendKeys 
    "{Down}"True
    SendKeys 
    "~"True
    Application
    .Wait Now TimeValue("00:00:02")
    SendKeys "%S"True
    SendKeys 
    "%Y%FX "
    Application.Wait Now TimeValue("00:00:02"

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] how i make the excel send data or save it to another bad file
    By FSt1 in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 09-06-2005, 09:05 PM
  2. Replies: 0
    Last Post: 09-06-2005, 06:05 PM
  3. Replies: 0
    Last Post: 09-06-2005, 10:05 AM
  4. Replies: 0
    Last Post: 09-06-2005, 06:05 AM
  5. Replies: 0
    Last Post: 09-06-2005, 01:05 AM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1