none
Exporting Data using VBA to an Existing Excel File RRS feed

  • Question

  • Hello All,

    Have a question that I hope someone might be able to answer.  Before I begin for the record, I searched the following topics:

    export to excel
    send data to excel file
    send data to open excel file

    Before I posted this question.  I didn't see an answer that applied to what I'm trying to solve here.  I am trying to export Project Data to an Excel file that is already saved, named, and open.  This has proven surprisingly difficult for me as a non-developer, but maybe the answer will be obvious to developers on here.  Ordinarily if I wanted to export data to Excel from Project using VBA I would use something like the following:

    Sub ExportExample
    Dim t as Task
    Dim xlApp As Object
    Set xlApp = CreateObject("Excel.Application")
    xlApp.Visible = True
    Set xlBook = xlApp.Workbooks.Add
    Set xlSheet = xlBook.Worksheets.Add
    Set xlRow = xlApp.ActiveCell
    'Export Task Name to Excel in A column, starting at A1
    For Each t In ActiveSelection.tasks
     xlApp.ActiveCell.Offset(NextRow, 0)= t.Name
     NextRow = NextRow + 1
    next t
    
    Set xlApp = Nothing
    End Sub

     

    This will input the data into the Sheet, and workbook I just created.  But what I need to do, is the exact same process except I don't need to create a new sheet or workbook, I need to use a workbook that is already open.  I already have code to detect if that workbook is open, I just need to figure out how to tell project to send the data to that specific workbook and worksheet.  Is this a simple thing?  How can I accomplish this?

    Any suggestions would be greatly appreciated.

    Friday, December 9, 2011 8:50 PM

Answers

  • Hey all,

     

    I think I found something that might be useful to us all.  I'm not sure why the way I approached it before didn't work, it seems as though it should given as though it came directly from microsoft support, but in continuing to investigate I found another way to directly attach to a specific open workbook when many other workbooks are open.   

     

    SomePath = "C:\Users\example\Documents\Sample.xls"
    Set xlApp = GetObject(SomePath).Parent
    xlApp.Application.Visible = True
    xlApp.Parent.Windows(1).Visible = True
    Set xlRow = xlApp.ActiveCell

    testval = xlRow.Offset(0, 0)  '
       
    Application.Visible = True    'Make project the visible application again

    I do not represent that this is the best or the only way to directly attach to an open workbook, but it is at least one way that worked for me.  Hopefully this information will be useful to others as well.

    • Marked as answer by B Fennell Friday, December 16, 2011 4:14 PM
    Friday, December 16, 2011 4:14 PM

All replies

  • Instead of CreateObject() use GetObject(,"Excel.Application") - note the leading , before "Excel etc.

     

    If you want to work with timephased data, MSDN has teh relevant chapter from my VBA book, see:

    http://msdn.microsoft.com/en-us/library/ee355231(v=office.12).aspx


    Rod Gill

    The one and only Project VBA Book Rod Gill Project Management
    Friday, December 9, 2011 9:24 PM
    Moderator
  • Ok I think I'm on the right road but I don't quite have it yet.  After your suggestion I looked into "GetObject" and came across this:

    http://support.microsoft.com/kb/288902

    I ran a test module to make sure that I had this correct:

    Sub TestRunBinding()
    WorkbookName = "XLtest.xls"
    Dim xlApp As Excel.Application
    Set xlApp = GetObject(WorkbookName).Application
    Set xlRow = xlApp.ActiveCell
    testval = xlRow.Offset(0, 0)
    Set xlApp = Nothing
    End Sub


    I have a spreadsheet called XLtest.xls that has text "ABCD" entered in cell "A1", and I saved the file with "A1" being the active cell.  I also kept another spreadsheet open to make sure that the program would correctly distinguish between the two, but it did not.  The "testval" came from the other open spreadsheet instead of the specified one.  What step am I missing?  xlApp should have been set to "XLtest.xls" right?  So the Activecell should also be in XLtest.xls no?

    Saturday, December 10, 2011 6:51 PM
  • Hi,

    set therightbook=xlapp.activeworkbook

    testval=therightbook.sheets(1).cells(1,1).value

    That will address the right book (providing it is active)

    If not run through the workbooks collection to check the name then set the right book as an obkject.

    Greetings,

    Saturday, December 10, 2011 7:58 PM
    Moderator
  • I'm not sure why but I'm not even getting that to work either Jan. 

     

    I have been looking around on the web to use the GetFunction, and apparently other people have run into this problem but I still haven't seen the solution.  The GetObject(,"Excel.Application") works if my specific file is the only Excel file open or if it's the first one opened because I have omitted the string path correct?  I specify the class as "Excel.Application" and leave the string path blank and thus the configuration is:

     

    GetObject({no string path}, {class}).  So specifying the path should fix that problem, right?  But for me it didn't.  It says either the file name or the class name cannot be found.  It seems impossible that it could be the class name, it works just fine without the string path.    The problem I have here is that I cannot guarantee that users will only have the one and only specified Excel Spreadsheet open that I need for the export of data.  My only other option (if I can't find any coded work around) is to require the user to shut down all other spreadsheets and open a specific spreadsheet before continuing forward. 

     

    Thoughts?

    Monday, December 12, 2011 1:02 AM
  • Hi,

    I did a test and the activeworkbook is always the one I used in the Getobject. I have to say I used the full path and not just the name. But still, the emainder of my advoce is still valid:

    If not run through the workbooks collection to check the name then set the right book as an object.

    For each LIttleBook in xlapp.workbooks

    if littlebook.name=workbookname then

    set therightbook=littlebook

    exit for

    next littlebook

    Greetings,

     

     

    Monday, December 12, 2011 2:21 PM
    Moderator
  • Hey all,

     

    I think I found something that might be useful to us all.  I'm not sure why the way I approached it before didn't work, it seems as though it should given as though it came directly from microsoft support, but in continuing to investigate I found another way to directly attach to a specific open workbook when many other workbooks are open.   

     

    SomePath = "C:\Users\example\Documents\Sample.xls"
    Set xlApp = GetObject(SomePath).Parent
    xlApp.Application.Visible = True
    xlApp.Parent.Windows(1).Visible = True
    Set xlRow = xlApp.ActiveCell

    testval = xlRow.Offset(0, 0)  '
       
    Application.Visible = True    'Make project the visible application again

    I do not represent that this is the best or the only way to directly attach to an open workbook, but it is at least one way that worked for me.  Hopefully this information will be useful to others as well.

    • Marked as answer by B Fennell Friday, December 16, 2011 4:14 PM
    Friday, December 16, 2011 4:14 PM