I found a way to import some data from some csv files, and it works well for me. I don't want to keep the option to refresh the data. I can go into each tab created from each import and and uncheck the box Data>Import External Data>Data Range Properties>"save query definition" that removes it for me. Is there a way to do that with code. It doesn't work with the macro recorder.
Sub ImportOnline()
On Error Resume Next
Dim DayFile As String
DayFile = InputBox("Enter Date of File (MMDDYY)")
Sheets.Add
ActiveSheet.Select
ActiveSheet.Name = "B-" & DayFile
Application.ScreenUpdating = False
With ActiveSheet.QueryTables.Add(Connection:= _
"TEXT;C:My DocumentsB-" & DayFile & ".txt", _
Destination:=Range("A1"))
.Name = DayFile
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False................
I have 30 worksheets in a workbook in which the first sheet is called summary. The summary sheet has 29 formulas with all named ranges, so one named ranged per worksheet. The issue I have is when i clear old data and add new data every month, the named range changes on some worksheets not pointing to the correct range as previous month file even if I have $
I want to copy a named range from an external workbook. Currently I am using some VBA copied from a recorded macro that first opend the 2nd workbook and then selects the named range, changes back to the original workbook and drops the copied range into the active workbook.
I would like to find a way to get around having to open the reference workbook, and instead simply point to the proper workbook name and named range.
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?
I have a table on a worksheet and have for quite some time used a very successful macro UsedRangeClear. I found here on the forum. Anyway the macro still works on all tables on all other sheets except this one. This is the reason, somehow someone, maybe even me, copied something down or formated down to the 1,048,152th row of the worksheet. My workbook has grown from 5000 kb to 11,000 kb.
I have tried to highlight and deleted the rows, highlight and clear contents and the following macros:
Sub UsedRangeClear() On Error Resume Next ActiveSheet.Unprotect Password:="myfadra" Range("A:A").SpecialCells(xlCellTypeBlanks).EntireRow.Delete ActiveSheet.UsedRange ActiveSheet.Protect Password:="myfadra" End Sub
Sub ReSetTable_UsedRange() 'For ICFMR Dim LR As Long LR = Range("A" & Rows.Count).End(xlUp).row
On Error Resume Next ActiveSheet.Unprotect Password:="myfadra" Application.ScreenUpdating = False Range("A:A").SpecialCells(xlCellTypeBlanks).EntireRow.Delete ActiveSheet.ListObjects("Table7").Resize Range("A1:T" & LR) ActiveSheet.Protect Password:="myfadra" Application.ScreenUpdating = True End Sub
Each option freezes Excel. I am sure there is something easy I am just missing. Soooo, Please offer any suggestions you can think of.
I'm trying to make a spreadsheet that references data tables I have pulled from the internet, with the hopes that these tables will update as I refresh the data connections. I'm in the early stages of creating this spreadsheet right now, but whenever I try to refresh my data connections, it takes a tremendous amount of time. I have about 6 connections with 100 row lines total. Is this typical with data connections to the web? Is there a better way to do this?
i have to take the PGN chess game notation file into excel.it is a text file from notepad. it is like this as shown hereunder. how can i get this into columns movewise. ex.1.d4 Nf6 should come in A1 and 2.c4 d6 should come in A2.
I am creating a tool that many people will be using. I need a way to do the following from vba:
1. Click on the "From Web" button under Get External Data section in Data and show the web query pop-up
2. Input the url into the field and press enter
At this point, it will be the user's responsibility to log in w/ their username and password. I have tried to record a macro w/ no success. I just need to get the query open for the user without them having to do anything.
I have some data in tabular form on a sheet. I want to represent this data on a different sheet, but have it be dynamically updated. I'm thinking that the smartest way would be to give the original data a named range, then do some sort of external data referencing to the source from Data - Import External Data. But this sounds kind of silly.
How do I do this? And no, I don't want to use pivot tables because I don't have statistics to populate in the cells...I just want to display the data as is.
I'm wrinting a macro to copy specific data from a table. To do that I'm using a Autofilter and a list of criteria. The macros works fine for existent data, I'm mean, when the result of the filter is not null, but when the filter doesn't find a result, instead of clearing the filter range, it keeps the last valid. The result is a colapse and freeze the Excel. I'd like to know how can I reset or clear the filter range? The code I'm using is:
Sub Filter_01()
'Definições preliminares Dim rng As Range Dim rng2 As Range Dim ARLE Dim filter_valid, filter_invalid As Variant
filter_valid = Array() filter_invalid = Array() 'Criação do Arquivo de Destino Caminho = "D:Documents and Settingscjcs.ABMeus documentosAutomaSIPPPlanilhas" Nome_Arquivo = "Produtos_Tanques.xls" Nome_Completo = Caminho & "" & Nome_Arquivo ...
I have a macro to clear all contents for a sheet in a workbook. The problem is that every file has over 20 sheets of data. Is there a way I can code a for statement to clear all sheets in this one workbook? Is it possible to make a for statement for multiple workbooks with several sheets?
I have used the VBA and it works to select all data but I have an issue when there is no data to select beneath the column header. I am running this on multiple worksheets which vary with data week to week.
How can I run this and not have it delete the column header when there is no data? Is there a way to change the formula so that it selects all cell columns of data without defining it?
is it possible to clear the value in a range variable?
For example say:
Set A = Range("B4")
Is there a way to clear the value of that range, in other words make it blank again? And unfortuanlly i can't just make it equal to another range i need it to be blank.
I have 5 or 6 macros set up to import stats from the internet to specific sheets. I want all of my imported data to be pasted starting at A1. However when i run these macros, the data IS pasted at A1, but when i run it a second time (or the next day after the pages have been updated), it pastes the data BESIDE the previously pasted stuff. here's what i mean..
say my imported data spans columns A to S.. everything's fine. but tomorrow i want to update the data so i run the macro again. well, it pastes the new data to A1 and again it spans from A to S like it's supposed to, but now it also tacks on the same paste job from S to, AG for instance.here's the code... There are currently 5 macros that have this problem. I thought by selecting A1 as the starting point, i could alleviate all of this but apparently not.
I'm trying to pull in data from another Excel workbook which gets updated on a regular basis. Using the Import External Data fucntion in Excel I created a query which pulls the data from the original workbook into a tab in the workbook I'm using. The issue I'm having is that not all of the column headers get pulled in when using the Import External Data function. I tried typing the column headers in myself but they disappear again after refreshing the data set. Any ideas what is causing the issue?
I use external data queries in excel. That's how I look up data in my access databases because I have so many access and SQL databases it's just eiser to be able to do quick queries on multiple databases at the same time in one spreadsheet. And, I can save the external data query as a tool to use in future queries. I have tons of tools and external data query is the foundation of most of them.
What bugs me is EDITING data.
If I want to do some light simple editing in an SQL database I am required to use a linked table in Access or SQL Studio Express Manager. Great programs, but I want to integrate!
Is there no way to make excel a database editor? I can retrieve data from any database or program with excel just fine, but is there a way to use excel to DIRECTLY EDIT the data in an external database with EXCEL like I can in access linked tables?
I am trying to import the data on the url below. When I go to the external data inport the page is loaded and I click the indicator to load the central table but when the data loads the page is severally corrupted. I have found this on a few pages but some pages load correctly.
I used to get data from external excel, access files and today tried to put together query to get data from CSV file. While using same source file, I have creted several queries to get data out based on criteria. For some strange reason, same structure queries that did run with excel or access as data source, does not want to work the same way with CSV files.
Here is example of non working query SQL: SELECT Vendors.Vendor, Vendors.Name, Vendors.`Name 2` FROM `Q:FinanceRapporterPSAPRaw`Vendors.csv Vendors WHERE (Vendors.Name Like '%' & ? & '%')
If I enter "London" as a criteria input, it will not work. However, if I enter "ondon" it will find me all values records whre name contains London... Seems like I need to skip first symbol when entering criteria. This is, of course, not something I would like to do.
I’ve got a question regarding import of external data in excel. The workbook I am conceiving at the moment does have a database query from another excelfile(a report from my business system) via the standard Excel functionality and it works perfectly fine. What I can’t figure out is how to re-direct my query to any file/path I choose.
At the moment I just replace my original file with a new freshly updated one but I am stuck with the original path and filename or else it won’t work.
I'm wondering if I can use external data in an excel spreadsheet without having to import all the data. I basically need a persistent connection with the external data and I want the excel spreadsheet to get the item descriptions by using the item number as the user types in the item number. Writing out the descriptions is killing me and I have all my products in an excel spreadsheet already. I really need someway to link two separate excel files together. One with data and one with user input that searches that data as I type in the item number.
I'm using a macro to copy the results of a formula and paste the values only on another sheet. The result includes lots of "blank" rows. I have another macro to get rid of the empty rows and move the information up.
It's not working because the "blank" rows aren't empty. Even though I paste values only, experimentation shows that the cells that appear blank return a false to the ISBLANK test with a length of 0.
So now I think I need a macro to run after the pastespecial command to look for cells within a range with a length of zero and delete the contents of those cells, but leave alone anything with a length of >0.
I am brand new to the idea of using VBA, but I have successfully cobbled together some stuff and can usually modify things to work.
It seems I need to maybe use some sort of IF statement along with a LEN and ClearContents. I don't want to delete the blank cells, just make them truly empty so that all of my actual data stays where it should, and my delete empty rows macro works correctly.
I did a search and see some info on clearing contents of columns or rows, or clearing contents based on the content of other columns or rows, but I'm unsure of how to tell it to search each cell within a range and clear the contents of 0-length cells to make them truly empty.
I am trying to write a macro in excel to clear all cells within a range that are non numeric. I seem to be going round in circles trying to find out how to do this.
I assume I have to use the IsNotNumeric(Target) argument but I can't find how to specify the target within a range.
I want to clear the range A10:IV65536 or all the cells containning numbers under the row A10.
Right now it takes for ever to clear it mannually because excel recalculates everything... i have over 100 000 data and id like to find a way to delete them quickly.
Is there a way to clear a range without excel recalculating everything???
If not the best way i think would be to clear the last columns first all the way to the first columns.
