Results 1 to 7 of 7
  1. #1
    3 Star Lounger
    Join Date
    Oct 2001
    Location
    Newport, Gwent, Wales
    Posts
    257
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Entering Numbers into Excel from Access (Win XP Office 2003)

    Dear All

    I'm trying to persuade Access to build me an audit sheet for a kanban system.

    Currenlty I've got a query provides the data I require and some code that copies the query into Excel, what I'd like to be able to do then is populate the cells to the right of the exported data with sequential numbers from 1 to whatever the maximum value is.

    I've got the maximum value in the query, but trying to work from one column to the next, as well as moving down the rows for each part number.

    What I'm trying to do is probably wrong, currently my code dumps the query into the spreadsheet, I'm then trying to iterate down the spreadsheet using a cell on each row as my maximum value to add sequential numbers up to.

    Any help greatly appreciated

    Thanks in advance.

    Ian

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Entering Numbers into Excel from Access (Win XP Office 2003)

    Forgive my ignorance, but I have no idea what a kanban is, or what exactly you're trying to accomplish? Could you elaborate?

  3. #3
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts

    Re: Entering Numbers into Excel from Access (Win XP Office 2003)

    Like you I had no idea what a kanban is but I found this .
    Regards
    John



  4. #4
    3 Star Lounger
    Join Date
    Oct 2001
    Location
    Newport, Gwent, Wales
    Posts
    257
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Entering Numbers into Excel from Access (Win XP Office 2003)

    OK, Sorry, been beaten with this for so long I assume everyone knows what kanban is...

    The attachment is what I'm trying to produce, the columns under the green header are the ones I get from the query, the columns under the red header are the ones I'm currently creating manually and have tried, without success, to create by automation.

    Column G is teh maximum number to fill to.

    Hope that makes what I'm trying to do clearer....

    Ian
    Attached Files Attached Files

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Entering Numbers into Excel from Access (Win XP Office 2003)

    Can you use this as starting point?

    Sub Kanban()
    Dim xlApp As Excel.Application
    Dim xlWbk As Excel.Workbook
    Dim xlWsh As Excel.Worksheet
    Dim r As Long
    Dim m As Long
    Dim i As Integer
    Set xlApp = ...
    Set xlWbk = ...
    Set xlWsh = ...
    m = xlWsh.Cells(xlWsh.Rows.Count, 7).End(xlUp).Row
    For r = 2 To m
    For i = 1 To xlWsh.Cells(r, 7)
    xlWsh.Cells(r, i + 7) = i
    Next i
    Next r
    xlWbk.Close SaveChanges:=True
    ...
    End Sub

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Entering Numbers into Excel from Access (Win XP Office 2003)

    Yes, I found that too, but it didn't help much with the present question - for me, at least.

  7. #7
    3 Star Lounger
    Join Date
    Oct 2001
    Location
    Newport, Gwent, Wales
    Posts
    257
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Entering Numbers into Excel from Access (Win XP Office 2003)

    Hans

    As ever I owe you my sanity. I guess I'm just not familiar enough with VBA to see the simplicity of it all.

    In 10 minutes you've shown me how to do something I've been failing to do, on and off, for the last week.

    If you ever need to know about kanban maybe I can repay the debt

    Ian

Posting Permissions

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