Removing Data Connections And / Or Query Tables Before Saving File?
Oct 10, 2013
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'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 am wondering if there is a way to take the charts I have created, and save them on a different worksheet in Excel. The issue I have is that when I erase the data source, the charts lose all their data. Is there a way to save the chart as is without it relying on its data source (ex. So I can remove the data for the chart)?
If I update my data, then refresh the pivot table, old values still show as an option. How can I delete these items? If it matters, my pivot table's name is PivotTable2.
I was wondering if there is a way to write a macro that will take an excel file that I have called Alldata and then have it copy the first two sheets to a new workbook named something else( FullSparameter_0) and then copy the name of the third and fourth sheet into the new FullSparameter_0 workbook and then copy column A-E from sheet dB Alldata.xls and paste it into the FullSparameter_0.xls workbook in the same dB sheet in the same spot. And do the same thing with the Phase Sheet. i know this sounds confusing, but im really new to the VBA stuff.
I just need 15 different files from the original Alldata.xls that will be named FullSparameter_0, FullSparameter_1, .., FullSparameter_14. but each file will have the same two first sheets, Setup Information and Cal Verification, and the same last two sheet names, dB and Phase, but then in the dB and phase sheets, it will copy over the next four columns of data. so in FullSparameter_0 it will have column A-E from sheet dB in Alldata.xls in the dB sheet in FullSparameter_0 (Same with the Phase Sheets). Then in FullSparameter_1 it will have column A and Columns F,G,H and I (for dB and Phase). In fullSparameter_2 it will have column A and then J,K,L and M. Each FullSparameter_X will have the exact same first two sheets and then the other columns for the last two sheets will be pasted into columns A-E in dB and Phase.
I am running code that updates several excel sheets from a database, but quite often other users on the network are already in the sheets, is there a way i can remove the users just before the macro saves the file? Or alternatively saves it to the new filename whether or not someone is in it?
I currently have a tab on my spreadsheet named CSV. I have a macro that when called saves the information on that tab and sends it to a folder saved as a CSV file.
However this tab has a lot of information on and i want it so send it with only the information needed at the time. the data i want to send is in Columns A to AM and has rows down to 7392. I only want it to save the file with the data in the rows that has "Export" in Column AQ ignoring any other rows of data that i dont need it to send
I dont want anything to happen to the CSV tab ie delete lines etc as files will be getting sent regulary from that sheet still but with the data constantly updating in different cells
I have a macro used to save a worksheet, its placed on the DATA sheet. is there a way so that when the user clicks this button each row 12:200 is checked for empty cells in that row (cells B:P). If a row is found the ref number in column A of each row is placed into next available row in Sheet3
For example : A12 = 12345 A13 = 6789 A14 = 9876 A15 = 54321
the DATA sheet is populated with various data in rows 12:15 it is found that B12, L12, E14,F14,P15 all have no data, so the values of A12,A14 and A15 are placed into next row in Sheet3
Result in sheet3
A2= 12345 (valuse of A12 in DATA Sheet) A3= 9876 (value of A14 in DATA Sheet) A4= 54321 (value of A15 in DATA sheet)
Finally a message box to state there is missing data, for example
"there is missing data from refs 12345 , 9876 , 54321" ie the list pasted into sheet
Neither is it because you've turned SU off, and then used 'Select' or 'DoEvents' etc in your code.
When running an update for a QueryTable (once a second), 3 'buttons' (activeX image controls with code behind them) flicker - though I see no reference to selecting and/or screenupdating in my code
Is this purely something about refreshing a querytable that I can't fix?
Is there a subclass or API call I can intercept to stop drawing the images alone?
Code:
Public Sub ActivateConn()
Dim strFilespec As String Dim strConn As String Dim sqlStr As String
Set RecSet = New ADODB.Recordset
[Code] .....
In short, there's no REASON why I couldn't just use the selection_change event to fire the same macros as the buttons but... well... it doesn't look anywhere near as good (I have a thing about making VB apps look as good as the code behind them is smart)
I am trying to export data from Access to an Excel file without saving the file. I have been unable to get my macro to work. Right now when the macro runs it only opens a new Excel workbook but does copy the data into the workbook.
I believe the issue lies in the generated strSQL. I suspect that it has to do with the Status field value equal to IVR Call Needed. Maybe the spaces in the value?
Here is my code.
Code: Private Sub Export() 'Step 1: Declare your variables Dim MyDatabase As DAO.Database
I have a website (National Lacrosse League - 2013 Regular Season - Standings), that I want to pull information off and automatically into Excel.
I have been using web queries on other websites, and they have worked beautifully, however the tables that I want aren't recognised by the web query wizard (no yellow/black arrows). I can import the whole page through a query, however that's not really practical.
Is there a way I can import just specific tables on this site?? I'd like both the 'east' and 'west' tables.
I am currently working on a project that uses Excel to parse a .txt document. Its working quite well for me. It functions by having two worksheets. In the first worksheet I use the " import external data" menu to import my .txt file. In another worksheet I have set up fields that show only the important information from the .txt file and leave the junk behind.
I’m looking to improve the functionality of this by adding a button to automatically clear the data in the first worksheet so that new data can be added quickly.
I have searched the forum and found a couple of threads on "QueryTables". After reading up on those, I have made a simple button with the following code.
After I load a .txt file and parse it using the formulas set up, I copy my needed information and then press this button. The cells clear, and the QueryTables are "reset" (maybe not the right word). Now a different .txt file can be imported and the process starts again.
The problem I am having is that. If there is no "QueryTables.Item(1)" to delete, I get a run time error. (Run-time error '9': Subscript out of range).
Would anyone know how to make my button conditional to having a "QueryTable" active? I.E. If I press it when there is no data loaded it doesn't do anything or give me that error.
How do I set the datatypes and columnwidths as variables? I get invalid procedure call or argument. Will import different text files and set dt and cw based on names.
Dim dt As Variant Dim cw As Variant dt = "2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2" cw = "7, 7, 5, 2, 7, 16, 3, 15, 11, 9, 9, 9, 7, 3, 3" With ActiveSheet.QueryTables.Add(Connection:="TEXT;B:BOMOEBTRfro1.txt", Destination:=Range("A1"))
I'm trying to query a web site and export the data to a single sheet in Excel. I've used the record macro function in Excel (2007) and come up with the following but I want to query over 20 pages from this site and wondered if there was any way in looping this macro to do the hard work for me;
I have a spreadsheet that imports data, manipulates it then deletes 2 of the sheets then saves the file under a different name to the network. Is there any way to save this new worksheet without it storing the macros - so when the user open it, only the data is there and they get no prompt to enable macros?
I don't have Excel 2013 so unfortunately I can't easily create relational data in Excel 2010. I'm looking for a solution to a design problem. I'd like to have 2 tables which I can join with Microsoft Query and run pivot table reports.
My department processes payments, both for internal clients and external clients. My Payments table looks like:
Date | Type (internal/external) | Operation (what type of payment) | Method (internet, mail, etc) | Quantity
Additionally, I have a table for Mail Opening, which looks like:
Date | Employee | Operation | Quantity
My overlapping fields are Date and Operation. Using each table individually, I can get nice pivot table reports. What I'd like to do though is be able to not just see what operations and methods were run each day with what quantities, but also to compare that to how much mail was opened. Employee and Operation is a multi-multi relationship, so when I join by date, I end up getting incorrect numbers because of problems with the data layout. I'm open to changing my data structure, as I know the way it's set up right now isn't great, but I'm having a mental block on how to redesign it. I attached a sample workbook.
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.
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 have numerous spreadsheets that I need to open and unhide a sheet, that has XML data stored in cell A1. What I need to do is copy that data in cell A1 and paste it into a text document and save that as an XML file saved as the XLS workbook name with a date stamp.
I'm running into many issues, the main issue is the saving as current file name, and the formatting of the text/XML file.
Here is my current code, which doesn't reference the current file name and is just very generic. Once I get the saving as file-name correct and the formatting of the xml file correct, I will work on it a bit more.
Code: Sub Test() Dim Rng As Range Dim wb As Workbook Set Rng = Range("A1:A2") Set wb = Workbooks.Add With wb Rng.Copy
I am setting up a "save as" macro that saves a file by replacing another file in a folder. Even though the macro has been recorded by approving the replacement (the prompt appears "the file --- already exists. Do you want to replace the existing file?"), when I run the macro, I am again prompted about replacing the file. Is it possible to avoid the prompt so the file is automatically saved by replacing the named file?
found the following code which works for 1 workbook at a time. I am trying to save 7 workbooks at 1 time. Is it possible?
Sub SaveAsCell() Dim strName As String
On Error Goto InvalidName strName = Sheet1. Range("V77") ActiveWorkbook.SaveAs strName
It will work on the first sheet but none after that. I need to have each workbook saved with the value in cell V77. Also if that is possible, is it possible to change where the file is saved as well?
Right now I have a master workbook that will open the 7 other workbooks, paste data onto several pages in each workbook. I would like the macro to save the workbooks. The workbooks are named: 02 Tuesday, 03 Wednesday, 04 Thursday, 05 Friday, 06 Saturday, 07 Sunday, 08 Monday.
I have an existing application that uses DAO sql queries to populate a form. It has always worked fine. Earlier this week, I password protected the spreadsheet. Now my DAO query fails. I get a run-time error '3161'. Could not decrypt file.
' Const stExtens As String = "Excel 8.0;HDR=Yes;" Const stExtens As String = "Excel 8.0;HDR=Yes;PWD=money16;"
Private Sub Execute_Query(SQL) 'Instantiate the DAO objects. strDb = ThisWorkbook.FullName Set DAO_ws = DBEngine.Workspaces(0) ' code fails on the line below Set DAO_db = DAO_ws.OpenDatabase(strDb, False, True, stExtens) Set DAO_rs = DAO_db.OpenRecordset(SQL, dbOpenForwardOnly) End Sub
I am trying to save my workbook in a specific directory with a specific filename and to incorporate a date field from within the spreadsheet. I have changed the format of the cell so it does not include / as i know this would not work. Saving it is the easy part but adding in the field from the spreadsheet is where im falling over.
In order to get inventory data from our Incode database, I've been creating an output file with the extension .IRP Thinking that excel would not bring in this data from the .IRP file directly, I would then create a txt file of it with notepad. I've recently discovered that I can bring it in directly from the .IRP
The file being created is in this format:
IN Stock Status Report - 6385.IRP
The number portion, 6385 in the name will vary everytime I generate a new report. However the text portion will not.
Currently, I've recorded a macro the bring in the data to my excel spreadsheet.
But it will only bring in data for the set filename.
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!
