I want to write some VBA that retrieves data from a lotus notes database into excel. I will then have some other code that manipulates the data for the user. I have managed to do this with MS Query manually. However when I start to put into VBA my problems start. As I do not know Sql I use the macro record function. This worksso far so good. The problems seem to start when I re-run the macro and try to save the workbook. Excel just sits there with the CPU running at 99%. I left the pc for an hour and it was still in the same condition with the status bar showing save.
Sub Macro4()
With ActiveSheet.QueryTables.Add(Connection:= Array(Array( _
"ODBC;DSN=premax;Database=Premaxse000273.nsf;Server=local;UserName=John Cross/bsp;EncryptMaxSubquery=20;MaxStmtLen=4096;MaxRels=20;M" _
), Array( _
"axVarcharLen=1024;KeepTempIdx=1;MaxLongVarcharLen=1024;ShowImplicitFlds=0;MapSpecialChars=1;ThreadTimeout=60;" _ .............................
I am not sure how to setup the initial connection to the datbase from excel and was wondering what would be the best way to go about things. Is it possible to run the query stored in the database and pass it a value or would I be better off just coding it into a sqlstring within the macro.
Here is the query that is in the database...when run it asks for the "Status?" variable.
SELECT ordernumber, mobilenumber FROM bookings WHERE status = [status?];
So yes, really simple query but yeah just want to know how to pass it that variable from excel or can I just set it up in the actual macro to maybe read the status from a cell?
I have an Excel spreadsheet with a query to an Access table. The query pulls data from 2 columns in a table and pastes it to the spreadsheet. I am trying to update the VBA a little on one sheet and now I get the attached error whenever the code tries to run in Excel. Here is the code with the offending portion separated at the top.
Here in our department we made a pretty elaborate macro that takes a report and sorts them out to 17 different sheets in a one workbook. This Macro pulls a file from a specific location on our server and then opens the CSV sorts it out color codes all the important information and saves it back onto the server under you specific initials.
They are four PC's along with our Managers laptop that run this Macro daily.
About 3 weeks ago my Managers laptop stops running the Macro completely and hangs in the middle of the whole thing. Eventually crashing Excel.
We try to remove the modules and re-import them back into the personal macro workbork but this does not work. The Macro's did not change and still fully function on the other four desktops to this day.
I uninstall Office on my Managers laptop and reinstall. Import the Modules again and still hangs up in very same spot it did 3 weeks ago.
I've tried to lower the macro security to the lowest level also and I've still had no luck with this laptop. I don't understand. The Macro's function perfectly on other PC's but will not function on this laptop.
I have a Sheet sheet1 and I want to run a macro when the cell D2 in Sheet1 is equal to 10,7,5,and 3. I only want this macro to run when those values are reached the macro then puts the data onto a sheet called wps. The macro is run as a module and is a sub macro.
However, as you can see from the text in bold i'm currently telling the url instead of bringing it from a cell with an url. I want the macro to be able to select the url from a cell.
I'm trying to use this solution, but i keep getting a Run-time erro ´5´: Invalid procedure call or argument.
connstring = Range("GUS8301!B3").Value With ActiveSheet.QueryTables.Add(Connection:=connstring, _ Destination:=Range("A1"))
i have recorded a macro wherein i need to copy paste some data in my database everyday. now the problem is tht the macro doesnot catch the last row.
for instance if i paste data today in A2:A10 tomorrow it should automatically paste data starting from A11.
the range of data tht i copy is not fixed.
This is a part of macro... Range("A1").Select Selection.End(xlDown).Select (This takes me to the last record on that row) is there anyway i can copy the data in next column?
'Range("A11").Select (This will work only for the first time when i run the macro)
but if i use the same one tomorrow Range("A1").Select Selection.End(xlDown).Select This will take me to the last record but.. it will again go to 'Range("A11").Select and then paste records there
I worked on this macro for about 2 hours, with only partial success.
I have credit card data (will attached next post) sorted by tender type. I recorded teh subtotal feature, after selecting the data. I used teh go to, special, last cell, feature to get the end cell for selection, and also set the print area to this range. I did it in relative mode.
the macro worked, but when I also tried to insert three rows above the "grand total" cell, then insert a sub total of just master card adn visa, it in one of these added rows didn't work upon playback.
also - Can I lock the position of my macro buttons? IN properties it's set to locked but they still move when teh data changes. Can i put them on teh tool bars?
So I have some data that I'd like to extract from some financial sites that I'm a member of (one in particular is investors.com). I try and pull stock info from the site, but I have to manually initiate a new web query, go to the website, login, then cancel the web query for the macro on my spreadsheet to start pulling information. What I'd like to do is have two cells referencing my user id and password, and then click a button that will goto the website from excel, login, and allow the extraction of financial info to occur.
I have a Excel database query which of which i import into Sheet 1. On a daily basis I need to edit this query and change a critea field to yersterdays date. Is there a way in which I could run a macro to change this query for yesterdays date without having to manually go into the query?
I have tried to run the macro, however I can only run this if I have a specific date in my code e.g. 11/03/2007 and not a formula to show yesterdays date.
I have an MS Access query that contains a parameter. The parameter is a date field, and I have configured that in the Access query. If I run the query within the MS Access user interface, it prompts me for the paramater value as expected, and runs just fine. However, I want to connect to this query from within Excel as a data source.
I have created a connection to the Access file using ODBC from within Excel. In the MS Query window, I am merely selecting all of the fields resident in the MS Access query, and returning all values. In other words, there is no selection criteria in the MS Query. I have done this many times with Access queries that DO NOT contain a parameter, and everything works fine. However, in this instance, I need to pass a parameter through to MS Access in order for the query to run. At the moment, I get the "Too Few Paramaters...1 expected" error message. This makes sense, because I haven't figured out how to pass the paramater to MS Access.
Is there a way to structure this that does not involve VB code? If so, I'd love to know how. I have tried creating parameters in MS-Query with the same name, but although I get the prompt it doesn't connect with the Access query as the source for the parameter value.
If the solution requires using code, I'm good with VB Code in Excel...is there VB for Excel code that could make this happen?
Failing that, I guess there must be (I've seen a few in my search thus far) Access VB Code that can make this work. I'm very rusty using VB with Access, so this is my least favored solution. However, if this is the only option, keep in mind that I need to pass the paramater ultimately from a user who will initiate the process using Excel.
Need the query parameters which takes the date from the cell into the query. How should I modify my query if it needs to take the date from a cell?? The bold one date should be picked from one of the cell in sheet 2.
My query is this WEB 1 http://fc-web-phl1-101.phl1:8090/gp/...runReport.y=12
I'm trying to get my macro to connect to Infomaker and run a query. I have successfully done this in the past by using the record macro function. But, when I try to record the macro and paste the query syntax from Infomaker into the commandtext box of the connection, I get the "too many line continuations" error.
I've been looking at ways to write the code rather than record, but the syntax for the Infomaker queries doesn't seem to mesh well.
The syntax of the query in Infomaker is (copy/pasted, all "'s are necessary):
I have an access database that has many queries and in order to speed it up I have a Macro in access that runs it in no time, but I need to be able to run the query in access via a trigger in Excel and have come up with the below, but it's not working.
I am trying to write a creative procedure that on workbook open will check the name of the worksheet if it includes the day's date in sheetname & if it doesn't rename the sheet & delete current region from A1, else exit the sub.
Then runs a query on an Access DB to place in the above worksheet.
I need this due to novice XL & DB (8)users.
This is what I have at the moment on a test DB courtesy of J Walkenbach's sample files .....
I am running a Showcase query from inside excel. I want the query to update, drop the data in excel save it and then close. The problem is that the save command is executing before the data is dropped. The Wait command pauses all processes so it won't work. Is there a way to have a minute of filler until the data is dropped so it will save?
I'm trying to get a macro to run when the user presses the save button. I have the following code:
[Code] .......
This code is supposed to apply the TRIM function to every worksheet in the workbook when the user presses save, and the saves the results. I understand the Private Sub bit is the code to have the macro run during the save, but for some reason the macro itself isn't actually running. I'm not getting any error message, its just that the cells which should have the TRIM function applied to them aren't being corrected.
I have a macro that takes about a minute to run. I would like to add a progress bar to appear once the macro starts running. There are no loops in the macro. What are my options?
I have 3 different sheets with a private sub on each all labelled
Private Sub CommandButton1_Click()
The macro's runs fine on each page.
I want to put a macro on a separate sheet that i can run each of those macro's
I did initially copy the original private macro and change the
Private Sub CommandButton1_Click()
to
sub report()
But I couldn't do all 3 in the same manner. i did change each name to something different... the other 2 subs did run, but they didn't do anything except put the massage box on the end saying that "the macro has finished".
I have 7 different workbooks as part of my daily routine.
At present the first workbook is opened via the task scheduler, and the macro is run using the following code
[Code] .....
This then opens the next workbook and closes the current one. The next workbook then runs its macro again from a timed call
[Code] ...........
This repeats for 7 workbooks. The problem is, if a macro overruns - the whole schedule is out of sync when macro's are called depending on the time. What I would like to do, is link each macro by calling for the next one, and closing the current workbook. However, when i have tried to do this, the macro has always stopped when the workbook has been closed - the next macro doesn't run.
Below is the entire code that I am using. It is a simple routine which checks whether a part has started its release process or not, based upon dates. The code works and does what I want.
The problem that I have is that it is very slow, for example it takes 35 seconds to go through 530 lines items. In my (limeted) experiance, based on other VBA doodlings this slow.
I already have a macro made named (PowerPoint) that is saved to the wookbook. I am looking for a way to have this run every 20min. I have tried several differnt things and it did not work. Here is one I found online. I only need it to run when it is open and every 20mins. as long as I have it open.
How do I ask for a confirmation box before running a macro, but only one time. What I mean is some of the macros I use loop and it wouldn't be practical to click on "ok" for each time the macro ran.
So again I just need some code to tack on to the beginning of a couple of my macros that will ask me before they run but only once.
I have the following macro in a worksheet...and it is running very slow. There are other macros in the worksheet and they all run very well. Any ideas by looking at this code why it would be so slow in running?
