I have a series of external database connections that I run in Excel, connecting to a Server based DB and running some SELECT statements. (ingres). We may have up to dozen of client machines accessing the ingres DB at any time. All is good at this point but when excel goes to INSERT some calculated data back to the main database, it appears that my Excel spreadsheet connection is locking the database when it connects, keeping other people from using their client connections.
first question: Does anyone know if the connection excel uses to run sql statements via external database connection will indeed prevent other users from connecting to the main database?
second question: if so, is there a setting or something that can make excel more "passive"?
Can a group of cells or more be locked once they reached or passed a certain time frame and date that follows your PC clock, and can it be set to a time that you decide on in a macro. The other question, Can this lockout macro clock that was set to a pacific time frame can it stop people from altering their PC clock in anyway for e.g.; 'I tried to turn back the time but I got this message - "This cell is Locked due to your lateness".
I've got a little program which uses webqueries to import data from anywhere between10 and 200 web pages. It's working pretty well, but I need a way to clear out the residual connections via the code.
The part of the macro which imports the data was simply recorded and very slightly modified. I've included it below... but what I'd like to do once the data has been imported is delete the connection.
I have a workbook which uses data connections to our sage accounts product. Everytime i open the sheet i get the "Data Connections have been disabled" security message, and need to go in and enable them. Is there any way of enabling the data connections by default? I only want to do it for this one workbook - i still want to be prompted when i open other workbooks.
From within VBA I need to open a database connection and issue a select and store the results in my spreadsheet. The issue is that I only JUST learned that you can do this and I dont know where I can look for instructions. Incase it matters, I am trying to connect to a Sybase DB. I have used perl, c# and a little bit of Java to do this but never from with in Basic (let alone basic within excel!)
I currently have a spreadsheet that has data connections to a file on a shared drive. On opening the file I have it update the data connections so that the data needed is always correct. The file I use is to be distributed out to work colleagues to use and on testing it works really well apart from if you have the file open and somebody tried to open the original file where the data is pulled from for the connection. It says it currently in use by 'another user'.
Is there any code to add to workbook_open that will close/disable the data connection links once the data has been updated? The code I currently have is this:
VB: Private Sub Workbook_Open() ActiveWorkbook.RefreshAll Application.Wait Now + TimeValue("00:00:02") Userform1.Show End If End Sub
What this basically does is gives it time to refresh the data connections and show a progress userform. The only thing is the connections stay live and I don't want that as the connection file is then locked. I'm hoping its something simple like ActiveWorkbook.CloseDataConnection added in after the userform has been shown (or something along them lines ). Also the data connections would have to be able to reconnect on open so that they can refresh again.
I have a workbook with multiple worksheets and over 25 Data Connections using MS Query to connect to different tables in an SQL Server database.
The problem is that the SQL Server database is now being moved to a different server. The database name and the schema of the tables will be unchanged. Only the server name needs to be changed from XXXXX to YYYYY.
I have been unable to find a way to successfully edit the data connections in Excel 2003. A web search produced one potential option. However, that did not work for me very well. Thought the code is designed to cycle through all tablequeries and pivotqueries, the change is only made to one of the data connections. Here is the option I tried:
http://support.microsoft.com/kb/816562
Note that it is very easy to edit data connections in Excel 2007. So I also tried opening the .xls file in Excel 2007. I used the Data Connections button under the Data tab in the ribbon and was successful in editing the connections to point to the new server. However, the graphs used to display the queried data behave weird. The "format axis" settings and chart sizes change. In general, default display settings for charts seems to differ between Excel 2003 and 2007.
I used Excel 2007 to only edit the data connections and saved the workbook in the original .xls format
My issue is we have a remote office, using an access DB which links to an Excel file. We sometimes send the files to other people to make changes, but the link is then broken (it was a network location).
You can just change it manually, or accept the many prompts that come up, but it needs doing at each end, each time. I thought I'd do a routine to change the connection paths, this is what I have...
Code: Sub alan() Dim i As Long Dim cnt As Long Dim modtext As String, modfile As String Dim modrange As String, OldPath As String, NewPath As String
I added the sourcedatafile part to the routine afterwards, because the connection string part worked fine. But I now get Run-time error '1004' Application-defined or object-defined error...
While making my first foray into defining external connections (to an Oracle database) I ended up with an "Existing Connection" that reports "File could not be opened. It is either damaged or the file format is not valid. Try opening the application that created it and saving to a new file." Followed by "Unable to open or read this connection file. Either the file has been damaged or the file format is not valid."
Ok, fine. So how do I delete it? There seems to be no option for that in the "Existing Connections" dialog. A search for a *.odc or *.udcx file doesn't turn up anything with the name of this connection. A search of the registry doesn't turn up anything.
I've created several access databases to which I've connected my excel files. However I've either deleted/moved these databases but my connections are still showing in Data -> Existing Connections tabs.
I've tried going to C:Program FilesMicrosoft OfficeOffice14QUERIES but no such queries exist.
The main objective of the code is to create different workbooks that are going to be send to different people(with only their data).
I am using the filter to delete the data that I don't need and then create a new workbook, save it and close it. This has been working fine.
The issue is that I have two slicers in the 'DistMenu' sheet. When I open again the newest workbook, they slicers are disconnected to the pivot tables. This can be fix manually by selecting the slicers --> PivotTables Connections etc
I use the macro recording to get a code and work from there, but is giving me error '1004' "Unable to get the PivotTables property of the Worksheet class".
The main objective is to create a, UDF with 2 parameters (Currency, Date) in order to display currency exchange rate based on the currency and date. Let's say I want to find an exchange rate between USD/ EUR, then I would have a formula something like "=FX(EUR, 12/31/2011)". All currencies will be against USD.
The trick is that it will have to be created as Add-On so that it can be used in any Excel spreadsheets. The second trick is that I cannot have VBA to make any internal connections to other workbooks or applications.
So the only thing that I can come up with would be to write a VBA code that would include all values and currency rates in it. The structure that I have in mind would be something like:
Code: Function Fx(Vl As String, Dte As Date) Dim Rte As String If Vl = "EUR" And Dte = "12/31/2011" Then Rte = 0.7723 ElseIf Vl = "EUR" And Dte = "12/31/2010" Then Rte = 0.7546
[code]....
The above code does not work!There will be at least twenty different currencies and at least 15 periods.
I'm trying to use vba to create an offline backup file for a sheet that is linked to an access database. the code I have is simple and worked for a file that is not linked to any database.
Now that I insert it into the actual file, which is linked to the database I get an error. I also tried to move the display alert up, before the select instructions, but also this does not work. this is not what I wanted since I want this file not to possibly connect to any database.
I have an Excel Workbook with Showcase Queries embedded into it.
Upon a manual open, the user is prompted to either refresh data or keep data since last save, the user then has to enter a username and password to allow the query to access the database.
Using the code below to open the workbook, no such prompts are given. Is the code overriding the prompts and automatically refreshing the data without password? [Given nature of data, visual validation of refresh is not possible]
If not, how can we code a data refresh through showcase query with user/pw req's?
I've tried to remove connections after saves and have been unsuccessful.
HTML Code: ' Delete Additional Connections ' If ActiveWorkbook.Connections.Count > 0 Then ' For i = 1 To ActiveWorkbook.Connections.Count ' ActiveWorkbook.Connections.Item(1).Delete ' Next i ' Else ' MsgBox ActiveWorkbook.Connections.Count ' ' End If
HTML Code: ' Remove named ranges For Each nm In ActiveWorkbook.Names nm.Delete Next nm
I've tried both of these and the Excel file is still attached with XML to the query table. I get an error message saying the format is different then if you click okay it shows its connected to the database still. Is there an aggressive command that will severe all connections before saving? What commands to run to stop this connection after save to another workbook?
i have two workbooks that use a identical worksheet which is called products (this sheet contains rows of over 19000 products ). The problem is i cannot merge the two workbooks together because they are just too big in size.
Also these workbooks have similar userforms and macros that rely on this product databases (worksheets).
My problem is when i add a new product to of one product databases i have to also add it to the second product database again. Is there a way where i can update both worksheets together without opening both workbooks.
I have three userforms that rely on this worksheet from both workbooks:
frmNewProduct - To add a new product to the worksheet.
frmProductUpdate - To edit, replace or delete products in the worksheet.
frmSearch - To search for products inside the worksheet.
I have also got other define names, worksheets and formulas that rely on these product database worksheets.
I'm trying to connect to SAP R/3, and I think I'm not sure which references (Tools --> References) I'm supposed have checked.
All I want to do for now is the following:
Dim sapConn As Object Set sapConn = CreateObject("SAP. Functions")
Unfortunately, I am getting a "Run-time error '-2147024770 (8007007e)': Automation error. The specified module could not be found."
I have read somewhere random on the internet that I should add "SAP:Remote Function Call: COM Support 1.0 Type Library" which links to the SAPGui Provided library librfc32.dll. However, this alone doesn't seem to work...
I was messing around with data connections and importing stuff from a network in real time. Cool stuff. But everytime I open ANY excel worksheet on this computer, it tells me "file cannot be found" and refers to one of the first files I used for this. this happens no matter what workbook I am opening.
This file connects to a weather API. The laptop I am using connects through a VPN connection. For some reason whenever I am on the VPN if I try to refresh the information I will get an error and the macro will fault out. Other than that the macro works perfectly fine.
Attached is the file in question : Weather Forecast Spreadsheet Draft V12.xlsm
When debugging the error goes to:
How can I place in an error management instead of the code just getting blocked out.
I have a VBA application that uses data from a sheet in the excel workbook. It accesses this data with an ADO connection. On workbook open I open this connection--I declare it as public variable so I can continue to use it throughout the session. My problem is that the connection really doesn't ever dissappear. I have the problem that once I close the workbook, the project still exists in the VB Editor. How do I get rid of the connection? I am stuck because I declared it as public? I want to keep it open, rather than opening every time I want to use it, because it is faster to keep the connection open.
My code is below.
Option Explicit 'requires variable declaration 'declare global connection Public cn As ADODB.Connection
Sub OpenDBConnection() 'open db connection 'this happens on workbook open If cn Is Nothing Then Set cn = New ADODB.Connection With cn .Provider = "Microsoft.Jet.OLEDB.4.0" .ConnectionString = "Data Source=" & WCHSBook.FullName & ";" & _ "Extended Properties=""Excel 8.0;HDR=Yes;""" .Open End With End If End Sub
select [C1] from [values$] It works, but my problem is to write this values. Why? Cause i work with my workbook open (not that from connection) and i have something like 55,000 rows to change value. A complex range:
D2,D11:D12,D14:D20,D32,D42,D52,D62,D72,D82,D92,D101:D102,D104:D112... I have a loop for each cell in this range, but this process is very slow.
Imagine:
For Each rng In rng rng.Value = objRecordset(0) objRecordset.MoveNext Next rng 55,000? So slow...
I'm having problems with date parameter for a web connection. I can provide the parameter and type the parameter via message box ... but when I want to reference a cell the query errors out.