Excel help!

[ntl customer]

I wad wondering if anybody could help me with my IT work as I have no bloody idea how to do this, and it is driving me mad!

Basically I have a system where I have a form inside a spreadsheet and when I click a button to which I have assigned a macro to and it is supposed to copy all the fields to a 'database' sheet. I do this by recording a macro and copying and pasting the cells individually into the rows of the database.

That does that fine but I can't seem to get it to go onto the next line when it has finished pasting all the fields in. If you press the button with the macro assigned to it it just overwrites the fields from the previous transfer.

How do you get it to go onto the next line? I need an 'idiot's guide' please, so don't use any jargon and take it step by step - I am no expert at Excel and I have work due at the end of this week - not good!

Thanks

[MovedGoalPosts]

I'm no great expert either, but to give us all a clue as to where to start looking:

1) Which version of Excel are you using.

2) The database sheet you are using, is it in the same Excel file, as a separate page, another excel file, an Acess database or something else.

It looks to me as though somehoe you need to tell the excel macor that it's reached the end of a line, i.e all fields are done for the record, and must start to write the next data record line.

[ntl customer]

I am using Excel 2000 but the college uses XP. Would this cause problems when transferring between systems?

I'm very tempted just to download Office XP off Kazaa and install that, purely for compatability reasons.

The database sheet is a worksheet in the same file as in the screenshot below: -



Also I've just noticed a bug - the first field (student's name) does not copy properly. It either does not do it or copies crap into the cell.

[MovedGoalPosts]

Shouldn't be a problem to use 2 different Excel versions, provided you tell the XP version to save in 2000 format (if you dont save that way 2000 will probably not be able to open it).

[Stuart W]

Originally Posted by ntl customer

I wad wondering if anybody could help me with my IT work as I have no bloody idea how to do this, and it is driving me mad!

Basically I have a system where I have a form inside a spreadsheet and when I click a button to which I have assigned a macro to and it is supposed to copy all the fields to a 'database' sheet. I do this by recording a macro and copying and pasting the cells individually into the rows of the database.

That does that fine but I can't seem to get it to go onto the next line when it has finished pasting all the fields in. If you press the button with the macro assigned to it it just overwrites the fields from the previous transfer.

How do you get it to go onto the next line? I need an 'idiot's guide' please, so don't use any jargon and take it step by step - I am no expert at Excel and I have work due at the end of this week - not good!

Thanks

Well, hit ALT+F11 to see your VBA (Visual Basic for Applications) window and look at the module you have recorded.
You will see that you recorded the cell locations you pasted into, this is where you need a small loop.

You need to add a line which will check the contents of a pre-defined cell before pasting info.

EG, In cell C4 is the number 1.
You run your macro, it looks in C4 and sees "1", so paste starts on line 1
Once paste has finished, increase C4 by one.
Next time round, your macro see's the "2" in C4 so you start your paste on line 2.

It isn't actualy as easy as I've made it sound here, but it does point you in the right direction

[ntl customer]

Sub copyandpaste()
'
' copyandpaste Macro
' Macro recorded 14/03/2004 by (removed)'

'
    Selection.copy
    Sheets("Student_database").Select
    ActiveSheet.Paste
    Range("B6").Select
    Sheets("Student_form").Select
    Range("B3").Select
    Sheets("Student_form").Select
    Range("B5").Select
    Application.CutCopyMode = False
    Selection.copy
    Sheets("Student_database").Select
    ActiveSheet.Paste
    Range("C6").Select
    Sheets("Student_form").Select
    Range("B7").Select
    Application.CutCopyMode = False
    Selection.copy
    Sheets("Student_database").Select
    ActiveSheet.Paste
    Range("D6").Select
    Sheets("Student_form").Select
    Range("B9").Select
    Application.CutCopyMode = False
    Selection.copy
    Sheets("Student_database").Select
    ActiveSheet.Paste
    Range("E6").Select
    Sheets("Student_form").Select
    Range("B11").Select
    Application.CutCopyMode = False
    Selection.copy
    Sheets("Student_database").Select
    ActiveSheet.Paste
    Range("F6").Select
    Sheets("Student_form").Select
    Range("B13").Select
    Application.CutCopyMode = False
    Selection.copy
    Sheets("Student_database").Select
    ActiveSheet.Paste
    Range("G6").Select
    Sheets("Student_form").Select
    Range("B15").Select
    Application.CutCopyMode = False
    Selection.copy
    Sheets("Student_database").Select
    ActiveSheet.Paste
    Range("H6").Select
    Sheets("Student_form").Select
    Range("B17").Select
    Application.CutCopyMode = False
    Selection.copy
    Sheets("Student_database").Select
    ActiveSheet.Paste
    Range("I6").Select
    Sheets("Student_form").Select
    Range("B19").Select
    Application.CutCopyMode = False
    Selection.copy
    Sheets("Student_database").Select
    ActiveSheet.Paste
    Range("J6").Select
    Sheets("Student_form").Select
    Range("B21").Select
    Application.CutCopyMode = False
    Selection.copy
    Sheets("Student_database").Select
    ActiveSheet.Paste
    Range("K6").Select
    Sheets("Student_form").Select
    Range("B23").Select
    Application.CutCopyMode = False
    Selection.copy
    Sheets("Student_database").Select
    Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
        False, Transpose:=False
    Range("L6").Select
    Sheets("Student_form").Select
    Range("B25").Select
    Application.CutCopyMode = False
    Selection.copy
    Sheets("Student_database").Select
    Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
        False, Transpose:=False
End Sub

Well here is the macro code.

Where would I need to insert this loop, and what would I need to type?

[Stuart W]

ROTFLMAO!

It's not as easy as that mate!

You will need to construct an array, which will involve typing in the VB code manualy & realy knowing what you are doing.

I *could* just do it for you, but this would not help you at all as you won't know how to do it in future or how any of it works.

My best advice would be to either grab yourself a comprehensive guide to VBA or just do your project in MS Access which is a database, rather than MS Excel which is a spreadsheet.

[ntl customer]

Originally Posted by Stuart W

ROTFLMAO!

It's not as easy as that mate!

You will need to construct an array, which will involve typing in the VB code manualy & realy knowing what you are doing.

I *could* just do it for you, but this would not help you at all as you won't know how to do it in future or how any of it works.

My best advice would be to either grab yourself a comprehensive guide to VBA or just do your project in MS Access which is a database, rather than MS Excel which is a spreadsheet.

I didn't know if it was that complicated

Surely there must be a simpler way for it just to check that the line is empty. Or how about recording a macro so that it inserts a new blank line and writes on that.

I can't do it in Access - the college have stipulated that it must be done in Excel. And anyway I have no clue as to how to do it in Access.

<rant>

Although they have taught Excel, they haven't really *taught* us how to use it as such. How the hell do they expect us to know such things?

<end rant>

[MovedGoalPosts]

I have to agree that it does look like something that is best suited to Access. Once you have your various access data tables set up (instead of each different excel spread sheet) then you'll creat a query to append or update data to a the output table, form or report you require. And creating a form for the input data table is easier too.

[ntl customer]

It's all very well saying use Access, but there are two distinct problems with that ...

a) I can't. They have said that you can use Excel only.

and most importantly...

b) I don't know how to use Access!

Would it be easier for the form in Excel to transfer to a Access database, or would that just complicate matters?

[MovedGoalPosts]

Ok then, drifing away from topic if Access is no good to you, but I do think your college is making life too hard by getting you to use inappropriate software.

Access can import and export from / to Excel. As far as I'm aware this is fairly crude in that the results of an access query (sort of equivalent to the macro you are trying to run) would paste / export into one data table (access) / sheet (excel). I haven't actually tried running a query that uses eexcel to write an access table.
You've possibly done much of the leg work for access with your excel spreadsheet, in that each sheet of the spreadsheet becomes an access table. Queries then bind each table together, with forms and reports added on (not essential) to provide the end user with nice easy interfaces for the information input / extract they want, without knowing what goes on behind the scenes.

[ntl customer]

Also they expect us to use some of the 'advanced' features of Excel such as v lookups, forms, dropdown boxes etc as well as have a database thingy.

Originally Posted by ntl customer

Basically I have a system where I have a form inside a spreadsheet and when I click a button to which I have assigned a macro to and it is supposed to copy all the fields to a 'database' sheet. I do this by recording a macro and copying and pasting the cells individually into the rows of the database.

That does that fine but I can't seem to get it to go onto the next line when it has finished pasting all the fields in. If you press the button with the macro assigned to it it just overwrites the fields from the previous transfer.

How do you get it to go onto the next line? I need an 'idiot's guide' please, so don't use any jargon and take it step by step - I am no expert at Excel and I have work due at the end of this week - not good!

Thanks

It will always do this because when you record a macro it saves the absolute values of the cell numbers you are copying from and too - so everytime you run it, it will copy from the same cells, and paste to the same cells.

The only way I can see to do it, is as mentioned above - write some custom VBA code.

If you have never done this before (written code) then a week is asking rather a lot. Even if you have written code before, then learming a new macro language in 3/4 days is a challange.

[gazzae]

Instead of all those copys and pastes and changing between forms it would be easier to do it like this.

With Worksheets("Student_database")
    .Cells(2, 3).Value = Worksheets("Student_form").Cells(2, 2).Value
    .Cells(2, 4).Value = Worksheets("Student_form").Cells(2, 3).Value
End With

Then you could put a loop around it like such

strmyloopvar = Worksheets("Student_form").Cells(2, 2).Value
strstudentdbrow = 1
strstudentform = 1

do until strmyloopvar = ""
     strmyloopvar = Worksheets("Student_form").Cells(strstudentform, 2).Value
          With Worksheets("Student_database")
              .Cells(strstudentdbrow, 3).Value = Worksheets("Student_form").Cells(strstudentform, 2).Value
              .Cells(strstudentdbrow, 4).Value = Worksheets("Student_form").Cells(strstudentform, 3).Value
          End With

strstudentdbrow = strstudentdbrow + 1
strstudentform = strstudentform + 1

loop

On the Student_form sheet if you want to find a row with no data just do a loop and then loop down until the value of the cell is = "" e.g

strmyloopvar = Worksheets("Student_database").Cells(2, 2).Value

do until strmyloopvar = ""
     strmyloopvar = Worksheets("Student_database").Cells(strstudentdbrow, 2).Value

strstudentdbrow = strstudentdbrow + 1

loop

Then if you remove the strstudentdbrow = 1 from above the first loop then strstudentdbrow will be equal to the first row where the cell was blank and the first loop above will start putting the values into this cell.

Dunno if this code will work, as its early monday morning and I've just written it off the top of my head, but it should give you an idea of what you need to do.

[Stuart W]

FLASHBACK I used to teach ICT at my local College and this looks surprisingly like some Diploma work..

Are you aiming for a Level III Diploma in Computer Applications by any chance?

If so, you need to speak to one of the lecturers at your College as they should have taught you all about this.
If you are doing the diploma, I can understand why you have been asked to create a database in Excel, because the diploma coursework was writen years ago.

FYI, again, if you are doing the diploma, it might be worth pointing out that you have spoken to Stuart Willet formerly at Harlow College who has explained that we had to DROP the excel project because excel is now so advanced, we couldn't program in the 'faulty' spreadsheet without Excel automatically correcting it.