Solved: Excel Template and Excel Database

[canucklady]

I am trying to figure out at way to transfer data that is in excel form to a database each time it is filled out.

This form also needs to be printed and sent to accounting for approval.

Form template basically is:

Date:
Name:
Product Detailscould be as many as 10 products)
Supplier:
Approval:

The above form needs to be printed.

Then I would like to take the above info and enter it in database each time the form is filled out.

Right now they are printing out the form and then re-entering in database. Is there an easier way to do this?

[mdmackillop]

Why not link to your excel table from Access and run a query to append it to your main access table.

[canucklady]

I am not familiar with Access and my supervisor wants everything in Excel.

[lucas]

What you want is complicated and basically a complete application.....Johnske once posted an invoicing workbook which used a template which you fill out...it then prints it, copies the worksheet, and adds the pertinant info to a register sheet...then it clears the contents of the template.....see how much is involved...

If your still interested and would like to see the invoice book so you can try to adjust it to your needs I will post it for you and you can go step by step with questions here to get it worked out but it's involved...be forwarned.

[canucklady]

I tried using Excel addi in Template Wizard, which would work, except for I have many products to add instead of one.

Here is the code that works for me, but I want to save the database to a different workbook instead of same workbook.

[VBA]
Sub Button2_Click()
Dim NewRow As Integer
NewRow = Worksheets("LF Returns").Range("e65536").End(xlUp).Row + 1
Worksheets("LF Returns").Cells(NewRow, 1).Value = Worksheets("Return Merchandise form").Range("j3").Value
Worksheets("LF Returns").Cells(NewRow, 2).Value = Worksheets("Return Merchandise form").Range("e4").Value
Worksheets("LF Returns").Cells(NewRow, 3).Value = Worksheets("Return Merchandise form").Range("c8").Value
Worksheets("LF Returns").Cells(NewRow, 4).Value = Worksheets("Return Merchandise form").Range("c9").Value
Worksheets("LF Returns").Cells(NewRow, 5).Value = Worksheets("Return Merchandise form").Range("a13").Value
Worksheets("LF Returns").Cells(NewRow, 6).Value = Worksheets("Return Merchandise form").Range("c13").Value
Worksheets("LF Returns").Cells(NewRow, 7).Value = Worksheets("Return Merchandise form").Range("e13").Value
Worksheets("LF Returns").Cells(NewRow, 8).Value = Worksheets("Return Merchandise form").Range("g13").Value
Worksheets("LF Returns").Cells(NewRow, 9).Value = Worksheets("Return Merchandise form").Range("i13").Value
Worksheets("LF Returns").Cells(NewRow, 10).Value = Worksheets("Return Merchandise form").Range("d46").Value
NewRow = Worksheets("LF Returns").Range("e65536").End(xlUp).Row + 1
Worksheets("LF Returns").Cells(NewRow, 1).Value = Worksheets("Return Merchandise form").Range("j3").Value
Worksheets("LF Returns").Cells(NewRow, 2).Value = Worksheets("Return Merchandise form").Range("e4").Value
Worksheets("LF Returns").Cells(NewRow, 3).Value = Worksheets("Return Merchandise form").Range("c8").Value
Worksheets("LF Returns").Cells(NewRow, 4).Value = Worksheets("Return Merchandise form").Range("c9").Value
Worksheets("LF Returns").Cells(NewRow, 5).Value = Worksheets("Return Merchandise form").Range("a15").Value
Worksheets("LF Returns").Cells(NewRow, 6).Value = Worksheets("Return Merchandise form").Range("c15").Value
Worksheets("LF Returns").Cells(NewRow, 7).Value = Worksheets("Return Merchandise form").Range("e15").Value
Worksheets("LF Returns").Cells(NewRow, 8).Value = Worksheets("Return Merchandise form").Range("g15").Value
Worksheets("LF Returns").Cells(NewRow, 9).Value = Worksheets("Return Merchandise form").Range("i15").Value
Worksheets("LF Returns").Cells(NewRow, 10).Value = Worksheets("Return Merchandise form").Range("d46").Value
NewRow = Worksheets("LF Returns").Range("e65536").End(xlUp).Row + 1
Worksheets("LF Returns").Cells(NewRow, 1).Value = Worksheets("Return Merchandise form").Range("j3").Value
Worksheets("LF Returns").Cells(NewRow, 2).Value = Worksheets("Return Merchandise form").Range("e4").Value
Worksheets("LF Returns").Cells(NewRow, 3).Value = Worksheets("Return Merchandise form").Range("c8").Value
Worksheets("LF Returns").Cells(NewRow, 4).Value = Worksheets("Return Merchandise form").Range("c9").Value
Worksheets("LF Returns").Cells(NewRow, 5).Value = Worksheets("Return Merchandise form").Range("a17").Value
Worksheets("LF Returns").Cells(NewRow, 6).Value = Worksheets("Return Merchandise form").Range("c17").Value
Worksheets("LF Returns").Cells(NewRow, 7).Value = Worksheets("Return Merchandise form").Range("e17").Value
Worksheets("LF Returns").Cells(NewRow, 8).Value = Worksheets("Return Merchandise form").Range("g17").Value
Worksheets("LF Returns").Cells(NewRow, 9).Value = Worksheets("Return Merchandise form").Range("i17").Value
Worksheets("LF Returns").Cells(NewRow, 10).Value = Worksheets("Return Merchandise form").Range("d46").Value
NewRow = Worksheets("LF Returns").Range("e65536").End(xlUp).Row + 1
Worksheets("LF Returns").Cells(NewRow, 1).Value = Worksheets("Return Merchandise form").Range("j3").Value
Worksheets("LF Returns").Cells(NewRow, 2).Value = Worksheets("Return Merchandise form").Range("e4").Value
Worksheets("LF Returns").Cells(NewRow, 3).Value = Worksheets("Return Merchandise form").Range("c8").Value
Worksheets("LF Returns").Cells(NewRow, 4).Value = Worksheets("Return Merchandise form").Range("c9").Value
Worksheets("LF Returns").Cells(NewRow, 5).Value = Worksheets("Return Merchandise form").Range("a19").Value
Worksheets("LF Returns").Cells(NewRow, 6).Value = Worksheets("Return Merchandise form").Range("c19").Value
Worksheets("LF Returns").Cells(NewRow, 7).Value = Worksheets("Return Merchandise form").Range("e19").Value
Worksheets("LF Returns").Cells(NewRow, 8).Value = Worksheets("Return Merchandise form").Range("g19").Value
Worksheets("LF Returns").Cells(NewRow, 9).Value = Worksheets("Return Merchandise form").Range("i19").Value
Worksheets("LF Returns").Cells(NewRow, 10).Value = Worksheets("Return Merchandise form").Range("d46").Value
NewRow = Worksheets("LF Returns").Range("e65536").End(xlUp).Row + 1
End Sub
[/VBA]

Edited 10-Apr-07 by geekgirlau. Reason: insert vba tags

[lucas]

Could you please post the workbook that your starting with?

[canucklady]

hope this works.

[lucas]

Here is the code that works for me, but I want to save the database to a different workbook instead of same workbook.

why?

[lucas]

You should take a look at this....it address many of the problems your going to encounter such as incrementing the form number. adding to the next line of the database, clearing contents of the form for the next entry, etc.

Click here

I also removed all of the modules except 3 that had macro's in your workbook and all of the forms...none were doing anything and reduced the size of your file to 90kb.....

[mdmackillop]

This task is made extremely difficult because of the merged cells in the form. It would be better to redesign the form to remove these before attempting a solution.

[canucklady]

Thanks Lucas for your help, that was exactly what I was looking for, saves me from entering data twice.

[geekgirlau]

Quick tip for next time - if you are posting code, select the text and click on the VBA button. This will format your code as you see above, making it easier to read.