How To Import Rows To Excel Template And Print One By One Automatically?

newyorknix

New Member
Joined
Aug 28, 2011
Messages
16
Hi,
I have 100 rows of data which contains sales order details.
For example each row contains order id, date, buyer address, item, quantity, price in cells.
I have a sales order excel template. Now I need to import these details to the template and print accordingly.
So finally 100 rows will be printed to 100 invoices.

Is it possible? Can I somehow import this data to an excel template and print out automatically?
I did searched with some keywords but don't know where to start. Solution would be macros I guess.
This is a repetitive task for me and it is very time consuming to do manually.
Any help would be appreciated.
Thanks in advance!
 

Excel Facts

Excel Can Read to You
Customize Quick Access Toolbar. From All Commands, add Speak Cells or Speak Cells on Enter to QAT. Select cells. Press Speak Cells.
I have assumed your input data looks something like:

Excel 2007
ABCDEFG
1Order idDateBuyerAddressItemQtyPrice
2113/01/2013aAddressItem A110
3213/01/2013bAddressItem B220
4313/01/2013cAddressItem C330
Sheet1


For testing this is the Invoice set up I have used:

Excel 2007
AB
1Order id3
2Date########
3Buyerc
4Address
5Item
6Qty
7Price
Invoice


You will need to edit the code to match your layout.

I have assumed the Output sheet is named "Invoice"
And the input data is in the first worksheet of the source file.

The code prompts for the file to open.
Loops through the source file and enters the information onto the Invoice.
And prints out - commented out for testing
Then clears the Invoice for the next record.

Create a dummy file containing a couple of records for testing.

To use, On your Invoice workbook
Press Alt+F11 - to open the VBA Editor
Double click the ThisWorkbook module in the Project Window on the left hand side.
Copy and paste the code.
Press F8 to step through the code to get a better idea of what it does.
Edit the Input/Output ranges to meet your sheets layout.
Code:
[color=darkblue]Option[/color] [color=darkblue]Explicit[/color]


[color=darkblue]Sub[/color] ImportAndPrintInvoice()
   [color=darkblue]Dim[/color] FileName [color=darkblue]As[/color] [color=darkblue]String[/color]
   [color=darkblue]Dim[/color] wbSource [color=darkblue]As[/color] Workbook
   [color=darkblue]Dim[/color] rngSource [color=darkblue]As[/color] Range
   [color=darkblue]Dim[/color] wsInvoice [color=darkblue]As[/color] Worksheet
      
   [color=green]'set up the output worksheet[/color]
   [color=darkblue]Set[/color] wsInvoice = Sheets("Invoice")
   
   [color=green]'select the source workbook to process[/color]
   FileName = Application.GetOpenFilename(FileFilter:="Excel Files (*.xls*), *.xls", _
                                          Title:="Please select a file")
   [color=darkblue]Set[/color] wbSource = Workbooks.Open(FileName)
   
   [color=green]'assumed data is on first worksheet[/color]
   [color=green]'assumed header row[/color]
   [color=darkblue]Set[/color] rngSource = wbSource.Worksheets(1).Range("A2")
   
   [color=green]'loop through the input data[/color]
   [color=darkblue]Do[/color] [color=darkblue]Until[/color] rngSource = ""
      [color=green]'=====================================[/color]
      [color=green]'EDIT OUTPUT RANGES TO MEET YOUR NEEDS[/color]
      [color=green]'=====================================[/color]
      [color=darkblue]With[/color] wsInvoice
         .Range("B1").Value = rngSource.Value               [color=green]'column A[/color]
         .Range("B2").Value = rngSource.Offset(, 1).Value   'column B
         .Range("B3").Value = rngSource.Offset(, 2).Value   [color=green]'column C[/color]
         'etc
         
      [color=darkblue]End[/color] [color=darkblue]With[/color]
   
      [color=green]'===================[/color]
      [color=green]'PRINT OUT[/color]
      'wsInvoice.PrintOut
      [color=green]'===================[/color]
      
      [color=green]'======================================[/color]
      [color=green]'YOU MAY WANT TO CLEAR THE INVOICE HERE[/color]
      [color=green]'======================================[/color]
      [color=darkblue]With[/color] wsInvoice
         .Range("B1").Value = ""    [color=green]'column A[/color]
         .Range("B2").Value = ""    'column B
         .Range("B3").Value = ""    [color=green]'column C[/color]
         'etc
      [color=darkblue]End[/color] [color=darkblue]With[/color]
      
      [color=green]'get next record[/color]
      [color=darkblue]Set[/color] rngSource = rngSource.Offset(1, 0)
   [color=darkblue]Loop[/color]


[color=green]'tidy up[/color]
   [color=darkblue]Set[/color] wsInvoice = [color=darkblue]Nothing[/color]
   [color=darkblue]Set[/color] rngSource = [color=darkblue]Nothing[/color]
   [color=darkblue]Set[/color] wbSource = [color=darkblue]Nothing[/color]
   wbSource.Close SaveChanges:=[color=darkblue]False[/color]
[color=darkblue]End[/color] [color=darkblue]Sub[/color]
 
Upvote 0
Hi I have created a sales invoice template named Invoice as suggested.
I have put the source data in a separate source file.
After copying the code and editing it to suit my requirements when i press F8 I get the following error message

Run-time error '91':
Object variable or With block variable not set

Please help me or suggest where I am going wrong. This message is appearing after completing the loops.
 
Upvote 0
Hi, it has been a while since I wrote that code.


Swap these lines around to close the workbook before clearing the variable:


Rich (BB code):
'tidy up
   Set wsInvoice = Nothing
   Set rngSource = Nothing
   
   wbSource.Close SaveChanges:=False
   Set wbSource = Nothing
End Sub
 
Upvote 0
Hi, it has been a while since I wrote that code.

Swap these lines around to close the workbook before clearing the variable:


Rich (BB code):
'tidy up
   Set wsInvoice = Nothing
   Set rngSource = Nothing
   
   wbSource.Close SaveChanges:=False
   Set wbSource = Nothing
End Sub


Hi there... have donr that but the error is still there...
 
Upvote 0
Hi there... have donr that but the error is still there...

I actualy changed a bit the code... source data is on the same workbook - Sheet 27 ... see the code bellow:

Option Explicit




Sub ImportAndPrintNotaTransport()
Dim FileName As String
Dim wbSource As Workbook
Dim rngSource As Range
Dim wsNotaTransport As Worksheet

'set up the output worksheet
Set wsNotaTransport = Sheets("NotaTransport")

'select the source workbook to process
Set rngSource = Sheets("Comenzi").Range("B2")

'loop through the input data
Do Until rngSource = ""
'=====================================
'EDIT OUTPUT RANGES TO MEET YOUR NEEDS
'=====================================
With wsNotaTransport
.Range("O1").Value = rngSource.Offset(, 4).Value 'column D
.Range("C3").Value = rngSource.Offset(, 13).Value 'column M
.Range("C16").Value = rngSource.Offset(, 17).Value 'column P
.Range("C19").Value = rngSource.Offset(, 3).Value 'column C
.Range("G19").Value = rngSource.Offset(, 18).Value 'column R
.Range("F22").Value = rngSource.Offset(, 21).Value 'column U
.Range("G22").Value = rngSource.Offset(, 10).Value 'column J
.Range("A25").Value = rngSource.Offset(, 19).Value 'column S
.Range("E31").Value = rngSource.Offset(, 23).Value 'column W
.Range("A33").Value = rngSource.Offset(, 20).Value 'column T
.Range("A35").Value = rngSource.Offset(, 25).Value 'column Y
.Range("N14").Value = rngSource.Offset(, 22).Value 'column V
.Range("J30").Value = rngSource.Offset(, 24).Value 'column X
.Range("N30").Value = rngSource.Offset(, 12).Value 'column L
.Range("I33").Value = rngSource.Offset(, 6).Value 'column F
.Range("L33").Value = rngSource.Offset(, 6).Value 'column F
'etc

End With

'===================
'PRINT OUT
'wsNotaTransport.PrintOut Copies:=2, Collate:=False
'===================

'======================================
'YOU MAY WANT TO CLEAR THE NOTA TRANSPORT HERE
'======================================
With wsNotaTransport
.Range("O1").Value = "" 'column D
.Range("C3").Value = "" 'column M
.Range("C16").Value = "" 'column P
.Range("C19").Value = "" 'column C
.Range("G19").Value = "" 'column R
.Range("F22").Value = "" 'column U
.Range("G22").Value = "" 'column J
.Range("A25").Value = "" 'column S
.Range("CE31").Value = "" 'column W
.Range("A33").Value = "" 'column T
.Range("A35").Value = "" 'column Y
.Range("N14").Value = "" 'column V
.Range("J30").Value = "" 'column X
.Range("N30").Value = "" 'column L
.Range("I33").Value = "" 'column F
.Range("L33").Value = "" 'column F
'etc
End With

'get next record
Set rngSource = rngSource.Offset(1, 0)
Loop




'tidy up
Set wsNotaTransport = Nothing
Set rngSource = Nothing
wbSource.Close SaveChanges:=False
Set wbSource = Nothing
End Sub
 
Upvote 0

Forum statistics

Threads
1,213,543
Messages
6,114,243
Members
448,555
Latest member
RobertJones1986

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top