Need to export file name to users sheet to the next row

B

Brad

I am tryinig to create a print queue. Tom O has helped me with the printing
from the queue, but now I need help putting the file name in the queue (xls
sheet). How do I code to get the fullname of my active book (abc.xls) to be
inserted into the next available row on the users sheet. I've tried piecing
together different code from searching the group, but I'm just causing more
problems.Here is my code so far:

Sub addtoqueue()
Dim usrid As String
Dim AWB As Workbook
Dim uswb As Workbook
Dim printdata_first_empty_row As Long
Dim source_range As Range
Set AWB = ActiveWorkbook
usrid = Environ("Username")
Application.ScreenUpdating = False
Workbooks.Open Filename:= _
"G:\Contract QuoteTemplates\2005 quote template\print
queue\" & usrid & ".xls"
printdata_first_empty_row = Worksheets(usrid).Range("A" &
Rows.Count).End(xlUp).Row + 1
With AWB
Destination = AWB.FullName
Application.ScreenUpdating = True
End With
End Sub

thanks for any help given!
 
T

Tom Ogilvy

How do you decide what workbooks to print?

Are you trying to print the first sheet in every *.xls file in a single
directory?
 
B

Brad

Basically the end user prints out contracts. It is currently setup to ask the
user if they want to print the contract, if they select yes, it prints, if
no, then it just hides the userform. So, what I want to do is when they
select no, they will be given a choice (from a user form) to add this
contract (based on filename) to their queue. Our contracts are created from a
template, so the contracts will always have the "add to queue" macro.
 
T

Tom Ogilvy

Sub addtoqueue()
Dim usrid As String
Dim AWB As Workbook
Dim uswb As Workbook
Dim Destination as Range
Set AWB = ActiveWorkbook
usrid = Environ("Username")
Application.ScreenUpdating = False
set uswb = Workbooks.Open( Filename:= _
"G:\Contract QuoteTemplates\2005 quote " & _
"template\print queue\" & usrid & ".xls")
set destination = uswb.Worksheets(usrid).Range("A" & _
Rows.Count).End(xlUp)(2)
Destination = AWB.FullName ' or AWB.Name
uswb.Close SaveChanges:=True
Application.ScreenUpdating = True
End With
End Sub
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top