I have a series of files, all named sequentially by date, for example...
Sales Report - Week of 2009-07-27 - NH.xlsm
Sales Report - Week of 2009-08-03 - NH.xlsm
Sales Report - Week of 2009-08-10 - NH.xlsm
...and so on. (The dates are always Mondays.)
I want to get the contents of a particular range on the previous file (e.g. the "RaceResults" range a.k.a. B4:B33 on "Sales Report - Week of 2009-08-10 - NH.xlsm")
When I have a macro open another file, it always activates the macros in it. Is there some way to disable the macros?
View 11 Replies View RelatedIs 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.
View 8 Replies View RelatedI have a database file with a list of components. Each component has manufacturers name, part number, description etc. I need to create an individual file from each of the components in the list. I would like a quick and easy way to copy the information in each Row and generate a new file for each Row containing the existing template I have.
View 9 Replies View RelatedI have a folder containing about 100,000 files and subfolders. Approximately 20% of the files are duplicates. I would like to create a list of all the files with some details so that I can sort through and identify which ones are duplicates, then create a batch file to delete them. How to get a file list with additional details, like size, modified date, etc, into an excel format?
View 3 Replies View RelatedI have a table made up of 4 columns and variable number of rows. Column A has 8-digit number; B has 8-digit number; C has text up to 40 chars; D has a number with two decimal points. I would like to generate or replace a text file in csv format containing each row's data EXCEPT where column D value (number with 2-decimal points) is 0.00.
Using Excel 2003.
I have a restaurant and every evening the manager emails me a sales report. The names of these files are all based on the day's date so I know what the file name will be in advance (e.g. today is 20120109.xls and yesterday was 20120108.xls)
I have a master spreadsheet collating the monthly sales data. It links to the daily spreadsheets with links like this:
=IF(ISERROR(VLOOKUP($A5,'C:UsersSimonDocumentsPersonalTOSEADaily Sales[20120105.xlsx]Sheet1'!$B$6:$F$50,3,FALSE)),0,VLOOKUP($A5,'C:UsersSimonDocumentsPersonalTOSEADaily Sales[20120105.xlsx]Sheet1'!$B$6:$F$50,3,FALSE))
Each day I create a new column and drag the previous column's formulas over. I then do a find and replace to change the file name (20120105.xlsx in the example above) to the current day.
What I want to be able to do is change the filename reference in the formula above to a cell within the master spreadsheet that contains the date, so that when I drag over a new column it picks up the date from that cell and automatically uses it to create the filename in the link. The path for the file name remains the same.
I have a file that I want to name based on content in certain cells. I.e. "Q-(text from cell G1)-(text from cell B7)-Date(mm_dd_yy)". I want to be able to specify the location where I save this file each time I try to save it, but I want it to update the file's name if any of the parameters have changed.
I have some code generated that does just about everything right except that it automatically saves a version of the file in the folder where the original is located even though it also brings up the dialog box and allows me to save it in another location.
Basically, I want to fix the code below so that it doesn't save what appears to be a backup file each time. Should I be using some sort of savecopyas?
Code:
Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
'Add the quote # to the header
ActiveSheet.PageSetup.RightHeader = "Quote No. " & Range("G1").Value
[Code].....
I am thinking to make a excel file which can generate a headre file for my c++ source file. Previoulsy we used to generate .h files using excel but i dont know the logic behind that(Hope some macros using for that).
MY header file contains this many data and my intention is to give "MYapp Alpha 0.0.3" through excel file because the version number changes for each release. If i used excel file then I can edit that excel and it creates .h file for me, later some more informations i can make configurable through excel file
#define APP_FLASH_APP_ID 0x123
#define APP_VERSION_NUM "MYapp Alpha 0.0.3 "
#define APP_PRODUCT_NAME "TPI "
#define APP_DESCRIPTION_STR APP_PRODUCT_NAME APP_VERSION_NUM
#define APP_RELEASE_DATE_STR "10/11/13"
#define APP_SOFTWARE_PARTNUM_LEN 10
1. In centain directory I have xls files where name of each file starts from "HR" string, eg. "HG_Control Mike.xls", ora "HR_Control Mark.xls".
2. I have a master xls file where I want to start a macro that will open each of xls "HR" files and copy selected rows to this master xls workbook (need to write this one too).
Problem is that opening of every "HR" files suposed to be protected by password. Users will be adding new xls "HR" files to the directory so I will not be able to change macro everytime new xls is added. So, I need to make a macro that will generate password base on xls "HR" file name and then I will use this password to protect this files and open them by another macro.
I have a supplier list which i access all the time. To view individual supplier details i use find function using supplier code and then scroll through the sheet to view all the details and it is quite hectic. Is there any way i can modify the sheet so that every time i search for a supplier , only that supplier details can be shown in a different sheet or notification which i can also print if i want.
View 2 Replies View RelatedI want cell D28 to be text of the location to the file selected through CommandButton1.. tia
This is what I have so far:
Code:
Private Sub CommandButton1_Click()
Dim browse As Long
Set Application.GetOpenFilename("All PDF files (*.pdf*), *.pdf*", , _
"Choose a Filename") = browse
ActiveSheet.Range("D28") = browse
End Sub
Is it possible to change the value of an Excel checkbox (from Forms or Control Toolbox) under program control (i.e. using VBA)?
I'm trying to toggle a group of checkboxes on a worksheet from FALSE to TRUE (or back again) using a single button. The checkboxes all have to remain individually selectable by the user even after 'toggling'.
I am trying to distribute an app that uses MSCAL Control. I am including the MSCAL.ocx file in the setup file and it saves it to a dierectory. However, when the app runs it will not load the activex Control (MSCAL)
Application.VBE.ActiveVBProject.References.AddFromFile("c:Program FileBM ApplicationMSCAL.OCX")
I use comments to supply various detail information on the value in the cell. This might take 10-12 lines of comments, but only the first five appear when you hover over the cell. In order to see the entire comment you have to right click and edit then manually resize.
Any tips on how to resize at the time the comment is loaded so no manual intervention is required?
Is it possible to determine the Version of Office / Outlook that is installed on a machine, an based on that, add a reference to the correct Microsoft Outlook com object library programatically?
View 10 Replies View RelatedI'm debugging a file that keeps crashing. Every time it crashes I have to reenter about 6 different watches in my VBE Watch Window. Is there any way to have my Workbook_Open() create the watches?
View 14 Replies View RelatedI've got a series of macros in place which, essentially, download several CSV files into a temp directory, then opens each and copies the data into a single workbook (one sheet).
What happens is that the some number of the sheets don't actually open until the macro stops. I've tried using the wait (Application.Wait Now + TimeValue("00:00:10")) and sleep functions, but it's not quite doing what I want.
What I'd LIKE to do is have the macro actually STOP processing for 10 seconds, then proceed (or start a new macro).
I've also tried putting the wait behind a button in a userform, which is non modal... the csv files open until the user clicks the button.
I have several buttons I and a chart I want to make invisible and visible
programatically. The buttons have names like Button65, etc. I did not
give them these names. Curiously, these buttons don't have properties when
I right click on them in design mode as do other objects. Why not?
Further, they act more like rectangles drawn from autoshapes in that I can
assign a macro to them, but unlike a rectange they are not named as
rectangles (e.g. rectangle 284). Why not? These are hybrid creatures!!
I like to use rectangles as command buttons, but then they do not have
properties which allow them to be made invisible, etc...
The chart name is Chart 1. Same problem, it has no properties. How can I
make it invisible?
I'm adding a command button at run time using excels help code;
Worksheets(newWorksheetName). OLEObjects.Add ClassType:="Forms.CommandButton.1", Link:=False, DisplayAsIcon:=False, Left:=325, Top:=24, Width:=50, Height:=20
I want to change the buttons properties including its caption etc.. I've tried using'
Worksheet(newWorksheetName).OLEObjects("CommandButton1").Caption = "Do"
But i get a run time error. Also, the add method doesn't specify what the button is called, any clues?
VBA coding for automatically saving an excel file as another file using the current date as part of the file name together with "32ga" as a constant add-in. I also what this macro to run at a particular time of the day let say 00:20hrs. The excel file i want to save as is always open . It has data that changes every 24-hrs.
View 1 Replies View RelatedI have a list of names on a worksheet in cells C9:C:C395. Their respected team numbers are located in G9:G395. What I'd like to know is it possible to put a team number in lets say cell K2 and have a listbox appear with the names of each of the team member from that team.
View 4 Replies View RelatedI have a big Excel file with 20+ sheets. All these sheets have different margins and layouts.
I need to generate a Single PDF with all these sheets. Is it possible?
When I save the file as PDF, it converts only the current sheet and NOT the whole file. As I said all my sheets have different margins and layouts, I can not combine them into a single sheet.
I have an excel document that is a form.
Can I use the data collected from this form to generate another new document?
I want the user to enter some data and hit "Create" and a new excel document with that data is created.
Also, the new document has some data in it already. So it has some data and it will get new data from the form.
here it is..
500 to 599>>>> equal t0 600
600 to 699>>>> equal to 700
the numbers from equals is the scale of the drawings which i use plot.
sample:
32039.98
31959.50 ...
Analysis of Profit is very impt in business , that is why i keep trying to find way to use excel to generate profit report.
I have been trying to solve this for last one month. even when i bath... But no result.
May i ask any one here know how to convert my data to a report using . Below table said every thing.
Project (10) HIJ1My Raw Data File 2BrandMonthProfit In %3Brand AJan 0730%4Brand BFeb 0750%5Brand AJan 0730%6Brand BFeb 0750%7 8 9I Need Report In New Work Sheet 10 11BrandJan 07Feb 0712Brand A30%30%13Brand B50%50% Excel tables to the web >> Excel Jeanie HTML 4
I have a macro here that is not working becuase User-Defined Type not Defined. I think i understand it but i want to make sure and if there is any advice for the macro below even better.
what is in red below is what i think i need to identify as Type /end Type.
these are named cells from my data page.
Sub Makeworddoc()
' create a word doc
Dim wordapp As Object
Dim data As Range, message As String
Dim Notes As Integer, i As Integer
Dim Participant As sting, POC As String, phone As String, Email As String, Staff As String
Dim Contact As String, When As String
The rest of the macro is
' Collected information from worksheet
Set data = Sheets("data").Range("A13:G13")
Notes = Sheets("data").Range("notes")
' updated status bar
Application.StatusBar = "Creating Word Doc."
' assign current data to variables.............
on generating a unique ID for each row in a spreadsheet. My current manually entered ID numbers are alpha numeric, so I want to use a formula or macro to follow the same format.
For example the IDs may be ABC001, ABC002, AAA001, AAA002, AAA003. So user enters info in a form built in excel, and I run a macro which takes the data entered into that form and populates another spreadsheet and I assign an ID number.
The ID number should look for the next number in the sequence, based upon the first three characters.
Is there an Excel Addin that will let me figure out the total number of combinations for any size number pool I specify (39,49, 53) and with any combination quantity I specify (3, 4, 5, 6)? And most important is that all these combinations be displayed in a column separated by commas.
Is a VBA script designed to handle something this complex.
I am working in a Lotus notes application that enables me to add an Excel report and chart. I have no problem adding a report using their template creater. however it says for me to add a chart I have to create an excel template that must contain the vbscript needed to generate a chart.
I tried using the macro editor to create the report but i get an error "Microsoft Excel Select method of range failed".