PDA

View Full Version : Assigning cell values



Eddie
04-09-2008, 09:39 AM
I need to print out a selection of cells from different test files on a regular basis. Since the files are used company wide, and there is a seperate file for each test, I cannont save a macro to the workbook. I wrote a module that selects and prints what I want and saved it to a .xla file.

Sub PrintSelection()
'creates strings to be set
Dim SerialDate As String
Dim SerialNum As String
Dim FullFileName As String

'sets the FullFileName variable value to the name of the file
FullFileName = ThisWorkbook.Name
'takes the first four numbers from the FullFileName string and assigns them to the SerialDate string
SerialDate = Mid(FullFileName, 1, 4)
'takes the next four numbers from the FullFileName string and assigns them to the SerialNum string
SerialNum = Mid(FullFileName, 5, 4)

'Selects cell H8 and then enters the serial number based off of the file name in the format "SN: ####-####"
Range("H8").Select
ActiveCell.FormulaR1C1 = "SN:" & SerialDate & "-" & SerialNum

'Selects the cells that are to be printed
Range("A1:L107").Select

'Prints selection to PDF printer
Application.ActivePrinter = "PrimoPDF on Ne00:"
ActiveWindow.Selection.PrintOut Copies:=1, ActivePrinter:= _
"PrimoPDF on Ne00:", Collate:=True

End Sub

My problem is when I am assigning the string "FullFileName" is pulls the .xla file name, not the file name from the workbook that I want. What is the proper code to set the string to the open workbook? Thanks a lot for your help.

mdmackillop
04-09-2008, 10:03 AM
Hi Eddie,
Welcome to VBAX.
Try

FullFileName = ActiveWorkbook.Name

If you have a few cells only and yoiu know their address, you can get data from a closed workbook.
http://vbaexpress.com/kb/getarticle.php?kb_id=454

Eddie
04-09-2008, 10:20 AM
That's exactly what i needed. Thanks a lot!!