CreateObject Command, Is There A CloseObject?
Sep 10, 2009I have the following in my code ......
View 10 Replies
ADVERTISEMENT
2 Versions Installed But CreateObject Code Creating Old Version
Nov 27, 2009Set xlApp = CreateObject("Excel.Application")
xlApp.Visible = False
xlApp.Application.DisplayAlerts = False
xlApp.Workbooks.Open ("somepathWebquery.xlsm")
xlApp.Application.Run "'some macro"
The only problem is, the web query will only work properly if it is in Excel 2007 (2003 will parse the table in a way that is not acceptable). However, when I tried to run the script, the stubborn Excel 2003 will show up. I even tried to uninstall the Office Compatibility Pack and the stubborn Windows will only run Excel 2003 and ask me to download the Office Compatibility Pack instead of giving me Excel 2007.
I tried to open it up using Open With...and it did not work, I even tried to regserver and unregserver and that did not work as well. Somehow Windows thought the only Excel I have is Excel 2003.
Is there anyway I could change the VB Script so that it would force Excel 2007 to open?
CreateObject: Object Variable Or With Block Variable Not Set
Nov 29, 2006I have my below code which will create a looping "ftp" and "get" effect.
However i am having some problems right now. There's always an error that states "Object variable or With block variable not set".
Sub File_Transfer_testing()
Dim N As Integer
Dim Lotid As Variant
Dim Tester_array(1 To 15) As String
Tester_array(1) = "10.10.10.10"
Tester_array(2) = "20.20.20.20"
Lotid = InputBox("Input a LOT ID")
N = 1
Set fs = CreateObject("Scripting.FileSystemObject")
Set A = fs.CreateTextFile("C:script.txt", True)
For N = 1 To 2
A.writeline "open " & Tester_array(N)
A.writeline "xxx" 'username
A.writeline "xxxxxx" 'password
A.writeline "cd /zzz/reports"
A.writeline "prompt"
A.writeline "ascii"
A.writeline "mget " & Lotid & "*sorts" 'file to be uploaded
A.writeline "quit"
A.Close
Set fs = CreateObject("Scripting.FileSystemObject")
Set A = fs.CreateTextFile("C: upload.bat", True)
A.writeline "ftp -s:C:script.txt" 'the ftp site
A.Close
dRetVal = Shell("C:upload.bat", 0) 'upload the file
''''''''''end upload.bat file
Next
MsgBox ("Done")
End Sub
Press Command But And Execute The Code Assigned To Another Command Button
Apr 14, 2009Is it possible that when you press a command button, that the first thing it does is to execute the code assigned to another command button (IE in another sub).
View 9 Replies View RelatedShell - Adding Another Command To Forfiles Command?
Apr 19, 2014I am playing around with the Forfiles command (being called from Excel via Shell)..
I can't for the life of me get it apply a second command (such as getting the file size of each file)..
For example:
VB:
Private Sub CommandButton1_Click()
Dim Z
Z = Split(CreateObject("wscript.shell").exec("cmd /c forfiles /P C:UsersapoDesktopTextFiles /S /M *.* /d
[Code]....
The end result being the filename and the filesize shown..
Excel 2007 :: Using Search Command With IF Command
Jun 20, 2013Excel 2007
I have this simple formula: =IF(SEARCH("ABC",BQ239,1)>0,"Found", "Not Found")
Instead of saying Not Found when the value is not in the cell BQ239 text string I get #VALUE! returned
how do I get it to say "Not Found"? I searching for a substring that can be anywhere in the string.
What Command Is Executing Or What Was Last Command Used
Feb 17, 2010Is there any way to tell (from VBA) what GUI command is currently in progress or what the last command used was?
On a wish list perhaps: Application.LastCommand and/or Application.CurrentCommand
Let's say I'm in the "Sheet_Change" event and want to know what caused the change. Was the event triggered by "Delete", "Paste", "Keystrokes" ...
AutoCAD VBA has "Begin_Command (CommandName as String)" and "End_Command (CommandName as String)" events which I find very useful. Basically I'd like to emulate that to the greatest extent possible.
Application.Caller does not seem to work for the stated purpose. I've been searching for a workaround by means of reading the "Undo Stack". Numerous articles written by experts state that the undo stack is not accessible from VBA.
I've considered reading the text from the "Undo" button's caption but it just seems like such a hack ... not that this whole idea isn't a hack
Command Button Name / Sort By Command Button
Feb 3, 2010How can I return name of a command button on click? I want to create some sort buttons on a sheet I will use regularly to speed things up. I thought if I could call each sort button by the column letter the button sits in I can use this in a single sub for all buttons
e.g the button sitting in column A is called "A". when i click the button, it returns it's name to a variable which I can then use to sort column A. I know I could create a seperate routine for each button but I was just trying to think of something neater.
Run Command Button Click From Another Command Button
Aug 31, 2007I have a userform with a button on it named But1. Is it possible to store But1 into a variable then activate the But1_Click action using a variable?
Dim strButname As String
strButname ="But1"
Now, how would you activate the But1_Click method using strButname variable. With listboxes you can use the Control(strListboxName) methodology?
This doesn't work, but gives you an idea of what I am trying to do.
Controls(strButname)_click
VB Command
Jan 30, 2007I have 2 worksheets. On the first worksheet, i enter a line of different information.
What i want the spreadsheet to do is copy and paste that info onto the 2nd worksheet - wil this require VB atall?
If Or And Command
Apr 24, 2007I have a large amount of data, of which i need to perfrom a command to show either true or false result.
H Column has a range of number from 1 through 260
L Column has different text entrys 'pen' pencil' 'tape' etc..
The aim of the formula is to show a true result
if the number in the H Column is larger than 30
if the text is equal to either pen or pencil
OR Function / Command
Nov 8, 2009I am trying to get the OR command or something that may work in a similiar fashion to work on this particular formula.
=IF(ROWS($G$2:G2)<=$Z$32,SMALL(IF(DEALLIST[Sales1]=$E$9,ROW(DEALLIST[Deal'#])-ROW('DEALS 1.0'!$B$1)),ROWS($G$2:G2)),"")
In this formula I want to be able to see if DEALLIST[Sales1], OR EALLIST[Sales2] is equal to $E$9, if they are then I want the formula to continue. I have tried this but it returns every row in my table starting from row 1 through 1000.
=IF(ROWS($G$2:G2)<=$Z$32,SMALL(IF(OR(DEALLIST[Sales1]=$E$9,DEALLIST[Sales2]=$E$9),ROW(DEALLIST[Deal'#])-ROW('DEALS 1.0'!$B$1)),ROWS($G$2:G2)),"")
Command Button
Jul 29, 2008I have searched this topic but I came up with nothing regarding my exact situation.
I have 5 buttons on sheet 1. I want them to all direct me to another sheet in the same workbook. However, some buttons will contain more than one sheet.
Example: Button 1 will direct me to 4 different sheets. Is this possible?
Also, When I open the workbook, I see many sheets that I am working from but I do not want them to show up when I open the workbook. The only way to see these sheets is to click on the command buttons at the top of sheet 1.
New Command Icon
Oct 15, 2008I'm looking to have a doc where people time stamp their start and stop times. I have found that Control(apple) - Shift -semi colon works. The problem i'm having is that I want people to be able to use a command icon in their toolbars for this task as opposed to people have to use the keyboard. I can use a button with a macro but this is not possible as our excel corrupts when there are too many macro's
View 4 Replies View RelatedCommand Bar Removal
Jan 30, 2009I have just started playing around with events etc and i was trying to reomve the command bar using the code below in module 1.
View 4 Replies View RelatedIf Command In BeforePrint
Sep 22, 2009The following code is not working like I thought it would. I've got 4 named cells on a worksheet, and if any of them are blank, I don't want the user to be able to print it. What am I missing? Is it something with my Cancel = True statement?
View 2 Replies View RelatedIF Command: Depending On This Value
May 21, 2009I have a sheet in which I have a sum then depending on this value I want the next cell, F4, to have one of four outputs
If the value in F3 is;
Less than 1 F4 should show 9
between 1 and 2 F3 should show 16
Between 2 and 3 F3 should show 25
Greater than 3 F3 should show 36.
I have used IF to get the correct result when F3 is less than one but cannot work out how to add the other three results.
Command Bar Too Long
Dec 20, 20061. if I have a custom command Bar that is very long and I want it displayed in two rows how do I code it to start a new row at a certain point (button)?
- can I control the length of it?
2. How do I make the Formatting toolbar inactive on opening a particular workbook?
VBA What Is The Command To Scroll
Apr 24, 2007Simple VBA What is the command to scroll to over "x" number of columns? and/or rows?
I just the active cell to move over 5 cells to the right for example?
Command To Do Nothing In An If Statement
Oct 9, 2007whats the vba command to do nothing in an if statement
View 9 Replies View RelatedDynamic Max, Is It The Right Command
Jun 5, 2008with this
With Worksheets("Sheet2")
Application.WorksheetFunction.Max(.Range(.Range("A65536").end(xlup),.Range("A65536").End(xlUp).Offset(-20)))
End With
I'm able to calculate the max each 20 cells. Right?
If yes, how can I put that value into B1 ?
Command Text VBA
Jul 17, 2008I recorded this SQL query and got this and a Connection string, the query kind of works but i want to be able to edit more. Can someone tell me what does what in this huge string, i want to be able to edit the MatsViewerView.SpecType and other things like i can edit the lot number=2007312RC (very end of string). P.S. sorry about the formatting, i guess this is what excel does when it records a query onto a macro.
.CommandText = Array( _
"SELECT MatsViewerView.SpecType, MatsViewerView.LotNumber, MatsViewerView.WaferNumber, MatsViewerView.DieNumber, MatsViewerView.TestDate, PostAnalysis.Description, PostAnalysis.Achieved, WPGain1.WPGain" _
Custom Command Bar
Oct 17, 2008I am trying to add aright click menu item a cell.
I am using the following code in a module.
Sub addmenu()
Set nbar = Application.CommandBars("Cell")
Set ntrnew = nbar.Controls.Add(Type:=msoControlButton)
ntrnew.Caption = "Paste In"
ntrnew.Visible = True
ntrnew.BeginGroup = True
ntrnew.FaceId = 110
ntrnew.OnAction = "Test"
To trigger the first code when needed.
I know this all works when I put it in brand new workbook it works my custom menu appears and I can run the the "Test sub"
However as soon as I put this in the workbook where I want this to work it does not show the menu.
It is there though as when I step through the remove Sub I can see it matched and then deleted.
I thought at first it was protection but that is now removed but still no joy.
I can not see any setting that controls these sub menus.
Concatenate Command
Feb 11, 2009i need to merge several columns together with a comma seperator. but there are some columns with only set of data in and sometimes 6 columns with data. so i end of with something looking like WC,,,,, or GAU,MAJ,CHEM,,, etc.
i have over 6000 lines so don't feel like cleaning up manually.
FIND Command In VBA
Feb 27, 2009I need to find the row number of the first instance of the string 'TIMEACT
in column A. I know that it must exist at some point in column A, but I do not know where. Column A might have 100,000 entries... ie: a very large number, so I'd appreciate it if the code could be very general.
Getting Rid Of Command Buttons
Apr 9, 2009I have a range of cells (A1:D5) identified within a worksheet (worksheetA) as part of a user input form.
When a certain condition exists, a routine is executed that copies a range of cells (C1:F5) from another worksheet (worksheetB) to this defined range. The copied area consists of two command buttons. They get copied and pasted no problem.
When I want to reset (blank it out of its contents) my range in worksheetA from I copy another range over of just a range of blank shaded cells. However, doing this does not eliminate the command buttons left in the range on previous pasting.
How do I delete these command buttons left from previous pasting activities when I wish to reset my range?
ScreenUpdating Command
Feb 9, 2010I have VBA code that hides and display columns. I have it coded in between Application.ScreenUpdating = False and Application.ScreenUpdating = True commands thinking that it will not show all the flashing and movement of columns.
View 9 Replies View RelatedCommand Line
Mar 7, 2006Is there any way to run a command line from excel?
E.g. i want excel to call this command:
ping 10.100.100.100 and then return the result to a txt file?
Decode Command
Aug 10, 2006there is a vba function that is similar to a sql decode command. Something similar to this....
decode(variable,"jan","01","feb","02","mar"03"....)
In this example, the function will return the month number when given the month name.
does a command like this exist?
How Use The Vlookup Command
Feb 15, 2007I'm trying to figure out how use the Vlookup command. ( Sheet to Sheet, same workbook) Can someone explain the process one goes through to set this up? I have a grip on most of it but seem to be missing some small step.
What I have is this: On one sheet I have 50+ rows of information. Each row has 3 columns, the column number, text which describes a type of construction, and then the last column has the associated R-value. I want the user to be able to use a pull down in a cell on another sheet which describes the contruction and then return the R-value associated with that constrution.
