I have a workbook which was quite large 1.4mb. I wanted to reduce the size so peice by peice I deleted forms modules and sheets to see which were the problem parts.
But interestingly enough I have now deleted eveything in my workbook and It still remains and 400kb?
I have deleted all code modules, sheet code, user forms, defined names and custom views.
I have tried saving as an html but alas it still remains at 400kb.
I have csv files auto generated (with date stamp in name) and saved in a specific folder everyday. I need to find a macro that will copy the row(s) every day and add to the next empty row in the master excel file. Some days the csv file may have a single row of data and on some other days it may have multiple rows of data to copy and paste.
View 4 Replies View RelatedI have got a master workbook and I have written macro to copy and paste data on another workbook. write a macro to save the new workbook to a file path with a file name where both file name and path are stored in master workbook sheet...
View 5 Replies View RelatedI 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]....
I am trying to import some csv files so I can combine them, but am having probs with the filename and location.
Sub test()
Dim wsName As String
wsName = ActiveCell
Sheets("Data").Select
With ActiveSheet.UsedRange
LastRow = .SpecialCells(11).Row
End With
With ActiveSheet.QueryTables.Add(Connection:="TEXT; &thisWorkbook.Path &" " & wsName &", Destination:= Range("A" & LastRow))
.Name = wsName
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.RefreshStyle = xlInsertDeleteCells.....................
I've encountered a bit of an odd one. I am opening a tab delimited file, which has been sved as an xls. When I open from file manager, it opens correctly. When I open from Excel, I have to go through 3 screen dictating how the data is set up, and the end result is that a couple of the numbers come through as text, rather than numbers.
Failing an explanation - how do you open workbooks as file system objects - currently I am opening them using
Workbooks.Open Filename:=wb.Path & "/" & ws. Range("B1").Value
If I open them by mimicking the FM, perhaps it will work OK.
I want macro which export each excel column to new text file. The data in excel file is number. The column has only 5 rows that means each new text file should contain five lines of one column. It looks simple but couldn't manage to do macro for it. I have very big data set in one excel file, and have to be splitted into text files. The file name in new text files can be any kind as long as it can be in some sort of order for each export.
View 2 Replies View RelatedI'm unsure how to adapt the code to suit my needs.
I have an Excel workbook that is created a few times a month from a template. When I update it I run a macro that saves a values only copy with the current date. I'd like to copy non-blank rows (from row 8 down) into the first empty row in a master workbook that is saved on a network drive. The path to the master workbook is "B:8MPBGTrade Pre-AdviceBG Trade Confirm Master.xls"
I have the macro that is supposed to save to the next empty row to a closed workbook in a shared network drive. But I am getting an error saying that Excel cannot open the workbook.
Code:
Private Sub CommandButton5_Click()
Workbooks.Open Filename:="phgbsorgad03.ph.gbsorg.netmembersmigsCall_Form_Dbase.xlsm"
Sheets("Database").Activate
Application.ScreenUpdating = False
[Code] ........
Let's say I have 2 files: Source file and working file. In source file there are some text names in a column that are updated once in a while.
1. I need to create a column in "working file" such that is taking values from column in "source file" even when "source file" is closed.
2. It should take only non empty values, because I need to create a cell with validation list that consists of text names from the column.
Solving attempt: By searching some solutions in forum I found that the first part I can do in the following way: copy column from "source file", select in "working file" a "paste special" option and choose "paste link". It works, but the problem is that it imports all the column: if in "source file" the column consists of words "a" (cell A1), "b" (cell A2) and all other cells in A column are empty - in "working file", after linking, it appears as "a" (cell A1), "b" (cell A2) and all other cells in A column are "0" (zeros) till cell A65536. And I need that in "working file" column after linking will appear as "a" (cell A1), "b" (cell A2) and all other cells will be empty, so by setting one of cells in B column to be a list (by "Data" - "Data validation" - "List" ) - it will consist only from "a" and "b", and not from "a", "b", "0", "0", "0", .... (65534 zeros).
Is it possible to have excel 2007 open a workbook, activate the first sheet "output" and activate the first empty cell in column A - ready for user input? i.e.
I want my user to open workbook "EHB Stock" and automatically the first empty cell in column A of worksheet "output" is activated and ready for input.
The rest of my code is:
I have a worksheet with some info that is filled in from other employees. I have Customer Name, Date of Oldest Invoice, Avg. Days Beyond terms in column N42:N44, with the data in column M42:44. I want to copy and paste that into another workbook, I need to transpose the data and then paste it into the next blank row. IF POSSIBLE, I would like to have some of the column headings the same as the above fields, and I would like to paste the data into the correct column heading.
Currently I have the code listed below. It copies the data, opens the spreadsheet, (this is where it fails) find the next blank row and transpose and paste the data. It does transpose and paste the data but it does not look for the next blank row, instead it just transposes and pastes the data in the last cell that was active when the work book was closed.
Code:
Private Sub CommandButton1_Click()' Paste Macro
' Macro recorded 5/14/14
'
'
Range("O42:O47").Select
Selection.Copy
Workbooks.Open Filename:="L:Financial Services! FRA !! Preston !3rd Party Collections Accounts 2014.xls"
[Code] ..........
Cell F9 looks at D9. If there is no value, I would like it to return "", if there is a value, I'd like it to finish the formula.
It figures the formula correctly, providing there is a value in D9.
However, the entire D column is referencing cells from another workbook.
So while no value shows up, if you click on an empty cell in D9 it says:
='C:Workbooks[EnterMenu.xls]Enter Menu Items Here'!D5
How can I get Excel to see 'visually' empty cells in the D column as empty when it seems Excel is reading the cell value as a reference path.
However, that diagnoses might be wrong, since if there is a value there, it figures the formula using the value and pays no attention to the reference path.
This is the formula I'm using currently:
=IF(D9=0,"", IF(N(H9),H9/D9,G9/D9))
Here find the excel file
My requirement
1) 4 values contains in each row based on the values from those cells the max value will display.
2) if more than 2 cells have empty,NR or NA text means the entire row has to delete.
3) if 2 or more that means 3 cells having values the empty cell,NR or NA cell will place value with the condition of macro that is 75% of other values which is maximum among them.
I am looking to find all visible cells in column E that are blank, and then add ''B'' to those empty cells.
I am using code similar to the below:
[Code] .....
I am trying to populate the 2 tables from excel to word. I will be getting the excel file with tables in various sheets. One sheet consist of 2 tables that will be inserted to one word document. So if there are 2 sheets then I will have the tables inserted in the 2 word document. In the excel sheet I have attached, there are 2 sheets with tables in each of them. I have written the code to copy and paste the table to word doc from (general) range A1:G4 (Table 1) and A9:H18 (Table 2) that has empty rows and columns selected. But there are empty rows and columns inserted since the table range is not same sheetwise. I would like get the empty rows and columns deleted in the word table.
Find the attached sample excel sheet and the word documents.
DeleteEmptyRows(Sample).xlsx
Sheet1.doc
Sheet2.doc
I have a long range of cells (U3:AX3), all of which are empty save one. Is there a way to search through the range of cells, and return the contents of the one cell that contains text?
I would do this with a series of nested IF statements if there weren't more than 30 of them!
Is a Cell with a formula (like shown below) considered true, or is it empty?
=IF(Scorecard!$B$13,Scorecard!$AD$4,"")
If Scorecard!$B$13 was False...
Would a cell with the above formula be considered?
True or Empty?
If Scorecard!$B$13 was True...
A cell with the above formula would be True.
I am getting values for my excel sheet from another department excel sheet . everything works fine. If there is no values in the rows in the Department sheet, then i need to hide the rows in my sheet. How to code this in VBA. When they add values to the rows then i should make the rows visble here. Kindy give me a sample of vba code to this or suggest me to solve.
View 9 Replies View RelatedIs it possible to make a cell "really" blank/empty based on an If statement? For instance:
=if(a1>10,a1,"")
Has a value_if_false of "". But Excel interprets this a bit differently than a cell that never had anything typed into it.
So if you have a column full of this formula copied down, and hit <control+down arrow>, you will go straight to the bottom and skip over all rows. Whereas if you have a column with values and empty cells alternating and hit <control+down arrow>, you will only skip the empty cells and go to the next value. Excel treats the conditionally empty cells as if they have a value, when it comes to this type of navigation. This holds even if you copy and paste "Values" for the cells over the formulas.
Is there any way to tell Excel to make the cells truly empty?
At work I have a folder full of .bas files containing useful UDF's.
At the moment I am the only person in my team that makes use of these files as importing a .bas file is beyond the rest of the team (i.e. then know it contains code so they get scared).
What I'd like to do is create a workbook where they can select one of the files (each file contains a single UDF), maybe see what it does (I put an explanation in the Comments section of the properties for each file) and then have it automatically imported into the workbook of their choice so they can make use of these functions.
a macro to check if a file exists before saving. The code saves the file OK if the filename does not exist but fails if it does.
Sub CheckFileName()
Dim FName As String
FName = ThisWorkbook. Name
'saves file
Dim FPath As String
FPath = ThisWorkbook.Path & ""
Dim y As String
convert all spreadsheet in a workbook to one pdf file. I use PrimoPDF to convert, then I only convert 1 sheet to PDF even that I have select all sheets. My be it is a better PDF converter for free you use or other ways to do it.
View 4 Replies View RelatedI have tried to work this problem out by myself, but I'm afraid it's beyond my ability. Is there a way to open a file from inside excel. Can I open " Master1.xls" from inside " Master 2.xls, (Master 2 is running, Master 1 is not open). I've tried linking but I think that's beyond me as well.
View 4 Replies View RelatedIs it possible to have the name of a workbook referenced in a cell via a formula or VBA in that workbook? I'm trying to extract the date that is in the filename, and input that in all cells in column N that have data in column M. I know I'll have to do some manipulating, but if I could somehow automate getting the filename input into the workbook, I might be able to muddle my way through it.
View 2 Replies View RelatedI tried opening an excel 2003 file in an xp environment and it says that the file is not accesible? how can i open the file?
View 3 Replies View RelatedI need to write a macro which checks cells in one column and if the cell is empty it deletes the whole row (which contains the cell).
I tried this code but it doesn't delete all rows with empty cells:
I am using the following block of code, which cycles through the data and first deletes any cell with "Legal:" in it, and then cycles through again and deletes any row where the cell is blank.
The problem is that within the data, there are some locations where there are two blank rows in a row. When the code runs through, it deletes the FIRST blank row only, not the second. I Need ALL blank rows within the data set to be deleted.
Code:
Sub ModifyNewData()
Dim r As Range, rAll As Range
Dim WS As Worksheet
Dim iLast As Integer
[Code] ........
Also, if there is a way to write looking for blank rows into the first block of code looking for "Legal:" that would be cool too.
If not, just deleting all the blank rows is good. Right now, I have to have the second block of code run twice to get rid of the remaining blank rows.
Is there anyway I can test / inspect a file before attempting to open it to check that it is indeed an Excel workbook?
I don't want to do it by file extension as that can / will be changed, but rather wondered if there was anyway to examine the ACTUAL file?
I'm wondering if all Excel workbook files start with a particular section of data or anything?
I've got a macro set up to copy a range of cells in one workbook, open another existing workbook, and then paste that range. So far so good.
However, I need the macro to also save a copy of that second workbook with a filename that is derived from the original workbook.
To be a bit clearer, let me give an example of how I'd like this to work. Let's say the original workbook is titled Pickle2014-03-14.Raw.xlsm, and I have a second workbook titled UploadFormat.xlsx. I want to run the macro in Pickle2014-03-14.Raw.xlsm, have the data pasted to UploadFormat.xlsx, then save UploadFormat.xlsx with the file name Pickle2014-03-14.Final.xlsx.