How do I use a variable name for a workbook in vba

[Guest]

Here is what I am trying to do. We download information from our AS400 all
the time in particular a "contract file" I need to be able to create a macro
that formats this contract file and performs several calculations on it. I
am trying to do this via a macro button. I am having a problem when I call
the file in my code.

Here is where I start having problems:


Call GetContractFileName

Workbooks.Open
FileName:="G:\USER\Contracts\Data_Files\All_2004_Catalog.xls"
Sheets("Sheet1").Select
Sheets("Sheet1").Copy After:=Windows("80266-00-0.xls").Sheets(1) 'here
is where I need to use the variable "ContractFileName" I just don't know how
to do it.
Windows("80266-00-0.xls").Activate 'here also
Sheets("80266-00-0.xls").Select 'and here too

_______________________________________
Public Sub GetContractFileName()
ContractFile = InputBox("Enter the Contract Number: ", "Contract File")
ContractNo=ContractFile
ContractFile = ContractFile & ".xls"
End Sub
_______________________________


Can someone help? Pleeease!

 

[Bob Phillips]

I think this might help

Call GetContractFileName

Workbooks.Open
FileName:="G:\USER\Contracts\Data_Files\All_2004_Catalog.xls"
sFile = GetContractFileName
Sheets("Sheet1").Copy After:=Windows(sFile).Sheets(1)
Windows(sFile).Activate
at the end

Sheets(sFile).Select


_______________________________________
Public Function GetContractFileName()
ContractFile = InputBox("Enter the Contract Number: ", "Contract File")
ContractNo=ContractFile
ContractFileName = ContractFile & ".xls"
End Function
_______________________________



--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[Guest]

I was close!

You are correct I don't think I need the ".xls" in the sheet name.

Thank you!

 

[Guest]

Hello Emma

You need to declare the wariable before you can use it in between subs

click down the dropdown menu from right up corner of the module window. find
declarations and click it.

Write there:

dim ContractFile as string

after you press enter at the end of this line it should draw a horisontal
line under the line. This means that all variable declarations above that
line can be used between subs.
Another way(not so good programming) would be to write it like this:

ContractFile = InputBox("Enter the Contract Number: ", "Contract File")
ContractNo=ContractFile
ContractFile = ContractFile & ".xls"

Workbooks.Open
FileName:="G:\USER\Contracts\Data_Files\All_2004_Catalog.xls"
Sheets("Sheet1").Select
Sheets("Sheet1").Copy After:=Windows("80266-00-0.xls").Sheets(1) 'here
is where I need to use the variable "ContractFileName" I just don't know how
to do it.
Windows("80266-00-0.xls").Activate 'here also
Sheets("80266-00-0.xls").Select 'and here too

this doesn't need public declaration because all variables are in the one
sub...

Hope This helps...

 

[Guest]

I am getting an error here:

Sheets("Sheet1").Copy After:=Windows(sFile).Sheets(1)

which says that

"Oject doesn't support this property or method"

Here is the code that I used:

Call GetContractFileName

Workbooks.Open
FileName:="G:\USER\Contracts\Data_Files\All_2004_Catalog.xls"
sFile = contractfilename
cntNo = ContractNo
Sheets("Sheet1").Copy After:=Windows(sFile).Sheets(1)
Windows(sFile).Activate
Sheets(cntNo).Select
_____________________________________
Public Function GetContractFileName()
ContractNo = InputBox("Enter the Contract Number: ", "Contract File")
contractfilename = ContractNo & ".xls"

End Function
__________________________________________

 

[Guest]

There is probably no "Sheet1" in the workbook. You will also have a problem
if the spreadsheet you want to open does not exist. You can modify your code
as follows:

Sheets(1).Copy After:=Windows(sFile).Sheets(1)

or use this code... which will let you know if the file does not exist...

Private Const m_cPath As String = "C:\"

Private Function GetFile() As String
GetFile = InputBox("Enter the Contract Number: ", "Contract File") &
".xls"

End Function

Private Sub Test()
Dim wbkCopyTo As Workbook
Dim shtToCopy As Worksheet

On Error Resume Next
Set wbkCopyTo = Workbooks.Open(m_cPath & GetFile)
On Error GoTo 0
If wbkCopyTo Is Nothing Then
MsgBox "Workbook not found.", vbCritical, "File Error"
Else
Set shtToCopy = ThisWorkbook.Sheets(1)
shtToCopy.Copy wbkCopyTo.Sheets(1)
wbkCopyTo.Activate
End If
End Sub

 

[Bob Phillips]

Emma,

It should be

Sheets("Sheet1").Copy After:=Workbooks(sFile).Sheets(1)

--

HTH

RP
(remove nothere from the email address if mailing direct)