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

Topic

New Reply

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

Reply | Quote

Replies

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

Reply | Quote

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

Reply | Quote

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

Reply | Quote

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

Reply | Quote

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

Reply | Quote

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

Reply | Quote