Results 1 to 3 of 3
  1. #1
    New Lounger
    Join Date
    Jan 2003
    Location
    Fontana, California, USA
    Posts
    2
    Thanks
    0
    Thanked 0 Times in 0 Posts

    send uncommitted value to Excel (2000)

    Hi, I'm new here. My little Access project relies on using Excel to read text format files and import the info into Access and spit out a report. I have a series of small macros to run everything. My dilema is, I need to get an uncommitted value from an Access form textbox and send that value to an Excel sheet. It's a date function, Today(). But, I need for the user to be able to put in any day they want and at a click of a button, the date is saved into an Excel sheet.
    I've tried using the SendKeys function and trying something like:

    Dim NewValue as String

    NewValue =Me!textbox1.text '(.value grabs committed value, .text will get uncommitted value)

    Dim objXL As Object, x
    On Error Resume Next
    Set objXL = CreateObject("Excel.Application")
    With objXL.Application
    .Visible = True
    'Open the Workbook
    .workbooks.Open "C:TempEDI Project.XLS"
    'insert date from Access
    excel_sheet.Cells(6, 2) = NewValue
    'Activate Macros
    .ActiveWorkBook.RunAutoMacros xlAutoOpen
    x = .Run("DirFileList") 'lists current EDI files
    x = .Run("ScanEDI") 'parses EDI files for report
    End With


    Thanks to MarkD, in a previous posting. I borrowed and tried the OutputTo method as shown below:

    DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel8, "qrytemp", "C:tempEDI Project.XLS"

    This gets my input (NewValue) into the Excel sheet, but only assuming it's a committed value read from a table.
    Any thoughts would help.

    Thanks, -patrick

  2. #2
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: send uncommitted value to Excel (2000)

    I'm a bit <img src=/S/confused.gif border=0 alt=confused width=15 height=20> about what you are trying to do, and why you need to import a text file into Excel first to then import it into Access. Access has text import capabilities that are comparable to those in Excel, so why not import it directly into Access? In addition, I don't understand where the user is typing in a date. I presume they are typing it into Access, but is it into a form, or an InputBox or what? And when you say you are creating a report, is it an Access report, or is it a "report" in Excel?
    Wendell

  3. #3
    New Lounger
    Join Date
    Jan 2003
    Location
    Fontana, California, USA
    Posts
    2
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: send uncommitted value to Excel (2000)

    Thanks Woody-

    The project has been modified and no longer requires Access. The reason I had a hard time importing directly into Access, is the file extensions are not .txt but a 9 character string that changes daily. It includes the date. So a file created 1-5-2003 would have an extension .cc01052003ccccc; where the 'c' is a stand-in for other variables such as a counter.

    But what I was really after was just grabbing the uncommitted value in a textbox (not saving to a table) from Access and copying it into Excel using a macro. The little program needs to run without user interface/ interference.

    Thanks,

    -patrick

Posting Permissions

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