PDA

View Full Version : Solved: Excel Template and Excel Database



canucklady
04-07-2007, 06:45 AM
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 Details:(could 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
04-07-2007, 09:24 AM
Why not link to your excel table from Access and run a query to append it to your main access table.

canucklady
04-07-2007, 10:13 AM
I am not familiar with Access and my supervisor wants everything in Excel.

lucas
04-07-2007, 10:55 AM
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
04-07-2007, 11:07 AM
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.


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


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

lucas
04-07-2007, 11:11 AM
Could you please post the workbook that your starting with?

canucklady
04-07-2007, 11:29 AM
hope this works.

lucas
04-07-2007, 11:56 AM
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
04-07-2007, 12:04 PM
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 (http://www.vbaexpress.com/forum/showthread.php?t=11426)

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
04-07-2007, 12:48 PM
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
04-08-2007, 09:27 AM
Thanks Lucas for your help, that was exactly what I was looking for, saves me from entering data twice.

geekgirlau
04-09-2007, 07:10 PM
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.