Edit Web Query - Get Data From Web
May 10, 2014
i have a worksheet that gets data from web - its automated, but the website goes through a tunnel - with security - requires username and password
the Query Runs ok and smooth but i have to login manually by right clicking on a table where the query is and selecting "Edit Query" so i can login, excel vba doesnt save passwords for that part...
one way i find it possible to automate that part too would be to use maybe like the sendkeys statement to pop up the context menu from right clicking the mouse button, or like the options button on the keyboard.
I found this one:
[Code] .....
It works but the context menu comes exactly from where the current mouse position is..
I needed it to do the right click on a specific range in the worksheet (where the web query is)..
View 8 Replies
ADVERTISEMENT
Apr 25, 2006
I created an Ms Excel Database Query to bring in data from MS Access. (versions 2002 of MS Excel and Ms Access). The query works fine initially. I can right click, choose Edit Query and change my criteria. Results are returned almost instantly.
My problem is that, once I save the workbook, or autosave happens, I get an error when I right-click to Edit Query: This query cannot be edited by the Query Wizard..
View 2 Replies
View Related
Jan 30, 2009
I am using the following code to run a Stored Procedure Query. It works perfect bringing back 4 columns of data. But is it possible to make it so that it only brings back 2 columns of data. I need the 2nd and 4th, which are named "DateOf" and "Gen", respectively. Someway to include a select command?
I posted here, without luck: http://www.mrexcel.com/forum/showthread.php?t=367809
View 2 Replies
View Related
Apr 11, 2012
I have recently installed excel 2007 how do you edit and ODBC query from the spreadsheet. In 2003 you right clicked on a cell that opened a dropdown where you selected edit query.
View 1 Replies
View Related
Sep 6, 2008
I have a csv file on another drive on the network that i need to query. I believe that ms query would be the best way. I know that a DSN needs to be setup but this macro will be used by various users who wont know how to do that. thus I would like to create one via VBA every time the task needs to be run.
I haven't a clue how to do this and i need it to be explained to me in general terms with words of one syllable!
View 9 Replies
View Related
May 26, 2008
I get a report that I have to manually edit everyday. I've done some VBA, but not sure where to start with this one.
The attachment has two sheets, first is what I get and the second is what I want. I'll explain the process i have to go thru now and maybe I can get some help to automate it.
First
-Add the tech number to the first column for each job for each tech.
7988 us the first tech number.
-Then delete every row that isn't a job.
-Add a column with the install date.(report data near the top.)
-Add a column and populate it with the system, which is the sheet name.
Thats why its example.
View 14 Replies
View Related
Oct 2, 2008
I have a worksheet, attached, that is a supplier response to a purchase order. Column E contains the original qty ordered of each part number, Column H contains the original price. Now, I've created a quick macro, (in the workbook), that copies the original values to the confirmed values, and the formulas in columns G and J calculate the qty backordered, or flags the part as a price change.
After copying the original PO values to the confirmed values, the supplier would need to go in and make whatever changes are necessary. For example, we ordered 3 and they shipped 0 of a certain part, or the price has changed.
What I would like to do is create a macro that pops up an input box where the supplier enters the Keystone part number, (column A), then enters the new quantity or new price, and those items are automatically changed on the response form. I picture it in the form of 3 input boxes, in the first box the buyer enters a part number. A second box pops up asking if it's a qty change or price change. They would enter the value in a 3rd box, and that value would automatically be changed on the response.
View 5 Replies
View Related
Dec 14, 2008
I have a userform that I have created to enter new part information to a spreadsheet but I am wanting to also use the userform to edit exsisting part information. My spreadsheet consist of part #, name of part, description of part, date manufactured, and date sold (Column A-E). My question is can a userform be used to edit information that is already present on the spreadsheet? I am looking to edit any entries that do not have a date in the date sold field.
View 3 Replies
View Related
Jul 1, 2009
Depending on the attached workbook,
How can I edit the employee's Name and badge number thru a userform?
What should we depend on to save the employee's name before changing it, in order to use it as a find key?
View 14 Replies
View Related
Jan 14, 2010
I have a cell that uses list type data validation. I need to be able to update the list of allowable values via a macro and would greatly prefer not using a range in excel.
I've been able to create the string that I want as the formula but cannot change the formula. I'm not very familiar with this aspect of excel macros (data validation)
View 2 Replies
View Related
Nov 15, 2011
I am looking to pull out an outcome based on the user inputting two criteria, below is an example:
A
B
C
D
[Code].....
So if the user inputted P1 into cell H2 and inputted 9 in I2, J2 would show Pass, but if they inputted P5 into cell H2 and inputted 9 in I2, J2 would show Fail.
View 1 Replies
View Related
Jun 12, 2013
I'm coming along nicely with my user form. My last issue was trying to get it to search/recall data from the "Data" tab. This works perfectly when only 1 record is found, and if multiple line items are found, a message box pops up and tells me how many records are found that meet the criteria. However, I am now struggling to get the code to work properly to past the details of those records into the list box, so that the user can select which record they want to edit.
I get a Run-Time Error '1004' Application-Defined or Object-Defined error and it points to the section in red:
Code:
Sub FindAll()
Dim strFind As String 'what to find
Dim rFilter As Range 'range to search
Set rFilter = Worksheets("Data").Range("E3", Range("h65536").End(xlUp))
Set Rng = Worksheets("Data").Range("E3", Range("e65536").End(xlUp))
strFind = Me.cbosearch.Value
[Code] ...........
View 2 Replies
View Related
Mar 2, 2013
I work in a small team of 5 people, but each of us is very busy and get emails from our manager asking us to complete different tasks. I have made a userform in excel for our manager to create the tasks in and put all the details in sheet1. This creates each separate task on a separate row. Now I want each of my colleagues to log into the sheet and be able to retrieve a task with the same userform. I am giving each task it's own ID, so I want to be able to search by this and the userform will be populated with the task details. I also have combobox's with the status of each task (Completed, In progress etc.) I want to be able to change these in the userform too.
VB:
Private Sub UserForm_Click()
Private Sub CommandButton1_Click()
Dim LastRow As Object
Set LastRow = Sheet1.Range("b65536").End(xlUp)
[Code].....
View 1 Replies
View Related
Feb 14, 2014
I am trying to edit the data in the actual pivots rather than editing in the data source. i know there is vba code to enable pivot table editiing. how to enable this
View 14 Replies
View Related
Feb 17, 2009
I have several Excel files that I am about to change some data in, so I need to create an macro to speed it up. All sheets should be updated besides the last one.
The number of sheets differs between the files. The data that should be changed is in column C, starting at cell C10. But the number of rows differs. If the data in the cell begins with a Q, the Q should be removed, and if it doesn't begins with Q it should be skipped.
View 3 Replies
View Related
Apr 22, 2009
I want to be able to select and edit a range of data on a userform. I am trying to pull the information that matches a cell range (Named as Action) into the respective textboxes. But I have fallen at the first hurdle and can't even get that working.
View 2 Replies
View Related
May 2, 2009
I have created a form in VBA using txt boxes and cmd's. I want the abitility to edit the existing data on my excel worksheet using the form. Currently, I can add data, but not search through the existing data on the form.
I have been using the excel tips and it has been great, but I am stumped at this point. This is my first time to every post a message.
View 4 Replies
View Related
Jan 4, 2012
Any way to edit data that has been populated in a Userform Listbox? I am trying to create a userform which has a multicolumn (3 columns) listbox and data being populated from Sheet1!A:C. I am thinking that there would be an Edit button where when clicked the data for the row that is selected in the listbox is shown in 3 textboxes (one for each cell on the respective row) on the same form which can be edited. When the user clicks Save. The Listbox would be updated with new values (e.g. write over the values in Sheet1.
View 9 Replies
View Related
Jun 19, 2012
I am using the below code to pull information from multiple sheets into one master sheet. Currently, it is funcitoning fine with one slight problem. In each of the sheets I have formulas copied down until row 500. However, some of the sheets may only have formulas populated up until row 50 for example. edit the below macro to where I am only pulling in the rows that have populated formulas?
Code:
Sub Consolidate()
'Author: Jerry Beaucaire'
'Date: 9/15/2009 (2007 compatible) (updated 4/29/2011)
[Code]....
View 1 Replies
View Related
May 28, 2009
I have a spreadsheet that summarizes variations on a project. On the "Variations" tab a userform pops up that asks the user to select from 3 options:
1. Create new variation
This launches another userform that allows the user to enter the necessary information and create a variation sheet. This userform updates "VarSummary" and also creates a new sheet for each variation created. I have been able to do all of this so far. The summary sheet "Variations" tab uses the data on the "VarSummary" tab. This is the code I used to add variation
Private Sub cmdadd_Click()
Dim iRow As Long
Dim ws As Worksheet
Set ws = Worksheets("VarSummary")
With ws
iRow = . Cells(.Rows.Count, 2).End(xlUp).Offset(1, 0).Row
'Copy the data to the database
.Cells(iRow, 1).Value = Me.txtVarRecNo.Value
.Cells(iRow, 2).Value = Me.txtDate.Value
.Cells(iRow, 3).Value = Me.txtChg.Value
.Cells(iRow, 4).Value = Me.txtSrcRef.Value
.Cells(iRow, 5).Value = Me.cbstatus.Value
.Cells(iRow, 6).Value = Me.DirQty.Value
.Cells(iRow, 7).Value = Me.DirVal.Value............................
View 8 Replies
View Related
Mar 30, 2008
A.) As a user is entering data into a userform two specific values need to be checked in the existing data sheet.
Textbox1 ( date)
Combobox (operation name) {4 tabs on from textbox1)
They relate to the data stored in columns 'A' and 'E' respectivly.
What I'd like is, as the user exits combobox4 to check if the values already exist. If they do; fill the coresponding textboxes with those values and allow the user to make any amendments, then have it SAVE to the SAME row, would a record number be necessary to accomplish this?
B.) For a future development, Im thinking of applying the same principle to a different project, where 3 values need checking.
Textbox9000 (date) Column 'A'
ComboBox1002 (Staff member) Column 'B'
Combobox1003 (operation name) Column 'C'
Possible complications are that below combobox1003 there will be 12 other comboboxes(a value title, from column header) with associated textboxes alongside(value previously entered by user).
There will be 22 possible values for the comboboxes(the values will be the same column headers) The user will only ever have the choice of making 12 entries though.
Does any one have any spare code laying round for this one..
View 9 Replies
View Related
Aug 9, 2012
I have created an example of a "Waiting List" for the college, everything I had done seemed to be working before I left for my holiday (apart from the search facility as I was still doing some problem solving, compiling and coding for it).
The Idea is to : Open Excel Document and be greeted by the "Hello" Userform
[Problem #1: This does not automatically show since I have returned from holiday, though there seemed to be no problem with it prior my escape.]Choose from three options;"Save and Exit" saves any changes on/in the database and closes the document"Insert Data" opens "Userform1" to allow users to enter a new row under the exsisting rows of data.
[Problem #2: The data in the sheet was all entered using this feature, but yet again since I have returned from holiday it keeps adding the data only on row 2]
[Problem #3: How to do Auto Formatting i.e. Postcode in Capital Letters]"Search" will open the "Search" userform which I hope to allow users to choose up to three column headings and enter the value to search that column with the parallel textbox value.
[Problem #4: The data which meets the criteria the most is listed into the listbox, if you select a piece of data (double click or "selected") it opens a new userformThe "Edit/Enrol" Userform allows user to Edit, Enrol or archive the row of data.the last Userform I created "Course" is for an admin of the workbook to choose a course and it be auto populated by the information in the worksheet "Code" and create a Mail Merge.
View 9 Replies
View Related
Dec 21, 2012
I have a workbook which contains a list of data on the first worksheet. I need for a macro to copy the name in each row,along with its data in that row and paste it into the corresponding locations on a template located on another worksheet. It would then save the file using the date (col. a) , then the words " Scorecard for" and then the name (col. b), then loop for each row until it reaches the end of the list.
I located this code here here.However I dont know how to modify it to fit my needs. I dont have 2 separate categories for two score cards in column b. It would all run off one scorecard and each row of data.
[Code] .....
View 1 Replies
View Related
Feb 22, 2013
In my work book there is 2 sheet, 1 (sfc)is data sheet where i puts all the data & second (size roll )is for a pivot table, now I am not able to make the update of the pivot table, I want to update the pivot table when ever i enter or edit any data of data sheet .
See the attached file , in sfc sheet Column W,X,Y,Z contain the data for pivot table on "size roll sheet "
cuting chart.xlsx
View 3 Replies
View Related
May 22, 2014
I noticed that when I click on a cell in my Pivot Table a new worksheet pops up with the requested information (essentially a filtered version of my data source depending on what cell I clicked) is there a way for that information to appear on the same worksheet as my Pivot Table? And if so can I also edit what parts of the data source appear?
View 1 Replies
View Related
Oct 19, 2012
I created a slick little excel sheet with the data coming in automatically via Access query. It has been working fine for months. Now all of the sudden there are a bunch of cells with missing data. The weird part is it's not as if whole columns are missing data, more like 90% missing. When I go to Access and run the query all cells are populated as the should be. There have been no changes to the query at all during this time.
btw...I am running Office 2010
View 2 Replies
View Related
May 31, 2014
A web query I am trying to use to bring an online data table into my worksheet is broken. Now, instead of returning the data table nicely into my worksheet, it imports the code of the webpage, instead, and turns my worksheet into a mess.
The query used to work but there was recently an "upgrade" to the program that populates the web table and the query no longer works and just returns the code for the page, instead. I can see the html code for the table in all of the code it returns but I really need the table to import cleanly into excel.
I can't talk to the people who changed the web program.
View 3 Replies
View Related
Jul 9, 2014
An external data web query points to a web site that offers a foreign exchange rate calculator. In my browser, I selected the specific currency pair I need and used the resulting url in the web query. The "New Web Query" pane resolves the url correctly and allows me to select the table data I want (the little yellow arrow turns to a green check mark). However, when I hit "Import" and select the target cell, the only data returned is the parameters from the url and an error msg (This web query returned no data...). How do I get the query to pass the parameters to the website correctly?
View 3 Replies
View Related
May 18, 2012
I'm working on an Excel based product. As part of the security process I would like to require the user to enter a product id key into excel. Then the spreadsheet would compare the entered value to some kind of online database to verify the id key is valid. Finally, the spreadsheet would update the online database so that the id key is no longer functional. How easy is this to do?
I know verifying that the id key is valid is fairly simple.
Here is my biggest issue: How to get Excel to update the database and of course what form would this database take. Maybe its a table on a webpage, or google docs. Again the issue is how would I be able to update the table.
View 1 Replies
View Related
Sep 26, 2012
I have created a simple userform that is linked to a button on sheet1. When the data in the userform is submitted, I set it up so it goes into a database under sheet2. The problem is, I don't want anyone modifying the entries in the database. Usually I would protect the sheet with a password, but when I do that, there is an error when the userform is submitted. The only thing I can think of is to hide sheet2, which doesn't seem like a great solution.
Is there any way to protect my database from being modified yet still allow the userform to be linked to it?
View 3 Replies
View Related
