Thanks Thanks:  0
Likes Likes:  0
Results 1 to 2 of 2

Thread: Excel data to MS Project throwing Error 1101

  1. #1
    Board Regular Michael M's Avatar
    Join Date
    Oct 2005
    Location
    South Western NSW
    Posts
    16,440
    Post Thanks / Like
    Mentioned
    6 Post(s)
    Tagged
    1 Thread(s)

    Default Excel data to MS Project throwing Error 1101

    Guys
    I have some code written by xld, to create a MSProject file and populate the tasks and dates ....
    I know I'm doing this the wrong way 'round, (it should be in Project and importing), but my peers want it in Excel.
    I tested it on a small sample of data and it worked fine....Now I'm in the real workbook it fails.
    I'm Getting a Runtime error 1101 on the line in red.
    The dates are formatted as dates, and I have tried dimming them as dates or strings.
    IS there anyone that can shed a little light on this....
    Any feedback would be appreciated
    Code:
    Sub MM1()
        Dim pjapp As Object
        Dim strValue As String, strStartDate As String, strEndDate As String, Strresource As String
        Dim newproj
        Set pjapp = CreateObject("MSProject.Application")
        If pjapp Is Nothing Then
            MsgBox "Project is not installed"
            Exit Sub
        End If
        pjapp.Visible = True
        Set newproj = pjapp.Projects.Add
        newproj.Title = "My New Project"
        Set ActiveProject = newproj
        For i = 6 To 45
             
            strValue = Worksheets("Report").Range("AF" & i)
            strStartDate = Worksheets("Report").Range("AU" & i)
            strEndDate = Worksheets("Report").Range("AV" & i)
            Strresource = Worksheets("Report").Range("AF" & i)
            newproj.Tasks.Add (strValue & " " & Strresource)
            newproj.Tasks(i - 1).Start = strStartDate
            newproj.Tasks(i - 1).Finish = strEndDate
            If Not ExistsInCollection(newproj.Resources, Strresource) Then _
            newproj.Resources.Add.Name = Strresource
            newproj.Tasks(i - 1).ResourceNames = Strresource
        Next i
    End Sub
    Regards
    Michael M
    ---------------------------------------
    The more I learn, the less I seem to know.....A Please and Thank You cost nothing !
    It's easier to debug if we can see the whole macro !
    Home 2007 & 2013

    - Posting guidelines, forum rules and terms of use

    - To download Mr Excel HTML Maker

    - Try searching for your answer first, see how

    - Read the FAQs

    - List of BB codes


    [CODE]Place Your Code Here[/CODE]

  2. #2
    Board Regular Michael M's Avatar
    Join Date
    Oct 2005
    Location
    South Western NSW
    Posts
    16,440
    Post Thanks / Like
    Mentioned
    6 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Excel data to MS Project throwing Error 1101

    Ok, Ok....I found the problem...
    The test sheet started on row 2, as per the original code
    which means the new tas will be i - 1...ergo Task 1
    Code:
    newproj.Tasks(i - 1).Start = strStartDate
    However, the Real workbook had hidden rows which I failed to notice and starts on row 4 !!
    Which means MSProject is trying to start the data at Task 3....which it can't do !

    If anyone has viewed this, I appreciate you having a look.....a large trap for young players !!!
    Regards
    Michael M
    ---------------------------------------
    The more I learn, the less I seem to know.....A Please and Thank You cost nothing !
    It's easier to debug if we can see the whole macro !
    Home 2007 & 2013

    - Posting guidelines, forum rules and terms of use

    - To download Mr Excel HTML Maker

    - Try searching for your answer first, see how

    - Read the FAQs

    - List of BB codes


    [CODE]Place Your Code Here[/CODE]

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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