I need to link my code to data from a different Excel file. I feel it is smth simple, i just have never done this before. In a regular (=same file) setting, my code would be smth like that:
With Worksheets("Daily1")
j = 1
Do Until IsEmpty Range("A6").Offset(j - 1, 0).Value)
j = j + 1
Loop
m = j
End With
What would be the syntax if data instead comes from, say, Z:Brazildata.xls. In general, when I retrieve data from another file, do I always have to use "With - End With" structure?
In A2 is the Stock price, and B2 the time of the update.Now every time it updates A2 and B2 get overwritten.I need some code that will save/ record the the stock price on the hour, every hour. I then need the highest and the lowest price within that hour, and lastly the price at the very end of that hour. (example)
I received what I think may work, but I dont know how to write this in VBA. If the time of the new value received > than B2 + 1 second, create new row where Open=High=Low=Close else if (last column=new value,if new value>high column then high column=new value,if new value<low coulmn then low column=new value.)
I inherited a spreadsheet to manage that is linked to a SharePoint table.
It is trying to populate a date that a certain "Tier" is selected (1, 2, 3, or 4).
It works great if I manually type in the tiers, but does not run on existing data (about 400 records) or lines that are updated and new via the SharePoint list.
How can I have this run on all of the existing lines and anything added or changed in the future from the list?
VB: Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count > 1 Then Exit Sub If Not Intersect(Target, Range("AD2:AD10000")) Is Nothing Then Application.EnableEvents = False
I have created a 'price list' database in ACCESS. Then in EXCEL I created a pivot table which retrieves data from one of the database queries (the query was saved as a .dqy file).
I emailed the file containing the pivot table to a colleague who is on the same server. He saved the excel file on he desktop & renamed it. When I update the databse file on a shared public drive on the server, he is able to 'refresh' his desktop file successfully !!
We have a set of workbooks with a linked Vlookup formula. When we email the file to staff that do not have access to the linked file, the linked formula seems to change the directory. see below
Original Formula =VLOOKUP($A$30,'G:Variance Reports FY07[Salary Dist Var Repts_Cur Mth.xls]end of July'!$E$76:$G$200,3)
Formula after user opens email with the drive changed automatically to C =VLOOKUP($A$30,'C:Variance Reports FY07[Salary Dist Var Repts_Cur Mth.xls]end of July'!$E$76:$G$200,3)
Note that the user does not have access to the G drive and they are not updating the links when they open the file.
I have a summary file in which I capture data from multiple sheets in one existing file (targetfile.xls).
One month might contain sheets that do not exist next month. When updating (edit) links, Excel reports an error (invalid external reference) on the first missing sheet, and does not continue checking/updating links for the rest of the document.
I tried =IF(ISERROR( SUM('[targetfile.xls]sheet1'!$M:$M)),0,SUM('[[targetfile.xls]sheet1'!$M:$M)),
I have attached an example spreadsheet to describe what I am working with. I was wondering if there is an easy way to have it so that everything in Column A is hyper linked to the ship bill file. This list can exceed over 1000 and to hyper link each line is too much, is there an automatic way to do this? For example. I would like the value '00001 in cell A2, to be hyper linked to the file "Shipping Bill - 00001" So that I can just click on the link and open up the file. The shipping bill files are in the same folder as the example spreadsheet.
I want to insert linked AutoCAD files into a worksheet.
When I select "Insert, Object, Create From File, Browse..." & select AutoCAD file I get error message "Cannot insert object"
I can follow same process, but with a Solidworks drawing & it works fine.
I need to install the relevant graphics filter "Dxfimp32.flt", but I can't find it anywhere.
Strangley, if I copy images directly from AutoCAD screen, I'm able to paste them into a worksheet, but as an embedded object. Whilst this suggests I have some AutoCAD graphics ability, I still can't insert a linked file.
I have a workbook with a button to get updated data. The button simply opens the other workbook and then closes it. THe problem is it opens the book and then closes it before the opened book has a chance to finish it calculation resulting in lots of #value errors. The book that is being opened works fine but because it is shared it can't be left open and needs to be closed asap, though it should be allowed to finsih calcs before it gets closed again. If anyone know a way to make the following temporarily halt until the opened book finishes calculating before it closes it.
I have a workbook with several sheets with formulas, etc. one of the sheets its like a "resume" of the workbook. I want that sheet with the "resume" to be visualized by other person's without giving the access to the workbook.
The idea it's a file with linked data with the workbook that have the sheet with the "resume". When I change some data in the workbook the file with the linked data must be updated when someone open it and cannot edit, it is just for visualization.
I've inserted a Solidworks drawing into a worksheet & wish to change the displayed image using VB. I suspect this could be done by changing it's source file, but can't get the code to work.
I am trying to write some VBA that will select a row of cells that each have links to cells in another workbook, and then either autofill the formulas down (or pastespecial them down) for 20 rows. I have that part down using either pastespecial or autofill, but -
Since the forumulas are links to another workbook, Excel wants to resolve that link to calculate the values at the time of the pastespecial or autfill. If the sourced workbook is not open, the "browse for file" popup displays. I don't want the popup to display and would rather just get the #REF in the pasted cells and let it resolve the next time the sourced workbook is open. The end result would be like clicking "cancel" to the "browse for file popup", which I'm tired of constantly doing... I would just like to skip that popup all together and get the #REF value.
I have 3 worksheets, one worksheet is a summary worksheet that gives totals for what the user chooses... like Actual vs Budget or Actual vs Prior... etc. When we are in our meetings a common question is where did these numbers come from?
I would like to create a macro button that when you are on a cell you can click the button and the macro well take you to the destination cell. Is there a way you can write a formula in to the macro that would use the formula in the current cell and take you to where that number comes from?
Is it possible to write vba code that will generate a text file with ALL changes that were made to an excel file. Ex. If Cell A17 = "Monday, June 4, 2012" and a user updates Cell A17 to "N/A", I would like to know what the value was before and after the udpate was made.
I have a spreadsheet where I want to require certain fields to be completed then I want to have that file auto emailed. I have learned that I do need to have the file saved before sending otherwise the data will not appear in the email, so with this I want to have the file temporarily saved emailed then the temp file deleted.
Here is the code I have so far but it errors on the blue text, I did change the TempFileName from = "Copy of " & wb1.Name & " " & Format(Now, "dd-mmm-yy h-mm-ss") to = [C16] & "_" & [B6] & "_" & [D6]
Private Sub CommandButton1_Click() If Range("B6").Value = "" Or _ Range("d6").Value = "" Or _ Range("f6").Value = "" Or _ Range("E9").Value = "" Or _
I have a file that I save with a new version number each time I make major changes. The file name currently is: "Telephony Equipment Inventory v26 (Summary).xlsm". The "26" is the variable number. give me the vba code to ensure I open the file with the highest version number?
I have linked two workbooks together but now need to be able to convert that linked data to straight text. I have a v-lookup in the report that needs this to be this way. I do not want to use paste special values because that would defeat the purpose of linking the two pages.
I am trying to set up a new assurance work book and worksheet (worksheet2 lets say) which will dynamically link to another workbook and worksheet (worksheet 1) owned by another part of my business. The purpose using the data set in worksheet 1 is that this is the source data and is the most accurate for the project information. So I am looking up columns A, B and C in that workbook in my new worksheet2 using ='[Spreadheet 1.xlsx]Sheet1'!$A$1:$A$1174 the same for column B and C, with the aim that any new data entries within columns A, B and C will be populated in worksheet2.
In columns D to Z of worksheet 2 (my worksheet) I am applying some assurance metrics to the project information that is specific each row of column A, B and C which are dynamically linked to woorksheet1. My problem and it is completely eluding me is this....
The owners of worksheet1 regularly sort the data into chronological order based on Column A. However the data is not normally presented in this way i.e. all new entries regardless of date are added to the list at the bottom of worksheet1. The problem I have is, is keeping my row data in Columns D-Z linked to the row data in columns A-C of worksheet2 no matter what kind of sorting occurs to Columns A-C in worksheet1.
I'm trying to see if there is a way to speed up the process of doing multiple Google searches to see if I can find a website attached to a text. I have a list of websites, and am trying to update the list to remove dead/inactive sites, and also find the URLs for the live sites.
The list looks like this (sample): Comstock, Inc. ConNuestroPeru Contacto Latino contentSutra Core States Crains Chicago Crains Chicago Business Crains Detroit Crains Detroit Business Credential Crescent-News.com Crowell Weedon
Thus there's no website attached, and I need to manually find the URL myself. There are over a thousand cells in the list.
How to speed up the process of finding a url for these websites?
I have two linked workbooks, one containing a large data table, and the other containing individual worksheets that extract the data from the large table via HLOOKUP equations in each cell.
Is there a way to have Excel hide the row of a worksheet if the resulting HLOOKUP result in the cell in that row is empty? Also, if there is data in the cell, can I get Excel to automatically adjust the row height based in the incoming data and not on the HLOOKUP equation?
I am using OFFSET to make dynamic named ranges- the only problem is that I'm having trouble using it with linked data-
The Situation:
-Cells A1:H500 are linked to another sheet, with each cell containing a formula like =IF(ISBLANK('C:...[workbook.xlsx]worksheet'!A1),"",'C:...[workbook.xlsx]worksheet'!A1)
-Currently only cells A1:H100 have data, but this is dynamic and will change
-I want a named range that will only select the cells with data in them...the problem is that COUNTA counts the link formula even if the cell is "blank" (because it isn't blank).
-I hate hard-coding ranges...the solution for the named range =OFFSET($A$1,0,0,500-countblank($A$1:$A$500),8) is ugly!
Any solution to making dynamic named ranges with linked data that may or may not be "" ?
I've have a spreadsheet where 4 cells are linked to another workbook via a vlookup. the problem i have is that a lot of users can update this external book, or it can be saved as a seperate spreadsheet somewhere else on the network. If it was up2 me i would have them only update the one sheet, but as it stands its not. So what i want to be able to do is put some code onto a button on the sheet, from here i want the: Application. GetOpenFileName
method to open....but from here i want them to be able to pick the cells where the data is situated. Any clues..... i can get as far as them selecting a workbook. Do you think i will need to create another userform?? Maybe RefEdit? I'm not sure.
My company has files that are already in use. I don't know too many details about how they work, but somehow saving the file will screw it up and my boss has to go back and reset something or other to correct it. Obviously it's connected to some other software somewhere. The code below will block Save and Save-As. BUT how do I get the file to hold onto the code without actually saving the file after the code is added (since the file shouldn't be saved)?
VB: Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean) If SaveAsUI = False Then Cancel = True
On a weekly basis I update a suite of charts in one workbook that are created from several other linked workbooks. Once compiled I send this via email to a colleague who incorporates it into another report by printing the charts as a pdf. For some reason he often ends up with erroneous data (zero's where there shouldn't be on the charts), or often his system doesn't display certain elements like titles and axes etc
We think his system is spending resource looking for the original data and getting itself knotted up, but surely there should be a way of delinking the charts but maintaining their integrity as they are saved?
On Sheet1 I'm attempting to auto populate specific cells in columns B,C & D with information found in Data Validation lists (found on Sheet2) based on the "value" chosen from a list in column A. For instance,
If A2=Pig Then B2=Slop, C2=Pen, D2=Food
Is this best accomplished through VBA or a basic Function?
I have the following code that should open all EXCEL workbooks in a specified path, and unprotoect any password-protected worksheets to allow for Link Updates, then close the workbook after password protecting it.
sub UpdateAllLinks() Dim vLinkSources Dim iLinkSource As Integer Dim AnySheet As Worksheet sPath = " C:Documents and SettingsShaneMy DocumentsHarcourt Assessmentspassword" sName = Dir(sPath & "*.xls") do while sName <> ""...............
Bar those who don't believe/celebrate in Christmas, Hope you all had a good Christmas and New Year celebrations.
I have two sets of data, lets call them 2013 and 2014. I have a Spreadsheet already set up in excel, but currently I just past this information into a tab in excel (which isn't ideal at all), and given the data is growing, is slowing down my excel spreadsheet a lot.
I have some experience with Access, SQL and queries.
What I have done thus far is to link my Access DB with Excel, run a query and paste the resulting table from the query into excel all using VBA (easy peasy).
The problem i have is that the table is still linked, and i do not want or need it to be linked. I know that there is a "Unlink" Option, which is not what i need, and also a "Convert to Range" option which is also not what i need, but closer. This got me thinking, is it possible to import the data into an array (all in VBA in excel from the query in the SQL) and then i can paste that information into my Excel spreadsheet, or is there a better method?
