VB:
Sub CreateFlat()
Dim wsData As Worksheet
Dim wsNew As Worksheet
[Code].....
In 2006 posting, this code was presented to the Forum. It works perfectly for a one dimension Crosstab. I have 4 dimensions that I need copied to the output.
I have attempted to modify the code, but nothing appears to adjust the pull of more than the first column of dimension data.
see the attached spreadsheet for the Crosstab data and desired output.
Attachment 49027I have an excel data dump of the organizational hierarchy out of ERP system that I need to convert to a flat file pivot table ready format.
The catch is that my lowest level always has to be a department meaning that a dept should be a last column however in the ERP not all the departments are mapped to the lowest level layer i.e. I might have Layer 1, Layer 2 dept or Layer 1 Layer 2 Layer 3 and then dept mapped to 3. Attached is the sample of the file.
I receive a flat text file every week which I would like to grab with excel and extract only the data I need and enter the data into separate cells and loop until I reach the end of the flat file. I got a subroutine written that allows me to open my text file and it will enter all the data however I need to know how to parse only the stuff I need and enter it into the right cells and loop until I reach the end of my text file. Here is what I have so far:
Sub testFSNew() Dim fs As Object ' scripting.filesystemobject Dim txtIn As Object ' scripting.textstream Dim strFile As String 'File Name Dim strLine As String 'Current line being read.
[code].....
Now so far this opens the text file and dumps all the data into an excel spreadsheet however when I say all I mean it dumps everything into the first cell and does not separate it, the following is an example of the text in the flat file. I will only put in the first 5 rows because their is 5000 rows in the real file.
Ok so the first problem is I don't need the first line it's a header line and if you will notice everyline of the file ends with either a 5 or a 4 but it is information about each employee, so the next line would end in a 5 and that would be the beginning of the next employee.
P.S. I noticed in the preview post that this message board truncated my flat file data, so keep in mind that each line is indeed 1 line ending in either 5 or a 4
I have a series of files in *.ew7 format (i know its a random program)
I have some code which i can run which will simply open the relevant file and save it as xls.
The files are saved in multiple folders in format "yymm" eg: 0910 - oct 09.
inside each folder there is a ew7 file for each day in rthe format: "01ddmmyy" eg 01271009 = 27th oct 2009.
I have folders going back as far as may 2002 (in folder 0205) and i need a way to convert these all into xls format and save them in the same location.
In the first colum there are sub-columns that i need to reorder into flat format. This would be easy if all sub-columns were from the same size, but this isn't the case.
I think that I need to use a macro that finds the records on the colum, based on each title (A, B, C, D for the illustrative example attached). and then paste them into a new column.
I've never worked with arrays before but after looking through some books, would like to attempt a crosstab table. I currently have data in cells A1:K8316. Row 1 consists of my headings, which include Team (column K), Ind/Dir (column I), and Hrs (column G). There are a total of 25 different teams and I would like to sum their hours for both direct and indirect. (The columns for Team and Ind/Dir are based on vlookup tables).
I've tried some of the steps I'm finding in the book but, unfortunately, they're assuming I know how to even start and what to select - which I don't.
how a crosstab query works in ms access. Below is a visual. IN my source material, the columns are Category, Commodity, Supplier, and Dollars. I need to be able to get only the top 5 suppliers in descending dollar rank in the columns adjacent to the pivot table, as displayed below. Try as i might, i have been unable to get this to work using a pivot because i don't know how to limit the supplier columns to only the top 5.
P123182.63323.393225.873299.541635.611641.7 1615.983304.913313.791637.02Totals52097.561575.5561750.5864889.2554803.6754775.2661905.5465112.9563407.6266701.1861598.34 The table supplied, starting at B8 demonstrates one of 8 worksheets in the spreadsheet... of which I would like all exported to 8 named worksheets in a new spreadsheet in the list format as:
Asset | Date | Value
I need to ignore the Totals column and row, The table can grow or reduce the assets and grow the days of the month, starting again at the begining of each month. I have to run a report each day of the month.
I have a crosstab query in Access that groups by month and year. In Excel 2003, I have a query that pulls in the crosstab query.
At the time, I couldn't just pull in the Access table. For the other Access tables and queries, I could go to Data >> Import External Data >> Import Data >> select the database as a source >> select the table name.
However, to get the crosstab query, I had to go to Data >> Import External Data >> New Database Query >> MS Access Data Source* >> browse to my database >> select the crosstab query name >> select the data I wanted.
Since I wanted all the data, there did not seem to be a difference. But now that new dates are being added to the database, there *is* a problem. When I wrote the query, I only included the months available -- up to June 08. Now that there is data in July 08, that month exists as a heading in the crosstab, but Excel's query doesn't know to pull it! I have to manually edit the query to add the new month.
So my question is... is there a way to do this automatically? I have two crosstabs in five different spreadsheets, making a total of ten manual updates I have to remember to do each month.
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.
I'm trying to write a function that I can pass an array along with the data I want added to that array. The function will resize the array, put the data into it and return the new array. Example:
Function AddNewDataToArray(MyArray As Variant, Arg1 As String, Arg2 As String, Arg3 As String) As Variant 'If no elements have been added Redim Preserve MyArray(1 To 3, 1 To 1) 'Add the first record of data to the array.
'If there is already data in the array Redim Preserve MyArray(1 To 3, 1 To UBound(MyArray, 2)) 'Add the next record of data to the array End Function
As 2 dimensional arrays are Base 1, the challenge I have is getting it to resize (redim preserve) the array when it has to add the first record. I thought I could just use an IsArray function to test if there had been any data added. If not, then it would run the following...........
Currently I am building a class to keep track of entries I have made during the macro execution. Thus far I have:
Private Type Memory MemoryArray() As Variant End Type
Private Sub Class_Initialize() Redim MemoryArray(0) As Variant End Sub Public Property Let AddToMemory(Object As Variant) 'memory is empty If UBound(MemoryArray) = 0 And MemoryArray(UBound(MemoryArray)) = "" Then MemoryArray(0) = Object ..............
how to word it but if someone understands then please help. I have two excel data files namely Book1.xls & Book2.xls. Both files have different data in it. Both files contain macros. When these macros run the files become **FINALIZED** version.
Originally, I get the above files in my email as txt. attachments. I then move these two txt files to my desktop in a folder called Folder-1. Then I open these files as an Excel and save them.
Basically, I need to know if two txt files are sitting in a folder-1 on my desktop. What can I do or what can I clik that....those two text files get converted into excel automatically, including running that macro I talked about in the above paragrah.
To put it differently, if I have two txt files Book1.txt, Book2.txt in a folder, how can I automatically create an excel **FINALIZED**version which sits right next to their txt version.
I am trying to write a function that grabs the value of some cells in a column and returns a String array with those values. I am getting a compiler error on the line where I assign a value to an array element. It appears to be assuming that rather than an array reference, I am trying to do a recursive call to the function. The error is "Function call on left-hand side of the assignment must return Object or Variant." I would have done this with by assigning the range directly to the array but the data is in a column rather than a row; don't know of a more elegant way to do it.
Public Function projectList() As String() Dim c As Range Dim i As Long For Each c In Range("FirstProject"). CurrentRegion Redim Preserve projectList(UBound(projectList) + 1) i = UBound(projectList) projectList(i) = c.Value ' *** Compiler error occurs here *** Next c End Function
I tried many ways to convert a CSV file into a formatted Excel (.xls) file via VBA. I have a file with 5 lines (header included) and about 10 columns (delimited by commas).
How can I format it via vba on button click action?
I have a spreadsheet with thousands of lines of code. Each row contains a complete code that needs to either be converted/pasted to a new .txt or .xml. Until now, just copying and pasting each line into a .txt file was necessary but there has to be a way to automate this. I would love to know if it's possible to extract each row(technically it is only a single cell per row, so its just a really large single cell) and add it to a .txt or .xml file?
>I am trying to convert a Lotus file over to Excel, and am having some trouble >converting an error handling dget function. > >=IF(ISERR(DGET(Databaseread,"Name","GROUP >ID"=GroupNumber)),VLOOKUP(GroupNumber,Databaseread,4,FALSE),DGET(Databaseread,"NAME","GROUP ID"=GroupNumber)) > >This is the function that was used in Lotus; it returns the name of a >company by looking at the ID number. I need to keep it as pure as possible to >the Lotus file. ....
Lotus 123's @DGET (and other database functions) are much more sophisticated than Excel's counterpart functions. 123's can use criteria expressions in the function calls. Excel's require criteria ranges.
In this particular case, there's no need to use DGET at all. There's a single criterion term, so VLOOKUP is sufficient. If the "Name" column were the 4th column in Databaseread, then try
=VLOOKUP(GroupNumber,Databaseread,4,0)
Explanation: it appears you're just trying to find a particular group number. DGET (and @DGET in 123) returns an error if there's more than one entry. VLOOKUP returns the first matching entry. You're formula makes it clear you want either the only matching entry or the first matching entry. However, when there's only one matching entry it's also the first matching entry, so VLOOKUP alone would have returned the desired result.
I suspect you have other formulas that are more complicated, but you believed the formula above would be a reasonable sample to provide. Not so. If you have more complicated D-function calls, show them, not the simple ones.
Converting excel files into fully functional standalone and interactive web applications/dashboards? I have only worked with spreadhseetconverter before it converts excel files into interactive calculators but lacks the features which are available in the standard dashboards like gauges and widgets and the rest because it only converts the standard excel charts. I wonder if you have encountered a product which can converts excel files into fully functional interactive dashboards?
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.
