Run External Programs With VBA
Oct 26, 2007
I'm wondering if it is a all possible to have a VBA macro call another program (like a Perl scripts, or a Python script) from within VBA?
I have no idea if this is possible. I know that people often write programs which call other programs, but would have no idea where to get started on this.
If it IS possible, can you tell me if it is is extremely complicated?
I have to have a VBA script read some complicated text files, and parse and extract information from them. Perl is so great at this, and I seem to get a headache trying to do any pattern searching and matching with VBA. If it was instead possible to write a Perl script, which is then run by VBA, that would make my life so much easier... :-)
View 3 Replies
ADVERTISEMENT
Jun 28, 2007
Is it possible to send the contents of a cell to outlook via VBA trigger?
View 14 Replies
View Related
Apr 29, 2014
I have a macro that copies rows from my DAILY OCCURENCE sheet that have a YES in them to my MANAGER SUMMARY SHEET and G1 is selected which has a hyperlink that opens a new email with the manager's email address and Subject pre-assigned.
I then have to manually go back to the newly generated summary sheet and select the cells and paste them into the email that was just opened.
Is there a way that the generated rows are already copied in memory and all i have to do is CTRL+V into the new email, or even better, the cells are pasted in the email automatically? So basically either the information being copied over stays in memory, or after it is copied into the summary sheet it is copied again. I just dont know how to copy unpredictable ranges generated by macros.
The code that generates my summary sheet for my manager is below.
[Code] ...........
View 4 Replies
View Related
Jul 8, 2014
I'm trying to make a spreadsheet that references data tables I have pulled from the internet, with the hopes that these tables will update as I refresh the data connections. I'm in the early stages of creating this spreadsheet right now, but whenever I try to refresh my data connections, it takes a tremendous amount of time. I have about 6 connections with 100 row lines total. Is this typical with data connections to the web? Is there a better way to do this?
View 1 Replies
View Related
Apr 27, 2013
i have to take the PGN chess game notation file into excel.it is a text file from notepad. it is like this as shown hereunder. how can i get this into columns movewise. ex.1.d4 Nf6 should come in A1 and 2.c4 d6 should come in A2.
1.d4 Nf6 2.c4 d6 3.Nf3 Nbd7 4.Nc3 e5 5.e4 Be7 6.Be2 O-O 7.O-O
c6 8.Qc2 Re8 9.Rd1 Bf8 10.Rb1 a5 11.d5 Nc5 12.Be3 Qc7 13.h3
Bd7 14.Rbc1 g6 15.Nd2 Rab8 16.Nb3 Nxb3 17.Qxb3 c5 18.Kh2 Kh8
19.Qc2 Ng8 20.Bg4 Nh6 21.Bxd7 Qxd7 22.Qd2 Ng8 23.g4 f5 24.f3
Be7 25.Rg1 Rf8 26.Rcf1 Rf7 27.gxf5 gxf5 28.Rg2 f4 29.Bf2 Rf6
30.Ne2 Qxh3+ 31.Kxh3 Rh6+ 32.Kg4 Nf6+ 33.Kf5 Nd7 34.Rg5 Rf8+
35.Kg4 Nf6+ 36.Kf5 Ng8+ 37.Kg4 Nf6+ 38.Kf5 Nxd5+ 39.Kg4 Nf6+
View 5 Replies
View Related
Jan 10, 2013
I am creating a tool that many people will be using. I need a way to do the following from vba:
1. Click on the "From Web" button under Get External Data section in Data and show the web query pop-up
2. Input the url into the field and press enter
At this point, it will be the user's responsibility to log in w/ their username and password. I have tried to record a macro w/ no success. I just need to get the query open for the user without them having to do anything.
View 2 Replies
View Related
Dec 3, 2008
I have some data in tabular form on a sheet. I want to represent this data on a different sheet, but have it be dynamically updated. I'm thinking that the smartest way would be to give the original data a named range, then do some sort of external data referencing to the source from Data - Import External Data. But this sounds kind of silly.
How do I do this? And no, I don't want to use pivot tables because I don't have statistics to populate in the cells...I just want to display the data as is.
View 9 Replies
View Related
Jun 6, 2014
I'm trying to set-up a costing system as follows:
Each job has it's own xls file, this is a list of manually input costs and a total cost at the bottom (total is the same Cell reference: G24) for all xls files. The xls file is named the same as the job - F0001, F0002 etc.
At the end of every month we then want to create a master list xls file for all jobs being invoiced that month. This is a list of all the jobs - F0001, F0002 etc. In Column A, and the corresponding cost totals in column B.
In the master file we want to be able to type in the job reference to column A (i.e. the file names of the single job files -F0001, F0002 etc) and have the corresponding total for that reference display automatically in Column B (i.e. Cell G24 from file F0001 will display in the master file cell B1, when 'F0001' is typed in cell A1).
Ideally the master file would display all the individual entries automatically, so no need to input the job references either - simply scanning the directory for the other files present and creating a list automatically in the master file, alternatively it can get the job reference from Cell A1 in the individual job files, instead of from the file name.
View 2 Replies
View Related
Mar 1, 2007
I update files monthly and save them each month with a new name (eg. Jan, Feb, etc.).
In the file I have a reference from one tab to another tab within the same file. So every time I re-save this file with a new name, it should still reference the cell in the other tab - same file.
BUT on 2 occasions this reference changed to an "external" reference to the previous month's file.
I've been doing this every month for over 2 years and this problem has only happened 2 times. I know others that have experienced this, but nobody knows what caused it.
Does anyone know what would cause this? The biggest problem is that I may not notice this has happened for a long time.
One other thing I might add. I use an older version of Excel (2000). I share these files with other people who may have newer versions (I'm not sure who has what). Can sharing these files with others over a network system have something to do with this? Can opening "read only" when someone else is in the file and then saving as a copy do this?
View 11 Replies
View Related
Feb 10, 2010
way to import the data from this link into an excel so that all the data will be listed in columns. IE: Association name, contact, advisor, etc....
So far I have not found an efficient way to do this through importing the data.
View 11 Replies
View Related
May 16, 2013
My raw data is exported in CSV format. Is it possible for Excel to link to the data or can it only link to an Excel format workbook?
View 1 Replies
View Related
Oct 13, 2009
I have 5 or 6 macros set up to import stats from the internet to specific sheets. I want all of my imported data to be pasted starting at A1. However when i run these macros, the data IS pasted at A1, but when i run it a second time (or the next day after the pages have been updated), it pastes the data BESIDE the previously pasted stuff. here's what i mean..
say my imported data spans columns A to S.. everything's fine. but tomorrow i want to update the data so i run the macro again. well, it pastes the new data to A1 and again it spans from A to S like it's supposed to, but now it also tacks on the same paste job from S to, AG for instance.here's the code... There are currently 5 macros that have this problem. I thought by selecting A1 as the starting point, i could alleviate all of this but apparently not.
View 2 Replies
View Related
Oct 30, 2009
if it is possible to have one xls file load all .xls files that are inside a folder or a folder with subfolders and so on?
View 9 Replies
View Related
Jan 10, 2010
Do any of you know if it's possible to send a scroll up / down to another app, perhaps in some fashion similar to send keys? I'm essentially pulling data from a proprietary UI into a spreadsheet where I can manipulate the data...the problem is that some of the pages have a table on the bottom half of the page with a vertical scrollbar - and I can't seem to get to the lines which are out of view.
View 4 Replies
View Related
Nov 20, 2011
I have file name "WM" from where I want to run macro and want that It open a File let say "test" & do the rest of the functions. like if I have following code how can it possible.
"TML" sheet is on file "WM" and "MD" sheet is on file "test".
Code:
Sub Copy_on_backup_file()
Workbooks.Open Filename:="C:UsersBinDesktop est.xls"
'*** Select the destination cell
With Sheets("TML")
LR = .Range("A" & Rows.Count).End(xlUp).Row
Range(.Cells(3, 1), .Cells(LR, 3)).Copy Sheets("MD").Range("B" & Rows.Count).End(xlUp).Offset(1)
End With
[Code]...
View 6 Replies
View Related
Sep 24, 2012
There are 100 loans, each one of them need to be tested on 11 different tests, the results are either "pass" or "fail". All the test results are stored in the Boolean array. g_sel_tag(). For instance, if g_sel_tag(2,7) = true that means the 2nd loan has failed on the 7th test.
But I got stock with the following code, I think there should be a loop for the public variable rep_num in order to loop through the 11 test results for each loan. I have searched the entire project but did not find any value was assigned to the variable rep_num. The following code is part of a huge project, I can't test run it to see if the following code is valid at this point.
I am new to VBA therefore wondering if it is valid with no specific value assigned to rep_num. (I think there should be something like "for rep_num = 1 to 11" in order to get it running).
There are several codes in the project with similar structure where they just use rep_num without assigning any specific values.
Code:
Dim rep_num As Interger
Sub test_results_reporter (rep_num As Interger)
Dim i As Interger
Dim g_sel_tag(1 to 100, 1 to 11) as Boolean
For i = 1 to 100
If g_sel_tag(i,rep_num) Then Msgbox "Loan" & i & "has failed on test" & rep_num
Next
End sub
View 2 Replies
View Related
Dec 18, 2006
I am working on a spreadsheet that hits an external site. My questions is, I need to hit the site about 80 times becuase I search for different info. It there a way to get the vba to go down a list 80 of names rather than me entering the vba code 80 times with the different names?
View 9 Replies
View Related
Oct 31, 2007
On the first worksheet of my workbook I have a list of file names in cells I11:I27. The filenames have been 'compiled' using the concatenate function, so for example, although cell I11 displays:
Richard 2007-09.xls,
the cell contents are really:
=CONCATENATE(G20," ",L9,"-",J9,".xls")
Using VBA, I want to be able to open each respective file in cells I11:I27, copy the contents into this workbook (sheet=raw), and close it. My problem is that I don't know how to tell excel the filename in VBA, because the cells contents are not really the filename - they are a formula.
View 9 Replies
View Related
Dec 26, 2007
I'm trying to pull in data from another Excel workbook which gets updated on a regular basis. Using the Import External Data fucntion in Excel I created a query which pulls the data from the original workbook into a tab in the workbook I'm using. The issue I'm having is that not all of the column headers get pulled in when using the Import External Data function. I tried typing the column headers in myself but they disappear again after refreshing the data set. Any ideas what is causing the issue?
View 9 Replies
View Related
Feb 5, 2008
I have a list of objects in column A
I want to select that list or part of it then run a VB module that references each item in the selection and compares it to a master list in a separate workbook that contains both the object and an additional value, and display that additional value in column B of the original workbook against each object in column A
View 9 Replies
View Related
Mar 7, 2008
I have a userform which saves project details....
Part of the makeup of the userform is a label control (label11) which can store a filepath for an external file (word doc, PDF etc)
How can i open this external file? (the filepath is stored as the caption)
View 9 Replies
View Related
Apr 1, 2008
I use external data queries in excel. That's how I look up data in my access databases because I have so many access and SQL databases it's just eiser to be able to do quick queries on multiple databases at the same time in one spreadsheet. And, I can save the external data query as a tool to use in future queries. I have tons of tools and external data query is the foundation of most of them.
What bugs me is EDITING data.
If I want to do some light simple editing in an SQL database I am required to use a linked table in Access or SQL Studio Express Manager. Great programs, but I want to integrate!
Is there no way to make excel a database editor? I can retrieve data from any database or program with excel just fine, but is there a way to use excel to DIRECTLY EDIT the data in an external database with EXCEL like I can in access linked tables?
View 9 Replies
View Related
Jul 15, 2008
I am trying to import the data on the url below. When I go to the external data inport the page is loaded and I click the indicator to load the central table but when the data loads the page is severally corrupted. I have found this on a few pages but some pages load correctly.
View 9 Replies
View Related
Jul 18, 2008
I used to get data from external excel, access files and today tried to put together query to get data from CSV file. While using same source file, I have creted several queries to get data out based on criteria. For some strange reason, same structure queries that did run with excel or access as data source, does not want to work the same way with CSV files.
Here is example of non working query SQL:
SELECT Vendors.Vendor, Vendors.Name, Vendors.`Name 2`
FROM `Q:FinanceRapporterPSAPRaw`Vendors.csv Vendors
WHERE (Vendors.Name Like '%' & ? & '%')
If I enter "London" as a criteria input, it will not work. However, if I enter "ondon" it will find me all values records whre name contains London... Seems like I need to skip first symbol when entering criteria. This is, of course, not something I would like to do.
View 9 Replies
View Related
Aug 20, 2008
I want to be able to import an external worksheet (say sheet1) to some data structure such as a 2d array or a dictionary. I don't want to store imported data temporarily in a local worksheet - I want to import external worksheet straight to array!
I have code that can import to a given worksheet name ... but I don't know how I can modify it such that it dumps it to an array or a dictionary.
The code I have is:
Sub read_in_workbook(sheet_name As String, source_dir As String, file_name As String, Optional append_from_y As Integer)
Dim rst As ADODB.Recordset
Set rst = New ADODB.Recordset
Dim stCon As String, stSQL As String
Dim fsoObj As Scripting.FileSystemObject
Dim fsoFolder As Scripting.Folder
Set fsoObj = New Scripting.FileSystemObject
Set fsoFolder = fsoObj.GetFolder(source_dir)
If append_from_y = 0 Then append_from_y = 1
stCon = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & fsoFolder & "" & file_name & ";" & _
"Extended Properties='Excel 8.0;HDR=No'"
stSQL = "SELECT * From [Sheet1$]"
rst.Open stSQL, stCon, adOpenForwardOnly, adLockReadOnly, adCmdText
Worksheets(sheet_name).Cells(append_from_y, 1).CopyFromRecordset rst
'###Instead of it dumping to a given sheet name, can I turn this routine to a function
'###and have it return a populated array or dictionary object?
End Sub
View 9 Replies
View Related
Apr 7, 2009
If I have a spreadsheet that performs a VLOOKUP to an external sheet, is there anyway I can get it looking at a certain cell in my current spreadsheet to say which external spreadsheet to look at?
View 9 Replies
View Related
Apr 18, 2009
I’ve got a question regarding import of external data in excel. The workbook I am conceiving at the moment does have a database query from another excelfile(a report from my business system) via the standard Excel functionality and it works perfectly fine.
What I can’t figure out is how to re-direct my query to any file/path I choose.
At the moment I just replace my original file with a new freshly updated one but I am stuck with the original path and filename or else it won’t work.
View 9 Replies
View Related
Jan 29, 2004
I have been trying something like this before, i could not get it fixed. Let's give it another go:
I have a number of files in a specific folder. I need to extract values from cell Sheet1!G42 out of [b]every file[b] in this folder. Problem that occurs, is that I do not know how many files are in this folder, neighter do I know what the filenames are. I need some magic to perform extraction of the desired values.
As for the files in the specified folder I have this VBA code, that suits my needs just fine:
Private Sub Workbook_Open()
Dim fileList() As String
Dim fName As String
Dim fPath As String
Dim I As Integer
This code will display all found files, including their path in column AD. (as plain text)
[b]Question that remains:[b] how am I to fix a formula In (column AE) that will extract the value found in Sheet1!G42 of each file? This must be very easy, but i can’t get this to work….
View 9 Replies
View Related
Dec 6, 2006
I want to link data between two files, which are always in the same position relative to each other, but can be copied to other locations. Here is an example:
D:Job 1SourceJob Info.xls
D:Job 1SlaveClient Form.xls
Cell B1 in Client Form.xls refers to Cell A1 in Job Info.xls. This link is created when both files are open. When Job Info.xls is closed, the reference in Client Form changes to 'D:Job 1Source[Job Info.xls]Sheet1'!$A$1.
Then, I copy and save Job 1 as Job 2. Thus, the second set of folders are: ....
View 7 Replies
View Related
Apr 14, 2007
='[January 07.xls]PPC Stats'!$C$19+'[Febuary 07.xls]PPC Stats'!$C$19+'C:[March 07.xls]PPC Stats'!$C$19+'C:[April 07.xls]PPC Stats'!$C$19+'C:[May 07.xls]PPC Stats'!$C$19+'C:[May 07.xls]PPC Stats'!$C$19+'C:[June 07.xls]PPC Stats'!$C$19+'C:[July 07.xls]PPC Stats'!$C$19+'C:[August 07.xls]PPC Stats'!$C$19+'C:[September 07.xls]PPC Stats'!$C$19+'C:[October 07.xls]PPC Stats'!$C$19+'C:[November 07.xls]PPC Stats'!$C$19+'[December 07.xls]PPC Stats'!$C$19
I am all these spreadsheets in the same spreadsheet folder that is calling it?
Is a direct path needed?
View 5 Replies
View Related
