each row in sheet1 contains variables that, when applied to cells in sheet2, calculate a final figure. I have written code that selects each row, distributes its variables in sheet2, and shows the result back in sheet1, then selects the next row, and so on until all rows have been calculated.
The calculation is complex, and I would like to create a text file that stores the calculation for each row in the same text file. The calculation is a 9 columns by 33 rows, and I would like to preserve this shape in the text file, though its OK not to have columns not lining up.
There is a similar problem on an old thread but this requires only one iteration of "open file, write values, close file". I need "open file, write values, write more value, close file".
I have many users of the same spreadsheet on different PCs writing .txt files to a common directory on a central server. I want to be able to create a uniquely named .txt file, using a set naming convention (datetimeotherthingsetc.txt) each time they execute my export code routine. Is there a way I can create a txt file from vba code? I have figured out how to write into an existing .txt file but would like to know if I can create a .txt file from scratch from vba code? ps to avoid any confusion I dont want to save (ie save as) the open spreadsheet to a .txt file
What I have is a double quoted, comma delimited text file that is created from an external application, every now and again we need to open the file to make corrections and this is easier if we open it via Excel using the delimiter options, the problem is when saving the file as a csv it removes the double quotes, however I have found some vba coding that adds the double quotes back, which works great, but it changes the format of the date fields from what the original file had from 09-Aug-2009 to 09/08/2009.
I suspect this may be because the file is opened before the double quotes are added and excel changes the format.
Is there any code I can add that will stop the date format from changing ...
I would like to automatically update a 'yearly' database file with info from a file that is changed on a daily basis.
The daily file that i use has info like date, truck number, delivery stops, weight.
the database file has the similar headings.
at the end of each day this daily file is saved. I would like to have the info that is entered into the daily file automatically plugged into the yearly database file into the next available group of cells with respect to the salesperson.
This is kind of a generalization but i'm hoping to just get pointed in the right direction. If something like this involves vba then it will be beyond my ability and i'll have to do it manually, which is fine
I have an existing spreadsheet with a column of strings (actually VIN numbers). These numbers correllate to a bunch of text files, that can exist in one of three folders (UsernameDesktop1, 2, or 3) on my desktop. What I need the macro to do is:
1) get the filename from A2 (A1 is a heading row) 2) Find the appropriate text file in one of the three folders 3) Put the folder name into I2 4) Scan the text file for some strings, and copy some data that follows those strings into J2:O2 (I can handle programming this) 5) Close the text file 6) repeat above for the remainder of filenames (about 1800 files)
Got a slight problem; I am making a "worker-plan", where you should be able to write a name on one file and have it updated (removed) from the equivalent columns on another file automatically. Here's an example with two separate projects:
Project 1 before and after changes: Attachment 257366 Attachment 257367
Project 2 before and after changes: Attachment 257368 Attachment 257369
You can see here how I intended it to work. When one worker is moved from Project 1 to Project 2, that worker should also be removed from those weeks in Project 1. The previous worker from Project 2 should also be removed from those weeks (in Project 2).
Are there any formulas that can make this work automatically? All week numbers will belong to the same columns in all files.
Edit: There are 30 files that should be syncronized, not just two.. I could also gather all files into one file with sheets, instead of having seperate files.
I've got numerous vba-coded spreadsheets in use with lots of different users, and have found that some users try to hide errors of their own making by blaming the spreadsheets (I know - what is the world coming to!)
I've decided to add a script which creates a log file each time one of my spreadsheets is used - opening a log which relates to the user's spreadsheet and recording username, date/time, path & file, and the vba being used.
I'll probably add an "on error" script to save copies of all open workbooks too.
The issue I have at the moment is how to record the name of the macro being run?
I have a text document to which i need to do some changes and then turn it into CSV. Here are some lines of what the document looks like and below i will show you what i need it to look like. So i open the document with Excel and next I'm not sure how to do what i will show you below. Original:
I need a little help creating the macro for a template file which is going to be used for data input and further analysis. I have a set of arrays, which can be altered. The arrays may look like so:
I have some code that looks at the part # in column D and then goes out to the D drive to drawings (.pdf files) and creates a hyperlink.
The problem is that it is only creating a link to the .pdf file and not to folders. I need a hyperlink to the folder so when it is clicked the open folder pops up and shows the multiple files associated with that part.
Link should be the same regardless of file or folder, no? As long as the name matches link should be made, correct?
E.g. 123-100.pdf (column D part #) links to 123-100.pdf file on CD 456-200 (folder name in column D) no link created to folder named 456-200 on CD.
I need to distribute it to several areas of my company. Each area only needs part of the report. Therefore I have written some code which splits the existing report by area and creates a brand new report with that smaller chunk of data in it.
However I now realize that the new sub-report will require some code of it's own in order to function. My problem is that only the sub-report will be distributed to the end user. The core report will not. Therefore any code written for the new sub-report must be included within the sub-report.
Just striving to have this kind of "picture arrangement's macro" to work, macro that whenever you choose a certain cell and operate it on hotkey it'll:
1. Create a new folder named "PicturesAuto" beside the excel file (wherever the excel file located).
2. Inside folder "picturesAuto it'll create a new subfolder named by the same name of the sheet related to that cell you operated the macro upon. (let's say this cell placed on sheet named: "happysheet"- that'll be the name of the subfolder created.)
3. Inside the subfolder "happysheet" it'll create a new subfolder named by the text of the cell you activated the macro upon (let's say you initially operated the macro on B5 cell -which has the text "montana" so it'll create "montana" subfolder also inside subfolder "happysheet")
4. Lastly instead of having only the text "montana" in "B5 cell" like we had initially before activating the macro, the macro'll also add to that text "montana" the link to "montana" folder (path of excel file->picturesAuto->happysheet->montana)
The way I'm planning to use this is to add a lot of HR pictures inside those folders created by the macro that related to that specific sheet, and that specific cell I've activated the macro upon. Plus just having those pictures so organized is just outstanding in every scale, and the excel file will keep running smoothly without any resource consuming overload.
I am trying to create a multiple file picker. The code is below.
I want to open the first file of lets say 5 selected files, do some autofilter task, copy data from that file, paste it in another workbook and close it. Open second file, do some autofilter task, copy data from that file, paste it in below the first file pasted data in another workbook and close it... and File 3... and so on.
It works for one file, the code for copying pasting is not yet added. I think I can manage that. But how I return to the file picker function after it opens the first file.
I am looking to have these workbooks save automatically to a folder on the C: drive. If the folder I specify is not available on the C: drive, how do I write teh save comand to create it automatically with a prompt
I'm trying to check to see if a file exists using VBA but the routines I see use a string that has the path and then the file name. I have a fixed path C:VBtesting and the a variable file name. I'm using a file name that consists of a username & date such as cwilliams201212. I'm having problems getting the variable file name into the string.
I have been tasked by the company I work for to come up with a simple to use interface that can be used by someone with little understanding of excel to set the autofilters to certain settings.
So here is what I have:
2 sheets (each with the same number of columns but different data which corresponds to good and no-good)
Each column has an autofilter but only two on each sheet need to be set.
One filter controls valve type (of which there are 10) and the other controls running condition (startup and normal)
Now my employer wants something that is basically idiot proof so that someone can simply open up the excel file, input the valve type and running condition, and be rewarded with only the corresponding data and charts. Now the filters are already in place, the charts already exist and the only thing left is the user interface.
I am working on a research project of which details cannot be disclosed. I basically have many worksheets which each contain 3D positional data and I am plotting overlaid graphs using multiple worksheets. I have gotten them to work where I manually enter the names of the worksheets, but I am trying to use the CELL("filename") command to get the current worksheet's name, then using a substitute command to change certain fields, then I would like to use the string name created by those functions to call a global variable in another worksheet which defines a range. I will try to give an example.
Say I have 3 worksheets for arbitrary measurements: 1) filename 1mm 2) filename 2mm 3) filename 3mm
I would like to graph all 3 sets of data in worksheet "filename 1mm", using the fact that everything in the filenames are the same except for the 1/2/3mm part. I have a template of which I will be copy/pasting data from numerous data sets and then each worksheet has defined names "X_vals" and "Y_vals" which give me dynamic ranges for the data I wish to plot from that worksheet.
When I type the names manually--i.e. " 'filename 2mm'!X_vals" everything works fine, but when I try to create the exact same string dynamically using indirect/substitute/left/right etc, it does not let me do it. I am able to create a dynamic string with a range, such as " 'filename 2mm'!C10:C100" but then when I change it to " 'filename 2mm!X_vals" it just gives me #REF!.
I guess my question is just is there any way to use the indirect function to create a string name dynamically which references a defined name in another worksheet? If not possible with just simple excel functions, is there a way to do this with a macro? I am not exactly familiar with VBA in excel, though I know how to run macros.
I have a sheet that has 5 columns as shown below, xxx represents the values within the cell that I need in each sql file.
A |B | C| D| E test1|xxx|xxx|xxx|xxx| test2|xxx|xxx|xxx|xxx| test3|xxx|xxx|xxx|xxx| test4|xxx|xxx|xxx|xxx| test5|xxx|xxx|xxx|xxx|
What I am trying to do is take the context of each cell starting with B2, and place it in an Individual sql file (or text file with .sql extension) and name it A2 (so test1 in this case).
I tried creating a double for loop, one for last column, one for last row, taking each content and placing in file but I am not sure how to take the column A as the name of the file and to have 1 file per cell.
I have a piece of code that runs and creates an output Excel sheet that currently has the run date as a suffix for the automatically generated file name. However, I now sometimes have to run this report multiple times in one day, which currently means overwriting the previously run spreadsheet (whereas I would like to keep both versions of the ruin output).
Is there a way that I can create some kind of counter for the number of times the report is run and add that as a suffix for the file name? At the moment the only way I can think to do this is to set up code that looks at the file name containing the date field and adds 1 to every number that is added as a suffix, but this doesn't seem very efficient...
This is a code that i have for having a master workbook, and saving it in different folder so my employee cant access it
Sub NextInvoice() Range("J2").Value = Range("J2").Value + 1 Range("E6:E9,H9,J9,B14:K20,H4,B28:K32,B36:B39,D36:D39,F36:F39,B42:K43,B46:K47,B50:K51,B54:K55,B58:K59,B62:K63,B66:K67,B70:K71,B 74:K75,D78:E78").ClearContents End Sub Sub SaveInvWithNewName() Dim NewFN As Variant 'Copy Invoice to a new workbook
[code].....
I have found a code for mandatory cell, but it creates a second macro and i was not able to link the two.The mandatory field has drop down of employee's and it is located in the cells D78:E78.
I'm trying take a reference from some workbooks (e.g. WB1.xls,WB2.xls,WB3.xls,...etc) They are all placed in one folder but there filename is subject to change,
I need to get the result of Cells A1 from WB1.xls+A1 from WB2.xls + A1 from WB3...etc to shown on AA1 in another workbook(e.g. Collected.XLS)
Is this possible knowing that workbooks filenames are changeable? Moreover Can the value of A1 from any new workbook added to the same folder be added automatically?...
Can the formula typed in AA1 from Collected.xls be draged down?(So as to show the sum of Cells B1 and C1.....etc) from each workbook?
I have a macro which imports data from a mainframe dump text file and performs 'Text to Columns' on the imported data so that formula in the spreadsheet can act on the data. The code works perfectly well when I use it, but if a different user logs on and performs exactly the same mainframe dump and import macro the Text to Columns action splits the raw data in a different way and the result is that the split renders the formulae useless.
I've experimented a little and for some reason it appears that the 'Field Info' parameters which are produced when the Text to Columns function is recorded in a macro differ between users even though the raw data is exactly the same.
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 workbook with many spreadsheet named Sheet1, Sheet2 and so on. Each sheet is filled completely upto 65536 rows. This data is being picked up from a CSV file. In this file there are sites with each site there is a assciated set of data. What happens is the data that extracts data does not differntiate between sites and when it reaches to the end of worksheet it splits the data into next sheet. So I am trying to create a macro to check each spreadsheet starting with the last sheet in the work book for example last sheet in the workbook is seven it should go to sheet6 and if there is a blank row after row 64000 it should cut all the rows and move them to sheet7. Then it should goto sheet5 and do the same and keep on doing it until it reaches sheet1.
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.
Ok, I have a situation that I would love to have some help with. I have a command button that when clicked takes the user through a few questions via input boxes. The final objective that I need help with is a large typing area. Either the input box typing area needs to be larger or I need a text box to appear. After user is done typing and presses ok, I need what they typed to go into an area where I have merged some cells/rows to make a paragraph.
