I'm trying to write code that looks at the range W(rowid):Z(rowid) and lists each cell value in a listbox. The row id is coming from the listindex of combobox1. I know the rowid part is correct and working because other operations in my worksheet are using it fine. Here's what I've written so far:
With Worksheets("DATA")
For I = 0 To 4
ListBox3.AddItem .Range("W" + I & rowid)
Next I
End With
In sheet 1 data 123. In sheet2 there is also data 123. In sheet3 will automatically data that is same 123 after reading sheet1 & sheet2. Any data with figure that is same in sheet1 and sheet2 will be reading in sheet3.
View 4 Replies View RelatedI would like to read data from Worbooks through VBA code and UserForm without opening them. Is it possibe?
I know I can read data from Workbooks without opening them if I write this formula directly into a Worksheet cell:
='C:Documents and SettingsUserNameDocuments[FILE_NAME.xls]Sheet1'!$A$1
The problem is it won't work for my task. I just need to analyze with UserForm Controls and For Each loops through many cells and many big Workbooks.
If I try:
Private Sub UserForm_Initialize()
Dim aaa as String
aaa = 'C:Documents and SettingsUserNameDocuments[FILE_NAME.xls]Sheet1'!$A$1
End Sub
OR
Without opening the file if I try:
With Workbooks("C:Documents and SettingsjohnMy DocumentsmProjectfilename.xls").Worksheets("sheet1")
For Each thing ...
....
Next
End With
In both cases it gives me - Compile error: Syntax error, Expected expression.
I can also say that I definately don't mistake path, workbook's or worksheet's name
------------------------
If you're not quite sure that my explanations are clear I wrote some more below:
Reasons i want this:
It's just a lot of traffic/memory to Open/Activate/Close many Workbooks/Worksheets from 10MB files for just taking one or a few values to an UserForm :->
What I do:
I'm using several Workbooks to manage data. One of them consists of the Macro code (ThisWorkbook.) and the rest of the Workbooks are just tables with data I use. On UserForm with controls such as (ComboBoxes, CommandButtons, ListBoxes, Labels etc.) I find, visualize, compare and copy just the data I need from other Workbooks to ThisWorkbook(on a new worksheet) :->
The idea of my task is:
1. I have 10 files, each of them 10-20MB with data.
2. Now, I'm making 1 file with VBA code which analyse these 10 files and displays the information in it.
The general idea is that these 10 files are well structured but have many columns and rows and it's not convinient each time I need to find 3 numbers I need to open 10MB file, scroll 5 minutes, copy/paste the numbers, close the file...i just want an UserForm with several controls to do this job
Each of these 10MB files consist of let's say about 10000 rows and 100 columns. Between these rowns and columns there's data (or empty cell). Everytime I use one of these files I need different information. So, if I use the formula you gave me, I should copy exactly the same Workbooks in one file but with formulas.
With the macro code I just go through these rows and columns and find the specific data I need with For Each loops. Everytime I need different data. With 4 ComboBoxes I just select an year, a name, a code (by rows) and another code (by columns) and I get the needful info.
This info could be in one file, could be in many files, it could be in HZ45 cell (file 1), it could be in C5 cell (file 2), the next time I would need file1,2,3,4,5 with cells GF45, J7, KK88, DR8, Y90 etc. etc. etc. So, the problem is that every time I need different information from these 10MB files, each of them consist of 10000*100 cells*10 files = 10 0000 000 cells which I have to write the formula you gave me and it will become one 100mb excel file, which is no good.
I need the function to be looped for all the values in the first column and I'm told I need to enter all the values in the first column into an array. how do I enter the values in a column into an array in VBA? The range of the rows in the column varies over the worksheets that I need this function for so the array cannot be of a fixed range.
View 7 Replies View RelatedI have two columns. In one people either saw Red, Blue, or Green. In the other people either chose One, Two, or Three.
I would like to get excel to read the first column, find all the times someone picked red, then look to the column next to it and see if they picked the number One. Then I would like it to count that situation. Then I want to do it for Two and Three. In the end I would have something that looked like this
---------One----Two---Three
Red-------5------8-------9
.
Blue-------4------5-------3
.
Green-----4------2-------6
Generated from something that looks like this
Red One
Blue Two
Blue Three
Green Two
Some kind of biconditional countif maybe
I'm using the following code to read in any number of CSV files into a single worksheet and concatenating them together
View 2 Replies View Relatedthe attached workbook, I am looking for M19, N19 & O19 to read from different locations depending on how many cells are called on when info is entered in P8.
View 10 Replies View RelatedI want to read all the folders names in a folder.
So for example c:win estfolder
And in test folder there are 5 more folders named test1,test2 etc.
I want to be able to give the path c:win estfolder and let VBA excel read all the names of the folders inside the folder testfolder.
I have found some methodes on the web, but those are for files like .txt etc.
The following code reads text one character at a time, and each character is determined either to be a blank space or to be any other character. If it's determined to be any other character, then the character is added to the active cell. If it's determined to be a blank space, then the active cell becomes the cell one column over. The idea is to have different words written in columns next to each other.
The sample text file I'm reading from reads: "text file". The file has only those two words. One space between them. No space before the first word, and no space after the second word. The following code compiles and runs. BUT the result is that the word "text" is in column k, not A. And the word "file" is in column J, not b.
I have a drop down list in my excel sheet that contains names of manufacturers. What i would like to do (using vba) is display some details of each manufacturer that will be copied from a different sheet.
So my question is, how can i read the string from the drop down list on one sheet and then copy a cell from another sheet.
I am building a data base. I have a tab where you fill out of a form and then click an activeX command button to write to the data base. I am wondering if there is a way to record how many times to button is pushed. Each time the button is pushed, I want to record the data from the form into a single row in the data base without writing over old data but I do not know how to read how many rows of data are already there.
View 9 Replies View RelatedI am using the find or search function but I need to read my text string from right to left (from the end of the string, instead of from the beggining). Is there a way to do this?
I have a spreadsheet with about 200 lines or records and I have added a checkbox to the end of each record. I need to the code to check the checkbox on each line to make a decision on how to process the data, but I do not want to read in a True or False value for 200 check boxes in every step of the loop. I think I'm close but I can't get the following to work...
This is basically what I want:
For i = 1 to x Step 1
IF me.Shapes("Checkbox" & i).Value = True Then
'Perform Action
END IF
Next i
I get an error when I hit the IF me.Shapes..... line
"Object doesn't support this property or method."
I have a table where sometimes cells are merged. To the right they are not merged, but I need to link to the value of the merged cell. How can I do it if I don't want to do it manually?
Example:
Say A1:A4 is merged, A5 is just normal. I want a formula using those values, say in B1:B5. Now just filling down the formula would reference A1, A2 and so forth. This is wrong.
Is it possible for VBA to read data from a clipboard so that it knows what sheet to paste the data to? Currently we run a financial tool and 2 reports are ran and I put in a bottom so users could easily just paste special values into the correct place. Sometimes the users forget if they copied the balance sheet data or income statement data and currently its not a big deal because there are no columns to the right of where it is pasting so it isn't screwing anything up and they just delete the data and paste it to the correct sheet.
I have thought of 2 approaches I am going to take, but started to wonder if one could read the data from the clipboard before the paste.
My 2 options would be:
Option 1. Paste data to newly created sheet and check if it is balance sheet or income statement and then move from newly created sheet to correct sheet and delete newly created sheet. The user wouldn't see any of this because I would disable screen updating.
Option 2. Learn how to use Undo within my VBA or keep the Undo cache with items in it as it typically clears when VBA is executed. That way if they paste it and it is wrong I can just undo the VBA paste.
Option 1 I am confident I can handle. Option 2 I can research and feel I could do, but if it comes a time when a sheet has data I do not want to paste over then this option is no good.
I would like to hear any comments or feedback on this and if it is possible to read data off the clipboard so it knows where to paste.
I need a little bit of help here ... I can read and process a .txt file from my Excel VBA program, but I am having trouble as follows :
The file I am reading contains a number of heading lines, followed by a number of data lines.
All of the data lines end in a date, so I can use the code ...
If IsNumeric(Right(MyDSRec, 1)) Then
... to pick up the data lines & ignore the heading lines. This is fine and works perfectly.
However, the .txt file that I receive is out of my control, and is prone to error, in that the data line occasionally has what I assume is a "Tab" character after the last date, which means that the line above does not recognise the last character as being numeric.
If you double click "06" at the end of each line on the example below, you should be able to see what I mean (in the .txt document, if I place the cursor at the end and press backspace, all characters at the end are deleted, back to the 06, so they are not individual spaces) ...
RAMSEY1129/0503/0603/0613/06
SHAKESPEARE 1102/0609/0609/0618/06
PREMIER603/0604/0605/0618/06
I am running into one of the strangest issues I have seen in a long time. I have a user defined type that I utilize in VB6 and write out to a binary file....
View 7 Replies View RelatedI am have a 4x2 table and would like to use the values in the table in numerous procedures. i feel the best way do to this is to record the table as an array and be able to 'paste' or call the array anytime i need to. However, i am unable to read the table into an array.
1. how the array works,
2. how i can read values into an array and
3. how can i 'paste' or call the array at a later stage.
how would you read consuctive dates as one
example
person is sick on 10/9/06 and on 10/10/06 . i want that to be read as one sick. now if the person is sick on 10/09/06 and then sick on 10/11/06 that would be read as 2 sicks.
what i need is the consective dates to read then one to read the dates that are not consectutive . giving a total for each one.
I'm trying to use the contents of a cell as a range in my code
Private Sub CommandButton3_Click()
Dim Target As Range
'the cell containing the contents i want
Range("P11").Select
Target = ActiveCell.Value
Range("P9").copy Target
End Sub
Does it read in the value as a string? If so how do i get around this?
i am trying to read the file name in a specific folder through macro of my EXCEL application. i wondered that it is reading that file name in all Lower case.
Ex: the file name in the folder is "Ford of Pompano.xls" when i am reading that name from macro, it is giving me as "ford of pompano.xls". Do i need to change any default settings in EXCEL so that it can read the files names what ever is presend in the folder
I need a formula that will be able to show what shift someone is on if the shift changes, for example. if one of my employees shift changes from and 8hr to a 10 hr on january 16th 2014 then I would like the raw sheet to show the old shift, the new shift and when the new shift started (date). the index formula I have will only show one shift even if there are new dates with a different shift. the formula pulls from the sheet name editor.
i really want the shifts to be different if it was change on a certain date then from that date the employee shift will reflect new shift but still keep the previous shift prior to the day the new shift started.
I've attached a sample workbook to show.
MDT Report December 201322(FILEminimizer).xlsx
SUM(IF(FREQUENCY(E10:E29,E10:E29)>0,1)). this is the formula I currently use to read employee numbers and it works when we just use the number i.e. 011004. When we use the full employee number with alpha characters it does not work i.e. ASMO011004. I have used helper cells to do similar, but am not wanting to do this way for simplicity reasons.
View 2 Replies View RelatedI have a .csv file that contains numerical data in the form of strings, much of which starts with leading zeros (i.e. "012345"). I double checked to make sure the .csv file has the leading zeros and that they are wrapped in quotation marks in notepad, and it does. However, when Excel opens the file, it must read those strings as numbers, becuase it chops off the zeros. Does anyone know of way to fix this? Can Excel read numbers as strings straight from a .csv file?
View 3 Replies View RelatedI'm using VBA to open several files and pull information from them into a workbook with the hopes of eventually outputting the information to a formatted text file that can be uploaded to a data transfer system that will fill out a form for me. The problem I am finding is that the workbook that I need to access has faulty VBA code that executes everytime the workbook is opened. I realize the easiest solution would be to fix the code that is faulty however the workbook that I am accessing is automatically generated for each product with the faulty code embedded requiring me to handle the error on the back end. Attached is my code and I attempted using error trapping to handle this issue but I've concluded that I either lack understanding of error trapping or the fact that the error is in a cascaded workbook complicates the error trapping code.
View 5 Replies View RelatedI am running a DOS command to create a directory listing of ONLY DIR files with the short dos filenames and the long filenames. The command used is
DIR /A:D /X /N /S > c:sampledata.txt
Once read into excel, I simply want to have a spreadsheet with the following:
Directory Path Name, DIR Short Name, DIR Long Name
Anyone have some code to already do this? I have attached the sampledata.txt file to go with the pseudo code.
I have a list of client names with their respective numbers on a sheet 1 and I have a list of jumbled client names with the names repeated in the next sheet 2. I would like to retrieve the client number from the data sheet and display the number for that respective client name in the second sheet
Sheet 1:
Client Number Client Name
1 David
2 Tom
3 Suzan
4 Richard
Sheet 2:
Client Name Client Number
Richard
Tom
David
Suzan
Richard
Tom
David
I want the program to retrieve the client number for the names from sheet 1 and display it accordingly in sheet 2. For eg:- it should retrieve Richard's client number as 4 from sheet 1 and display digit 4 next to richard's name in the client number column. I have attached the sample document.
When I read a CSV file into a recordset, the headers are not all being read. I get gaps in the headers for whatever reason. Attached below is the Function to load the recordset from the file. I have hard coded Hdr to "No" in the connection string.
Code:
Function QueryByID(tableName As String, fieldToQuery As String, Target As Long, Hdr As String) As Recordset
Dim strFilePath, strFileName, strQuery As String
Dim oFSObj, oConn As Object
Dim oRs As Recordset
Dim f, lastRow As Integer
[Code] .......
The below is how I output the resultset:
Code:
Sub OutputResultSet(aTable As String)
Dim row, col, i, j As Integer
row = 1
col = 1
ThisWorkbook.Sheets(aTable).Activate
[Code] .....
I have triple checked the CSV file and the first row in each file has definitely got headers. Some headers are loaded and output fine, others are just blank.
I have made an invoicing system that when opened reads a record number and also pulls through some data from an excel document on our server. The user then inputs his information and clicks submit and this will write to the excel document updating the invoice no by one for the next invoice the problem is that this is slow as when writing to the excel worksheet it has to open the document update it then close and save the worksheet on the server. I am trying to speed this up by using a access database on the server but I am having trouble trying to work out how to do this as I have never communicated with another type of document. I found some code witch I am trying to modify but It comes up with a 424 error:
Code:
Sub Auto_Open()
Set conn = Server.CreateObject("ADODB.Connection")
conn.Provider = "Microsoft.Jet.OLEDB.4.0"
conn.Open "\TWKKLNGNational Electronics DocumentsWorksorder DatabaseElectronics Notification system.accdb"
[Code] .......
I have a formula that reads the date in Colum B on 3 different sheets. The data on the seperate sheets is pulled from a SQL data base and formats the date wtih the time after it.
11-13-2006 10:47
My formula is looking at dates and subtracting them to give a total count of the occurance of the word "Yes" between dates.
My Formula:
=SUMPRODUCT(--('6200'!B2:B65000>=1/1/2006),--('6200'!B2:B65000