Connecting Between VBA Excel And Web Site
Dec 7, 2009
I have a some problem with connecting between VBA Excel and web site.
I wrote a macro to insert the data from excel but there is one problem with displaying the correct view.
I use this site to get the price of the bonds:
[url]
it is the site of russian exchange
First of all I write the ISIN code, for example RU000A0E6X12, in the "Quote Search" field and then press Enter for getting the data. After it I can choose the History information or Online from the last field in the left margin. I want to chose "History" and after it the dates from and till are appearing automatically. The automatization of it is my task. And I have only one problem in the end.
The program:
View 9 Replies
ADVERTISEMENT
Apr 25, 2014
Due to one of our business requirement I have to fetch so many users Alias name through Outlook.
I am following below steps to achieve that Open new mail, giving user's name in to list, press ctrl+k, then right click properties. In properties I use to find alias name of user. I want to automate this process through excel sheet. I want to provide names in one column as Input to excel sheet and want to fetch alias name in next column of excel sheet.
View 1 Replies
View Related
Jan 15, 2014
I have a list with different categories and several characteristics per category. My goal is to identify the 10 strongest growing characteristics (I guess by sorting them in descending order) and then show them in this order, but organized in the respective categories. e.g.: if "Psychographic">"Demographic" and "geographic" and "behavioralistic", then put "Psychographic, including its subordinated characteristics that are included in the top 10, first. Follow this order until all Top10 members are included.
View 2 Replies
View Related
Jun 14, 2012
I am running Excel 2007 and connecting to an Oracle DB.
I have created a VBA macro to connect to an oracle DB and retrieve data then place it in a table. The code works, but it uses DSN entries which are specific to my computer. I want to be able to distribute this Excel spreadsheet to others in my company and have them be able to click one button and update the data. I figure the most logical way is to connect to the DB using an IP address that should work for anyone on the intranet.
How do I modify the connection info below to have it connect via IP?
I tried "Data Source = 10.1.1.10orcl" and "Data Source = 10.1.1.10", both of which VBA dislikes.
Here is what I have now:
Code:
Sub Create()
Dim Servername As Range
Set Servername = ActiveWorkbook.Sheets("Summary").Range("B16")
Set StartDate = ActiveWorkbook.Sheets("Summary").Range("B3")
[Code] ..........
View 2 Replies
View Related
Jan 11, 2014
So I have an excel workbook that is connected to an Access database I use. When the workbook opens, it refreshes the data from a couple queries in Access. However, every time it refreshes the data, it modifies several named ranges pulling from areas of those tables.
Example: $A$2:$A$50000 becomes $A$2:$A$50002 if there are two new lines entered into the table.
Because of this, some of the lookups and arrays I use are mismatching and giving errors. How to prevent the ranges from updating when the table refreshes?
View 3 Replies
View Related
Nov 17, 2013
I have a list of web address in column A. All of the web address belong to the same site and are different products at the store. I want to be able to some how launch the sites and have Excel copy the text string which follows the word "PRICE:" on the site back into Excel into the corresponding cell of column B.
View 3 Replies
View Related
Jan 4, 2012
Automating Excel from Access, I'm attempting to generate a single sheet workbook and save to a Sharepoint site. This process works fine on my machine (XL2007, XP), but on a coworkers computer (XL2003, XP), the code throws a 1004 error on the save as line. However, the really odd thing is that stepping through the code doesn't throw an error on the coworkers machine.
Here's the sub being ran; the line it errors out on is the first branch in the .saveas block.
Code:
Private Const csSharePointSaveAs = "\sharepoint-us.mycompany.comsitesfinance adminlah blahlah-blahCustomer Publication Tracking.xls"
Private Sub PublishXLtoMOSS()
Dim objXL As Excel.Application 'Object
Dim wb As Excel.workbook 'Object
Dim ws As Excel.Worksheet 'Object
Dim rs As DAO.Recordset
Dim i As Long
'Set objXL = CreateObject("Excel.Application")
[Code] ...........
View 1 Replies
View Related
Feb 18, 2013
I was asked to create a userform for the use of our whole team which would enable them to select a name from a dropdown list and then it would display their business contact details in the rest of the form.
I'm running Office 2010 and Windows XP server/enterprise edition (not quite sure which). After some research I've managed to use VBA code to open the userform while hiding the workbook, then when the close button is clicked it closes the user form and it's associated workbook using the code below.
Code:
' ThisWorkbook module
Private Sub Workbook_Open()
Application.Visible = False
UserForm1.Show
End Sub
'UserForm module
Private Sub CommandButton1_Click()
Unload UserForm1
ThisWorkbook.Close (False)
End Sub
This works great when the excel file is accessed directly. However when the excel file is accessed via the link on my teams Sharepoint site it doesn't. When the Userform is closed the underlying workbook and data is then displayed which then has to be closed separately.
View 2 Replies
View Related
Nov 25, 2008
If data in A1, Sheet 2 = to A1, Sheet 1, then B1, Sheet 2= to B1 Sheet 1, otherwise Nil.
This will be put in Column B1 of Sheet 2 ....
View 12 Replies
View Related
Aug 18, 2014
I'm developing a Java app that calls a Macro. Is there anyway how to connect to HSQL DB table and insert data in it with VBA code ?
View 4 Replies
View Related
Nov 9, 2009
I have 14 ranges like this on the same spreadsheet and would like to print all of them based on the result in the target cells for the range of each to print. I currently have a macro button to print each range as you can see. Is it possible to connect these? What I have tried is cutting the End Sub and then the Dim r As Long but get errors.
View 4 Replies
View Related
May 2, 2007
I have 1 row of user entry cells (A1:Z1). To keep it simple, let’s say A1 is always 0 and Z1 is always 100.
The user can enter any positive number in any of the cells. For Case 1, let’s say ‘20’ in M1.
I want a set of formulas in A2:Z2 that fills in the values with straightlined values from 0 to 20 from A2:L2 and 20 to 100 in N2:Z2.
For Case 2, the user enters ‘10’ in M1 and ‘60’ in T1 and the formulas in A2:Z2 fill in the appropriate values that connect the empty cells (i.e., 0 to 10, then 10 to 60, then 60 to 100).
Basically, a user enters 1 or more numbers in 1 row, and the second row fills in all the non-entered cells with #s that are straightlined, thus ‘connecting the dots’.
I think a bunch of nested IF statements might work, but it’ll be hairy and nested IF statements are a resource hog. Any better ideas? I’m think maybe some INDEX and MATCH functions.
View 9 Replies
View Related
Dec 3, 2009
I need to connect my two sheets using the value in a column...
Is it possible in Excel?
Actually in one sheet i have only the empno number and his performance report.
(in tabular form)
but in other sheet i have full information about that employee, So I wanna click on any empno and jump to other sheet to see details about that employee...
I cannot use hyper link as i have 50,000 employees and it will be very tedious to hyperlink all of them one by one...
View 9 Replies
View Related
Aug 15, 2013
I wanted to know whether this can be done in excel-- i.e connecting image with a alphabet .
What I want is , when I click on a alphabet on my keyboard , a particular preadjusted image(by the user) must appear . Is it possible??
Is there any way I can create this program in excel.?
View 14 Replies
View Related
Jan 6, 2010
I have a data and it has been plotted into a graph. Here's what i have: I have 2 points, point A and point B with caps, 1.00 and 2.00 respectively. So now i need to draw 3 parallel lines, i.e from the caps and the main point itself. How do i do that? Manually will be of course selecting a line from the shapes. Any suggestions?
View 14 Replies
View Related
Aug 13, 2012
I need to connect to an Oracle 10g database using vba. Google has loads of snipets of code but I don't seem to be able to get it to quite work correctly.
This is what I have:
Code:
Sub ADOExcelSQLServer()
Dim Cn As ADODB.Connection
Dim Server_Name As String
Dim Database_Name As String
Dim User_ID As String
Dim Password As String
Dim SQLStr As String
[code]....
When the connection attempts to open (red code) I get the following error:
Run-time error '-2147467259 (80004005)':
[Microsoft][ODBC Driver Manager] Driver's SQLSetConnectAttr failed
My best guess is that I do not have the correct drivers installed... If this is the case which drivers do I need and where can you get them from?
View 1 Replies
View Related
Jun 7, 2007
I would like to do a sort of lookup function, but am having trouble.
What I would like to do is:
In one column, list all financial years (ie 2006-07, 2007-08 etc - listed in cells A2 - A10). In a particular cell, I will have a date, lets say cell B2. Then in cell B3, I would like to know which financial year that this date in B2 fits in. So for example, if the date was 10/10/2007, it would return the following "2007-08".
View 9 Replies
View Related
Apr 8, 2014
I have the following formula to calculate the angle a connecting rod of a piston should be
P3 is the length of the crank
P4 is the length of the connecting rod
and K42 is the angle of the crank
=PI()-ASIN(P3*SIN(K42)/P4)
Whats the purpose of taking pie away at the start?
View 5 Replies
View Related
Jun 16, 2014
I want to make simple Combobox with two dropdown lists throwing me back cells values, but I never made that so I've got a little difficulties.
The sample combobox should look like this:
Clipboard01.png
For the left Dropdown list there's defined name of the range "ITEM1", and for the second - range named "ITEM2".
After user will choose something I want to have numbers of the items selected from the lists in cells "A1" and "A2", and run a macro after clicking OK. How can I do this?
View 1 Replies
View Related
Sep 3, 2013
I have a table and I need it to filter on the basis of a value entered in a different cell - but i want it to show all rows greater than and equal to that specific value.
Sub Testmacro()
filtercriteria = Range("H3").Value
Sheets("Report 1").ListObjects("Table1").Range.AutoFilter _
Field:=4, Criteria1:=">=filtercriteria"
End Sub
View 1 Replies
View Related
Feb 7, 2014
I have working macro but I need to share it with other people.
I have saved it as a xlam file.
I would like to make modification to my vba code or Excel settings such that the add-in become available as a click-able button on the Ribbon.
View 2 Replies
View Related
Jan 11, 2008
I have an access database: 2007.mdb
I have an access table: All Data - Pre07
I have an excel worksheet which connects to 2007.mdb and selects data from table All Data - Pre07 using a 'user defined' filter via an input box. This filter sets the group which is one of the fields on the All Data - Pre07 table.
The code which performs the above function is shown below.
I wondered whether it was possible to perform a Count function in vba (rather than a Select Function) which will just count the data in access and then return the count rather than importing all the data.
For example, I would like to count the number of rows in the Access table - All Data - Pre07 which have a group = 'AA' and then return the count to WorkSheet 'AA'.
Is it also possible to perform multiple count functions in one connection or do I need to reconnect (i.e. write the code again).
Application.ScreenUpdating = False
Dim mygroup As String
Dim datinput As String
mygroup = InputBox("Enter Group", "Please Enter", "AA")
datinput = "'" & mygroup & "'"
Sheets("AA").Select
With ActiveSheet.QueryTables.Add(Connection:=Array(Array( _
"ODBC;DSN=MS Access Database;DBQ=C:2007.mdb;DefaultDir=C:;DriverId=281;FIL=MS Access;MaxBufferSize=2048;PageTimeout=5;" _
)), Destination:=Range("A1"))
.CommandText = Array( _ ................
View 9 Replies
View Related
Aug 24, 2006
I have the following code, saving created files to a local network. I need to change the save location to an FTP site (with username and password). Is this possible/How do I do this?
Sub Filter()
Dim name As Object
Application. ScreenUpdating = False
Application.DisplayAlerts = False
ActiveSheet. Unprotect Password:="letmein"
MYCOUNTER = 100
View 9 Replies
View Related
Sep 25, 2011
I am using a spreadsheet for a weekly football league. I would like "Sheet 1" to have the picks for the current week with the point total at the bottom for each team (I have that set up), but on a different "Sheet" I would like to keep a running total for each team. I know how to creat a formula for the total points, but how do I get that formula to attach to the corresponding column on the same row.
EX:
A B
"Team Name" "Formula for total points"
I would like A and B to stay connected when I auto arrange them in numerical order.
View 1 Replies
View Related
May 4, 2007
I'm working on a spreadsheet that will connect to a firebird DB (Programmed using VBA and ADO intially).. The spreadsheet itself will reside on a server in a shared folder..
When a user opens the file up, I'd like for the spreadsheet to use the servers ODBC connection, and not the client machine.. This way we don't have to install the Firebird ODBC driver on each client workstation..
View 9 Replies
View Related
Jan 7, 2013
I have a master workbook called "BoM count" which I would like to have capture all the materials used on site. This info has already been captured on the workbook "Survey of materials used on site" (There are losts of these, 1 per site)
I want to open the survey within the BoM count workbook and pull out the relivant numbers of materials used and also list the site ID to show that, that survey has been completed.
View 1 Replies
View Related
Mar 15, 2012
I have tried to search these forms for some information on how to have VBA code check a file saved in an FTP site. I am hoping to use this file to contain version information where the workbook code will compare the version of the workbook to the version log saved in a text file on an FTP server. If they are different, I would like a pop up window to appear that notifies the user that there is a new file available, and perhaps a prompt to save that new file to the user's computer (it does not need to update and replace current workbook).
Is it possible to have VBA check an FTP site for this if I put the username and password in the code?
View 1 Replies
View Related
May 8, 2014
I have the vacation sheet so I can track my vacation hours used and available. The last thing I need is a way to count the number of days I'm not at my site. It doesn't matter why (Training, Holiday, Travel or Vacation) I need to track total days of site.
View 4 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
Feb 11, 2009
Is it possible to open a file on an FTP site using VBA?
I've tried using the code below but it asks for a username and password even though it is in the code.
Workbooks.OpenText Filename:="ftp://username:password@ipaddress/ste1.txt", Origin:=xlWindows, StartRow:=1, DataType:=xlFixedWidth, FieldInfo:=Array(0, 1)
View 9 Replies
View Related
