VERY Basic Define Variables Help Needed
BeeJay,
You don't need to store the workbook name anywhere. Simply use a workbook object reference:
Dim myBook As Workbook
Dim myQCNum As Workbook
Set myBook = ActiveWorkbook 'Will be CONTRACT if CONTRACT is the actve workbook
Set myQCNum = Workbooks.Open("C:\Folder\QCNUM.xls")
Then you can switch back and forth (though you don't really need to - you can do everything without
activating)
myBook.Activate 'This is CONTRACT again
myQCNum.Activate 'This is QCNum again
Not sure whether you are putting data into QCNUM or taking data out of it.
HTH,
Bernie
MS Excel MVP
"BEEJAY" wrote in message
...
I'll try to explain.
Salesman has a number of WorkBooks open.
He's currently working on a CONTRACT.
When he's done, he will use a macro to open file QCNUM.xls,
which is where the sequencial quote number is stored and therefore will be
extracted from. The difficulty, I think, is in how to direct the copied
material back to CONTRACT, and not to one of the other open WB's.
I had hoped that by putting the file name in cell G42 of CONTRACT,
copying it to QCNUM, cell F8, that, after copying the data from QCNUM,
the copied material could be directed back to the correct WB by referencing
cell F8 in QCNUM.
Note that the actual name for CONTRACT will change everytime that WB gets
used. That's why the first part of the macro has to initiate the procedure to
put
the actual WB name into cell G42.
All my previous attempts at explaining the complete procedure that I'm trying
to acomplish has done little more than confuse everyone, including myself.
Actually, that's not quite true. I have learned a lot of interesting things
from everyones attempts to help me.
I hope this clarifies what I'm attempting. ( at least the first steps )
"Vacation's Over" wrote:
What Bernie said plus:
FYI
Syntax for VBA is very different from Excel formulas
=G42 in excel becomes
Activeworkbook.Range("G42") or
Activeworkbook.Cells(42,7)
and so on...
"Bernie Deitrick" wrote:
Bee,
Your first line of error-producing code looks like a worksheet function used
to return the current file name.
You should use VBA to get that:
Activeworkbook.Name
Activeworkbook.FullName
Activeworkbook.Path
Unless the file name is stored as a string in cell G42, in which case you
can use VBA string manipulation functions to extract it.
Post back with what it is that you want to do rather than with just the code
that you tried to use.
HTH,
Bernie
MS Excel MVP
"BEEJAY" wrote in message
...
The following is step one in what is turning out to be a complex
(at least for me) procedure.
I hope someone has the patience to walk me thru these basics. I feel like
I'm missing a few "keys", in understanding the terminolgy, etc., as it
relates
to defining variables.
Option Explicit
Sub SeqNum()
' Quote Number Macro
' 9-27-05: To assign quote number to Contract forms
' Keyboard Shortcut: Ctrl+Shift+Q
' Salesman has saved the contract with a new name (unknown name)
' The following (from McGimpsey & Associates) is to insert new File name
' of new Contract to Cell G42, of the contract
' (The file name will be different each time this procedure is used).
Dim RngToCopy As Range
Dim DestCell As Range
'THE FOLLOWING is what is causing the grief, so far.
' The 1st G42, "Compile Error, Variable not Defined
Total = Mid(Left(CELL("filename", G42), Find("]", _
CELL("filename", G42)) - 1), Find("[", CELL("filename", G42)) + 1, 255)
' File QCNUM.xls to be opened
' File name in G42 to be copied to F8 in QCNUM.xls
' Will try to use the file name to direct the further required copying
and pasting
' instructions into the correct file. (to be added later)
' To Copy FROM (Newly Named Quote, Cell G42)
With Workbooks(Cell G42).Worksheets("Contract")
Set RngToCopy = .Range("G42")
' To Paste INTO (QCNUM.xls must be opened first)
With Workbooks(c:\Excel Add_Ins\QCNUM.xls).Worksheets("Sheet1").Open
Set DestCell = .Range("F8")
End With
'Save and Close QCNUM.xls
With Workbooks("QCNUM.xls")
.Save
.Close SaveChanges:=True
End With
End Sub
'There is a lot more to come, but I don't feel the rest will be near as
tough
as this is (to me)
Thanks in advance.
|