Free Microsoft Excel 2013 Quick Reference

VBA to command prompt - sending multiple commands to same line?

Good day all... hoping for a little help with the syntax on this one. An associate of mine created a phenomenal mini application in Python, which we can run from a command prompt manually. I'm trying to integrate his application into a spreadsheet our group has been using for quite some time now.

Other than opening a dos prompt and doing a "pushd" to map a network drive, I got nothing. And nothing I'm doing is working. And nothing I've read is helping.

I don't think this should be terribly difficult, but... well, for me it is.

I'm trying to do the following:
From VBA, open the Command window, which seems to work with
- Once the DOS window is open, I need to change directories to, something like 
"Corp.Bloomberg.comPn-dfsq_search"

- I need to then execute this line
"q_search.exe small_set.txt"

- Wait until the dataset is loaded (small_set.txt)

From here the script allows some user input, which it then processes (n-gram string searches done via Python), then outputs the data to a file, which I will import back into my spreadsheet.

Hope that makes sense - and thank you for any help!

JP


Post your answer or comment

comments powered by Disqus
my email from excel works great...


	VB:
	
 objMail 
    .To = myemail 
    .Subject = "Note from Production" 
     '.Body = "...."
     '.Body = ",,,,"
    .Body = ">>>>" 
    etc. 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
but i want to send multiple (and different) lines in the body of the email, but i can seemingly only send one...

???

thank you...

What would be the vba for a button to open a file in the same folder? I can't use a complete file path because its on a portable drive that is E: at work and G? at home.

The file name is UpdatedList.xlsm

Hi all, I hope someone can help. I have the following code that extracts data from mutliple workbooks and places it in a "summary" workbook. The code works- except if any of the source fields are empty the offset statement does not function as required and the data that should be on the next line appears in the line above. ( if the previous sheet had blank fields)

My thoughts are that if the field is blank then insert a mesage or "-". The most important thing is to keep the datpoints from the same source workbook on the same line. Thanks in advance!!

Sub Extract()
Dim myFile As String, myCurrFile As String
myCurrFile = ThisWorkbook.Name
' Folder location with school data to be extracted (do in 2 batches 1st 20 then 20+)
myFile = Dir("C:Draft 2 Summary Matrix*.xlsx")
Do Until myFile = ""
Workbooks.Open "C:Draft 2 Summary Matrix" & myFile
' School Name
Workbooks(myCurrFile).Worksheets("Sheet2").Range("A1").End(xlDown).Offset(1, 0) = Workbooks(myFile).Worksheets("Area Input").Range("C2")
' Grades
Workbooks(myCurrFile).Worksheets("Sheet2").Range("B1").End(xlDown).Offset(1, 0) = Workbooks(myFile).Worksheets("Area Input").Range("L2")
' Reported Enrollment
Workbooks(myCurrFile).Worksheets("Sheet2").Range("C1").End(xlDown).Offset(1, 0) = Workbooks(myFile).Worksheets("Area Input").Range("B22")
' Dorm 1-8 student count (for schools after first 20- change E13 to E11 for earlier batch)
Workbooks(myCurrFile).Worksheets("Sheet2").Range("D1").End(xlDown).Offset(1, 0) = Workbooks(myFile).Worksheets("Area Input").Range("E11")
' Dorm 9-12 student count (for schools after first 20- change E16 to E14 for earlier batch)
Workbooks(myCurrFile).Worksheets("Sheet2").Range("E1").End(xlDown).Offset(1, 0) = Workbooks(myFile).Worksheets("Area Input").Range("E14")
' Therapy room SF
Workbooks(myCurrFile).Worksheets("Sheet2").Range("G1").End(xlDown).Offset(1, 0) = Workbooks(myFile).Worksheets("Space Summary detailed").Range("D88")
' Resource room K-5 SF
Workbooks(myCurrFile).Worksheets("Sheet2").Range("H1").End(xlDown).Offset(1, 0) = Workbooks(myFile).Worksheets("Space Summary detailed").Range("D91")
' Resource room 6-8 SF
Workbooks(myCurrFile).Worksheets("Sheet2").Range("I1").End(xlDown).Offset(1, 0) = Workbooks(myFile).Worksheets("Space Summary detailed").Range("D92")
' Resource room 9-12 SF
Workbooks(myCurrFile).Worksheets("Sheet2").Range("J1").End(xlDown).Offset(1, 0) = Workbooks(myFile).Worksheets("Space Summary detailed").Range("D93")
' Testing room SF
Workbooks(myCurrFile).Worksheets("Sheet2").Range("L1").End(xlDown).Offset(1, 0) = Workbooks(myFile).Worksheets("Space Summary detailed").Range("D94")
' Gifted and Tallented room SF
Workbooks(myCurrFile).Worksheets("Sheet2").Range("M1").End(xlDown).Offset(1, 0) = Workbooks(myFile).Worksheets("Space Summary detailed").Range("D95")
' Academic Buildings
Workbooks(myCurrFile).Worksheets("Sheet2").Range("N1").End(xlDown).Offset(1, 0) = Workbooks(myFile).Worksheets("Area Input").Range("B37")
' non-Academic Buildings
Workbooks(myCurrFile).Worksheets("Sheet2").Range("O1").End(xlDown).Offset(1, 0) = Workbooks(myFile).Worksheets("Area Input").Range("B39")
' unusable Buildings
Workbooks(myCurrFile).Worksheets("Sheet2").Range("Q1").End(xlDown).Offset(1, 0) = Workbooks(myFile).Worksheets("Area Input").Range("B41")
' portable structures
Workbooks(myCurrFile).Worksheets("Sheet2").Range("R1").End(xlDown).Offset(1, 0) = Workbooks(myFile).Worksheets("Area Input").Range("B43")
' ineligible area
Workbooks(myCurrFile).Worksheets("Sheet2").Range("S1").End(xlDown).Offset(1, 0) = Workbooks(myFile).Worksheets("Area Input").Range("B27")
' surplus or shortfall (for schools with dorms use different field?)
Workbooks(myCurrFile).Worksheets("Sheet2").Range("T1").End(xlDown).Offset(1, 0) = Workbooks(myFile).Worksheets("Space Summary detailed").Range("E19")

' close file and continue loop
Workbooks(myFile).Close savechanges:=False
myFile = Dir
Loop

End Sub

Hi all,

I have a list of records in a Excel that I would like to send by email each of them to the appropriate customer (defined by a field in each record).

There is one record per line and some lines relate to the same customer so I would like to compile and send in the same email.

At the moment I lookin at this function to send the emails.

Sub email()
Dim OutApp As Object
Dim OutMail As Object
Dim EmailAddr As String
Dim Subj As String
Dim BodyText As String

EmailAddr = "email@email.com"
Subj = "Your subject"

BodyText = "your body "

Set OutApp = CreateObject("Outlook.Application")
Set OutMail = OutApp.CreateItem(0)

With OutMail
.to = EmailAddr
.CC = ""
.BCC = ""
.Subject = Subj
.Body = BodyText
.send
End With

Set OutMail = Nothing
Set OutApp = Nothing
End Sub

Ideally I would like to include the information on the table as part of the body of the email rather as an attachement.

Any suggestion?

Thanks for reading.

I need a resume header format that looks something like this:

Job Title (left justified) and then on the right, Date (right justified) Same line.

How can I make everything line up perfectly without using tabs and spaces. (Tabs and spaces mess things up if margins are adjusted or font is adjusted).

See attached for what I'm trying to do.

Thank you much,

I currently have a macro that creates a pivot table with Row Labels of "Unique Model Number" and a Report Fiter of "Sold To Customer." I need a macro that will create a command prompt asking "Which Customer would you like to view?" The prompt needs to have the same drop down that you would have if you clicked on "All" in the Report Filter. When the prompt is executed, the report filter will be changed according to what has been selected. I am fairly new with VBA and any help would be much appreciated.

Thanks,
J

SOLD_TO_CUSTOMER (All) Row Labels Count of UNIQUE_MODEL_NUMBER 3051SFPDS010W3M0800D31AA1A2KDM5C5 23 00753-9220-0001 16 3051CG4A02A1AH2E5M5CN 7 475HP1EKLUGMT 7 3051CD2A03A1AH2E5CN 6 3144PD1A1E5CNM5 6 00809-0100-4001 6 03031-0242-0001 6 MISC-PRES-0004 6 03031-0234-0001 5 3144PD1A1E5M5Q4CNB4 5 3144PD1A1E5 5 1420A2A3A4N5WL2 5 03031-1300-0002 5 751AM4E5B 5 3051CG4A02B1AH2E5M5CN 5 3051CG4A02A1AH2E5CN 4 03031-0242-0002 4 00275-0096-0001 4 00475-0003-0022 4 1067D0P160550E5XA-248HANAN0XAF6 4 3051CG4A02A1AKDH2CN 4

I am using this command in my vba function but it keeps asking me for the host. HELP PLEASE!!

scp://userassword@10.10.10.1 (This is only part of it due to security) everything before this works but when it gets to this point it asks for a host in the command prompt)

I am trying to run the following code in the DOS prompt window using VBA: "H:spanposconv H:hello2.csv H:hello2.pos." I have the following code which opens the command prompt. However, I do not know how to input the code and then execute in the prompt. Can someone help me how I can do this?
HTML Code:
Sub CallApp()
    Par As String
    Par = "H:spanposconv H:hello2.csv H:hello2.pos"
    Call Shell("C:WINDOWSsystem32cmd.exe", 1)
   
End Sub


I am using a shell command to open a converted excel file with .exe extension:
file_nm = Range("file_name")   'this refers to a file name with .exe extension in a workbook cell

For Each wb In Workbooks
If wb.Name = file_nm Then
MsgBox ("The " & wb.Name & " is already open.")
Exit Sub
End If
Next

open_str = ActiveWorkbook.Path & "" & file_nm
Dim RetVal

RetVal = Shell(open_str, 1)

[COLOR=#333333][b][noparse]
[/noparse[/b][/COLOR]

The shell command opens the workbook in another instance of excel. Therefore searching for an open workbook of the same name to avoid opening the same workbook twice (as in the for Each loop above) doesn't work because the previously opened workbook is in another instance of excel.

I need either to have the shell command open the file in the same instance of excel (preferably), or else know how to look in another instance of excel to see if the workbook is already open.

Please help with example code.

Thanks,

Jerry Erwin

I have a quite specific VBA request - I found how to do it on another website but I can't find it after hours of looking (I should have written it down I know) - what I need is:

VBA to send the same email to multiple addresses (they would be listed in a sheet, say from A1 to A500)

If possible I'd like it to send the message to one address at a time, but automatically, so click 'send' and it goes to the first address, then second, then third, etc..

I would like to be able to type the Subject and the content in cells on the same sheet as the addresses, possibly with some HTML in.

Is any/all of this possible?

I am using the following code to send a row of data from one sheet (list) to another sheet (form) within the same workbook by pressing my command button. What I would like to do is open a different workbook that has the form and populate the new workbook instead of a sheet in the same workbook. I currently hit the button copy the data and past it into the separate form to allow for saving individual files and keeping the record list separate.

I dont know how to rewrite the code to open a my other workbook, nor do i know where to put it in the exisiting code. I have also attached copies of the 2 documents that i am working with. I do need to do this with 3 other systems as well but if i can get help on this one I will be able to get the other ones programmed (I think, i found this code for a different purpose on another site and was able to make it work for me, and i know nothing about VBA)

Code,

Private Sub cmdFill_Click()
Dim shInv As Worksheet

Set shInv = ThisWorkbook.Sheets("PackingList")
If Selection.Column <> 3 Or Selection.Value = "" Then
MsgBox "There is nothing to export!" & Chr(10) & "Select a cell with data from column C," & Chr(10) & "and try again"
Exit Sub
End If
rw = Selection.row
With shInv
.Cells(2, 1).Value = Cells(rw, 3).Value
.Cells(9, 6).Value = Cells(rw, 4).Value
.Cells(9, 7).Value = Cells(rw, 5).Value
.Cells(12, 6).Value = Cells(rw, 6).Value
.Cells(12, 7).Value = Cells(rw, 7).Value
.Cells(9, 8).Value = Cells(rw, 8).Value
.Cells(21, 4).Value = Cells(rw, 13).Value
.Cells(8, 2).Value = Cells(rw, 14).Value
.Cells(9, 2).Value = Cells(rw, 15).Value
.Cells(10, 2).Value = Cells(rw, 16).Value & ", " & Cells(rw, 17).Value & " " & Cells(rw, 18).Value
.Cells(14, 6).Value = Cells(rw, 19).Value
End With
Cells(rw, "c").Select
shInv.Select
End Sub

Hello ,

i want to make a multiplication , i knew the command but there is a snag

multiplication command should be run as i*j and result will be printed on 'K' ( Command : =i2*j2)

Now the problem is that i do have more than 1000 Rows and i want to do the same for all those , am i going to edit and enter the same command for each row ?

i was wondering if anyone could send me a solution to make a multiplication for i*j and print it to the K for all the rows

Plz check the attachment file !!!

Excel 2007 - There was a problem sending the command to the program

I am running Windows 7, with Office 2007.  All of a sudden when I open an excel file I get the "There was a problem sending the command to the program".

I have seen messages relating to Word 2007 generating this but not excel.

Can anyone help?

I have an Excel Spreadsheet of an evaluation form for a team manager in a call center. There are a few tabs (Team Data, Eval Form, and the individual agent totals). I have a command button on the Eval form to 'submit' the scores of a particular evaluationl to the appropriate agent tab. The agent is selected on the eval form from a drop down list. Once the 'submit' button is clicked it should copy the eval form data and paste into the appropriate agent tab. Each agent will have 3 eval's each, and the data will paste into either 'call 1', 'call 2', or 'call 3' for that agent depending on how many eval's have already been done.

Can someone help me create the coding for the 'submit' button that will allow me to do this? I've attached the Excel Spreadsheet I am using as the Evaluation Form and team totals. I'd like the coding not to be static so that the same Eval form with the 'submit' button can be used if the Team Data is changed.

Word2007-There was a problem sending the command to the program!

Windows RC
It's fine for a long time,suddenly doesn't work,maybe the change of some other programs or system
I can only start the word 2007 first,then I can open the file,but in other account such as guest my word is ok.
If I right-click the word

Other sutes of Office 2007 bisides word 2007 is ok

My version is office 2007 enterprise.

Reinstall the office doesn't take effect.

Open winword.exe directly is fine.

http://zaq5sa.bay.livefilestore.com/y1pzX0dVQyt4ZEHxhBnyejMVvmPvplVBErUTYRlMj5XJpIkSL2jzMrt9VAa3Cy11aFNukFOMpBP3ejVL2gy8D2dZUvxls2dJx47/Untitled%20-%20Copy.jpg

I very often need to reformat OLAP-based pivot tables in Excel 2007 : change the layout to tabular , remove subtotals, ...etc.

I want to automate these repetitive tasks with macros - I am a rather experienced vba programmer, yet still learning.
Doing this by using the pivot table object can lead to relatively complex code. A much simpler approach would be to use the built-in commands that are available from the contextual ribbon pivottable tab. Understanding how to do this would also open many doors for automating lots of other tasks.

I have searched and read numerous posts and articles including the following MS article Executing a Built-In Command, downloaded the list of IDs files but I have so far not found a way to make it work.

For each control, the list of IDs file specifies the following attributes:
Control Name,Control Type, Tab Set Name, Tab Name, Group Name,Parent Control, Secondary Parent Control, Tertiary Parent Control, Ordering, Policy ID
The ribbon control that I want to execute is PivotTableLayoutShowInTabularForm which has a Policy ID = 12321
However, even though the current selected cell belongs to a pivot table, when I run the following vba line, I get the error "Object variable or with block not set":

	VB:
	
Application.CommandBars.FindControl(ID:=12321).Execute 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
I must be missing something. Can someone provide some help or code that shows how to "execute" a built-in button, even if it is in a contextual ribbon tab ?

Regards,
Bert

Hey. I am using a button on a custom-made form to create a new worksheet. Clicking the button will:

1. Ask for a name
2. Make a worksheet using that name
3. Give worksheet a random color
4. Insert Name into A1
5. Bold, and change name to size 24 in A1
6. Insert a command button into the new worksheet.

Step #6 is the ONLY one that doesn't work, mostly because I don't know how to do it. Another worksheet has a command button on it already that is attached to a totally different macro. I would like an exact duplicate of that command button on every new page added with this user form. (It can't be a copy and paste code in case I should update the other button in the future.) How can I use VBA code to automatically create a working command button for step 6?

I've written two Excel queries extracting data from different tables in a Sage accounts database - one is transactions, the other is category groupings.

So each transaction has one code attached - eg 4010, and that code falls within one of the category groups - eg 4000 to 4100.

What I'm needing to do is add an additional column to the transaction data set in Excel which will be the category title for the relevant category group.

So I need to write something that says if 4010 falls between the lower and upper codes on the first group on the list then add the category title for that group to the transaction listing, and if it doesn't continue to the second group of codes and so on until you find the group it falls into.

Effectively its a series of nested if() commands, however with around 70 category groups (and this number is flexible as more could be added!) I need some kind of variable loop code. I know VBA is the answer to my problem, I just don't have the technical knowledge to know what that answer is!!

Can anyone point me in the right direction?

Hello
I've got a workbook with command buttons on multiple worksheets that have been linked to macros (i.e. go into the relevant spreadsheet, press the button and the macro is activated).
I would like to create a button that activates all the other button - does anyone know what code is used to activate a command button (linked to a macro).
Thanks

Tom

Hi All,

I want a VBA code to send multiple mails. I tried mail merge option but it does not have CC facility. Can you please help me out with a vba code to send multiple mails which choose mailing address from excel for both CC and TO.

Thanks in advance!

Regards,
Shweta

Simple but goodie. Send DOS Commands from VBA. This examples assumes you have the net send service running. SP2 disables this service by default. Obviously you can send any DOS command that you like, this is just a simple example.


	VB:
	
 Send_CMD_DOS() 
     'DOS SHELL SEND TO VBA*******************
    strDOSCMD = "net send your_network_id hello world" 
    sCmdLine = strDOSCMD 
     'SEND TO DOS
    dblRetVal = Shell(sCmdLine, vbHide) 'VBHIDE HIDES SHELL WINDOW (BLACK DOS WINDOW)
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines


I need to know how to use vba to change the caption in a command button on an
excel spreadsheet. Any assistance would be appreciated.

Paula

Hello, I'm having a strange problem working with Excel 2010, which I just installed. I'm am opening a new instance of Microsoft Internet Explorer and sending the command to navigate to any URL. It works fine up to this point, but when I try to access the ie.object after that, I get the RunTime error 8010108 or 80004005, whiich tells me it has disconnected from the object or has suffered an automation unspecified error. I don't know why this is happening. I've been using the same code in other versions successfully for probably 7 years. Has anyone seen this problem before? Could it be a permissions problem? Something I'm overlooking?

Here is the very simple code I'm using:

Sub openIE()
    Dim IE As InternetExplorer
    Dim MyStr As String
     
    Set IE = New InternetExplorer
     
    'Search google for "VBA Excel Automation"
    IE.Navigate2 "http://www.google.com/search?q=vba+excel+automation"
     
    'Loop unitl ie page is fully loaded
    Do Until IE.readyState = 4
    DoEvents
    Loop
     
    'This wait was added to isolate the exact point when the ie object was disconneecting
    Application.Wait Time + TimeSerial(0, 0, 10)
     
    'return text from google page
    MyStr = IE.document.body.innerText
    Sheet3.Range("A2") = MyStr
    Set IE = Nothing
End Sub
Another code that gives me an unspecified error is below:

Sub MoveInnerHTMLtoSheet3()

    Dim URL As String
    Dim IE As InternetExplorer
    Dim HTMLdoc As HTMLDocument
    Dim TDelements As IHTMLElementCollection
    Dim TDelement As HTMLTableCell
    Dim r As Long
    
    'Saved from www vbaexpress com/forum/forumdisplay.php?f=17
    URL = "http://www.google.com/search?q=vba+excel+automation"
    
    Set IE = New InternetExplorer
    
    With IE
        .navigate URL
        .Visible = True
    
        'Wait for page to load
        While .Busy: DoEvents: Wend
    
        Set HTMLdoc = .document
    End With
    
'    Set TDelements = HTMLdoc.getElementsByTagName("TD")
    
    Sheet3.Range("A2") = HTMLdoc
    Set IE = Nothing
            
End Sub
Thanks for any advice you can offer.......

John

As part of a larger script this is the only part that I cannot get to work. Your assistance would be much appreciated:-


	VB:
	
 Batch_Run() 
    Dim RetVal 
    RetVal = Shell("C:Usersuser1Documentsdfs_update.bat", 1) 
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
The result :-

dfscmd_error.JPG

When I attempt to run this bat file from a command line the dfscmd.exe executes correctly:-

dfscmd_from_command_line.jpg

The system error shown in the last img confirms that the script works from the command line. So effectively if I can get it to respond like this when initiated from excel vb it would be perfect.

The .bat file would contain multiple command lines that would utilise Microsofts distributed file system to map/unmap dfs links to storage locations. If I can only get the .bat file executed from the excel sheet that would be great.

Your asisstance/guidance would really be much appreciated.

Regards
Ronnie


No luck finding an answer? You could always try Google.