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?

View 2 Replies


ADVERTISEMENT

VBA - Save File As New Workbook BUT Without Existing Data Connections

May 23, 2013

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.

Code:
Sheets("Data").Select
Sheets("Data").Copy
Application.DisplayAlerts = False
ActiveWorkbook.SaveAs Filename:=myfilename
Application.DisplayAlerts = True

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.

View 9 Replies View Related

Saving Charts And Removing The Data Source

Jul 13, 2006

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)?

View 2 Replies View Related

Removing Old Data Values From Pivot Tables?

Jul 17, 2012

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.

View 1 Replies View Related

Moving Columns Of Data Into Another File And Saving File With New Filename?

Aug 30, 2013

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.

View 1 Replies View Related

Removing Users When Saving Workbook

Aug 2, 2006

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?

View 4 Replies View Related

Saving And Sending A File With Selected Data Only

Jan 2, 2010

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

View 2 Replies View Related

Check Certain Cells For Data Before Saving File

Jul 2, 2008

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

View 9 Replies View Related

Query Tables And Screen Flickers

May 9, 2012

No, this is not about ScreenUpdating = True/False

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)

View 9 Replies View Related

VBA To Export Query And Open Excel Without Saving

Jan 31, 2014

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

[Code].....

View 1 Replies View Related

Inserting Query Tables In Active Sheet

Feb 26, 2013

When using

VB :ActiveSheet.QueryTables.Add

How would you get the code to overwrite whatever is in the worksheet as opposed to adding new columns?

View 1 Replies View Related

Web Query - Import Just Specific Tables On Site?

Aug 17, 2013

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.

View 8 Replies View Related

Delete Query Tables Macro Code

Aug 14, 2008

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.

Sheets("Sheet1"). Cells.Clear
Sheets("Sheet1").QueryTables.Item(1).Delete

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.

View 4 Replies View Related

Query Tables DataTypes And ColumnWidths Arrays As Variables

Jul 12, 2011

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"))

[Code] ........

View 8 Replies View Related

Excel 2007 :: How To Use Query Tables With Multiple Web Pages

Apr 22, 2014

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;

With ActiveSheet.QueryTables.Add(Connection:= _
"URL;http://www.futwiz.com/en/players", Destination:=Range("$A$1"))
.Name = "players_1"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False

[Code] ........

On the second page the URL changes to;

URL;FIFA 14 Ultimate Team Players - FUTWIZ

and the .Name field changes to;

.Name = "players?page=1_1"

and on the 3rd page I get;

URL;FIFA 14 Ultimate Team Players - FUTWIZ

and..

.Name = "players?page=2"

View 3 Replies View Related

Saving A File Without Saving Macro

Jan 30, 2003

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?

View 6 Replies View Related

Excel 2013 :: Creating Two Tables That Can Reference With Microsoft-query

Jun 28, 2013

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.

View 3 Replies View Related

How To Enable Data Connections

Dec 16, 2011

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.

View 2 Replies View Related

Close Data Connections After Refresh

May 3, 2013

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.

View 5 Replies View Related

Editing Data Connections In 2003

Oct 20, 2009

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

View 3 Replies View Related

VBA To Change Workbook Data Connections

Jun 2, 2014

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

OldPath = "\192.168.1.100MultimediaCommissioning Folder"
NewPath = "C:matts"

[Code] ...

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...

View 3 Replies View Related

External Data - Cleanup Existing Connections

Apr 26, 2013

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.

extdata.jpg

View 1 Replies View Related

Macro To Delete All External Data Connections

Mar 31, 2009

I am trying to write a macro to delete all connections from a very large excel file

I created this file by using a Macro to extract data from a external data source (url's listed on a sheet)

The issue is the 1000+ connections on this file make it next to impossible to operate

I cannot recreate the file

macro to delete all connections in all sheets in a given work book.

[URL]

Macro used :

Sub Dump()
Dim myURL
Sheets("Sheet1").Select
ActiveCell.Offset(1, 0).Select
myURL = Worksheets("Sheet1").Range(ActiveCell.Address).Text
Sheets("Sheet2").Select
Range("A65536").Select

[code].....

View 3 Replies View Related

Saving Cell Contents To XML File Saved As Current Workbook File Name

Dec 12, 2012

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

[code]....

View 4 Replies View Related

VBA For Bypassing Replace File Prompt When Saving As A File In Macro

Jul 7, 2008

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?

View 9 Replies View Related

Saving Multiple Workbooks At One Time/file As A Text File

Mar 16, 2007

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.

View 9 Replies View Related

DAO Query On Password Protected File: Could Not Decrypt File

Dec 9, 2006

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

View 3 Replies View Related

Saving A File With Specific File Name Using Macro

Feb 18, 2009

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.

View 2 Replies View Related

Query File With Largest Number In File Name

Jul 10, 2009

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.

View 13 Replies View Related

Create An Conection With MS QUERY To A Csv And Query Data

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







Copyrights 2005-15 www.BigResource.com, All rights reserved