| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
How do I use a variable name for a workbook in vba
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_C atalog.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! |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
How do I use a variable name for a workbook in vba
I think this might help
Call GetContractFileName Workbooks.Open FileName:="G:\USER\Contracts\Data_Files\All_2004_C atalog.xls" sFile = GetContractFileName Sheets("Sheet1").Copy After:=Windows(sFile).Sheets(1) Windows(sFile).Activate are you sure about this next line, a sheet wouldn't normally have .xls 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) "Emma" wrote in message ... 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_C atalog.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! |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
How do I use a variable name for a workbook in vba
I was close!
You are correct I don't think I need the ".xls" in the sheet name. Thank you! "Bob Phillips" wrote: I think this might help Call GetContractFileName Workbooks.Open FileName:="G:\USER\Contracts\Data_Files\All_2004_C atalog.xls" sFile = GetContractFileName Sheets("Sheet1").Copy After:=Windows(sFile).Sheets(1) Windows(sFile).Activate are you sure about this next line, a sheet wouldn't normally have .xls 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) "Emma" wrote in message ... 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_C atalog.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! |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
How do I use a variable name for a workbook in vba
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 the 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_C atalog.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... "Emma" wrote: 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_C atalog.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! |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
How do I use a variable name for a workbook in vba
I am getting an error he
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_C atalog.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 __________________________________________ "Bob Phillips" wrote: I think this might help Call GetContractFileName Workbooks.Open FileName:="G:\USER\Contracts\Data_Files\All_2004_C atalog.xls" sFile = GetContractFileName Sheets("Sheet1").Copy After:=Windows(sFile).Sheets(1) Windows(sFile).Activate are you sure about this next line, a sheet wouldn't normally have .xls 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) "Emma" wrote in message ... 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_C atalog.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! |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
How do I use a variable name for a workbook in vba
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 "Emma" wrote: I am getting an error he 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_C atalog.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 __________________________________________ "Bob Phillips" wrote: I think this might help Call GetContractFileName Workbooks.Open FileName:="G:\USER\Contracts\Data_Files\All_2004_C atalog.xls" sFile = GetContractFileName Sheets("Sheet1").Copy After:=Windows(sFile).Sheets(1) Windows(sFile).Activate are you sure about this next line, a sheet wouldn't normally have .xls 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) "Emma" wrote in message ... 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_C atalog.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! |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
How do I use a variable name for a workbook in vba
Emma,
It should be Sheets("Sheet1").Copy After:=Workbooks(sFile).Sheets(1) -- HTH RP (remove nothere from the email address if mailing direct) "Emma" wrote in message ... I am getting an error he 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_C atalog.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 __________________________________________ "Bob Phillips" wrote: I think this might help Call GetContractFileName Workbooks.Open FileName:="G:\USER\Contracts\Data_Files\All_2004_C atalog.xls" sFile = GetContractFileName Sheets("Sheet1").Copy After:=Windows(sFile).Sheets(1) Windows(sFile).Activate are you sure about this next line, a sheet wouldn't normally have ..xls 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) "Emma" wrote in message ... 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_C atalog.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! |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Can a workbook variable be set by clicking the workbook? | Excel Programming | |||
| Can a workbook variable be set by clicking the workbook? | Excel Programming | |||
| Can a workbook variable be set by clicking the workbook? | Excel Programming | |||
| Can a workbook variable be set by clicking the workbook? | Excel Programming | |||
| Can a workbook variable be set by clicking the workbook? | Excel Programming | |||
Linear Mode
