Free Microsoft Excel 2013 Quick Reference

VBA Code to extract data to csv file.

i think i put this in the wrong thread, so i have put it here.

I have a vba script that captures data from certain rows and columns and pushed the output to a csv file.
I am having problems creating an and/if statement to push data from one cell and combine it with data from another cell to the output.

I need to do a test where if data in Column B (vba script RC[-4]) = 1, then combine the data from Column E (vba script RC[-1]) to now read DP202876, else if RC-4 is greater than 1 then SP202876.

thanks in advance


I am looking for vba code to move data from one spreadsheet tab to another. I need it to copy based on greater than or equal to and less than or equal to a certain column. I saw other related threads with just specific values; I need a range of numbers.

Attached is an example of the before with the data download, and the after when I just manually move them over to each tab. The tabs noted GT100K or GT50K means in column I values over 100,000 or 50,000, etc.

So the code would pull based on column C or C & I from the data tab, and copy to the other tabs. After moved If you could sort the tabs by B&C too, that would be perfect. If this just pulls from the top row down, then you can ignore the prior sentence since it will be sorted before running the code.

I'm not sure if my files are working so here is kind of an brief example. If you can give me the base, maybe I could manipulate the vba for my specifics.

Example :
Account Var. Amount
45000 $105
45005 $50
56100 $25
79008 $125
67002 $25

The code would move the data (first 2 rows) in one tab for accounts 45000-56009. Then move to another tab for accounts 45000-56009 and variance amounts greater than $100 (just the first row). Move data from accounts 56000-56999 to a separate tab (copy the third row into new tab). Etc.

Let me know if you have any questions.
Thanks,
Preston

I am wanting to use VBA code to open a text file with the
import wizard. Does anyone know how I would invoke the
import wizard to open a text file in VBA? Any assistance
on this matter would be greatly appreaciated.

Can somebody help me with a VBA code for extracting data from Yahoo Finance?

Thanks!

In my workbook, there are 2 worksheets. One is a standard template that I need to populate data with from the second worksheet. In worksheet 2, the order of the columns will constantly change (but the column names will not) since I will be copying and pasting new files into worksheet 2. Also, worksheet 2 will contain columns that are not in worksheet 1. I will only need to extract data that matches the column heading in worksheet one.

Is there a vba code that will compare the column headings of worksheet 1 and 2 and if the heading exists in worksheet 2, then that matching column in worksheet 1 will be populated?

any help is much appreciated!

Hello everyone. On a monthly basis I have a procedure to extract data from a website and place that data into an excel worksheet. it does not take long to do, as there is a "button" on the website that you can press to "dump-to-excel". However I would like to use VBA code to inlcude this procedure into some macros I have already developed. The only "variable" in the procudeure involves the user inputing dates in the following format: dd-three letter month abreviation-YYYY. The web data is contained in a nice table in the center of the site. The number or rows varies, but the number of columns is fixed at 5. The intranet site uses IE. The following code sort of works, but as you can tell it does not prompt the user to enter a date. The code below is a good start, I think.

Sub WB()

URL = "http://cds.worldbank.org/Pages/CurrMain.aspx?rate_date=31-mar-2010"

Set IE = CreateObject("InternetExplorer.Application")
IE.Visible = True


'get web page
IE.navigate2 URL & Ticker
Do While IE.readystate <> 4 Or _
IE.busy = True

DoEvents
Loop

Do While IE.document Is Nothing
DoEvents
Loop


End Sub
I would like to save the table into a worksheet called CDS_Raw.xlsx. Any suggestions would be greatly appreciated. Note that the code above does bring the website open.

I am assuing I should declare a variable say fdate

and use that somewhere in the URL?

Hi,

I was wondering whether someone may be able to help me with my problem.

I have data in Sheet 1 that gets entered from a form. It has a range of issues and times in hours that it took to deal with those issues. Also entered from the form is the date the issues occurred and from this I have columns that have the week numbers and month names in.

What I am struggling with is to come up with the VBA code to move the data (the issues and time it took to sort them) into Sheet 2 and arrange them in 2 columns. I would like to do this either with a button or the change event of two combo boxes with the week number and month names in them. Ideally if there are duplicates of the issues then I'd like to sum the hours taken to sort the issues and show in a third column how many occurrences there were of that particular issue and show just the one issue name but even if I could get just the issues and times into 2 columns on Sheet 2 I would be happy, I could sort it manually from there if necessary.

At the moment on the UserForm I have 12 comboboxes that have regular/common issues and 12 textboxes for irregular/one-off issues. On Sheet 1 I currently have columns that count the number of regular issues with CountIf formulas (12 columns) and then on a separate Summary sheet I have SumIf formulas that sum these regular issues for each week and month. The calculations do noticeably slow the spreadsheet down but if I could get the VBA code to work I could do away with the calculations and just run the code when I need to produce the reports which would be so much better.

I have been trawling through different threads on here for quite a few days now trying to find some code that I might be able to adapt but I think my knowledge of VBA is not yet good enough. I have learnt quite a bit though I think, which is good, but I have to admit defeat in getting this right. I have attached an example file to show what I'm trying to achieve.

An easy one, I hope. I've searched the help files and data base for the answer to this one, but it eludes me.

I need the VBA code to delete the following file from the hard drive, without displaying the standard Microsoft confirm delete warning:

c:maindirdatfilesdatfile1.xls

Thanks in advance for a speedy response

Ted.................

hi,

Is there a way to write code in VBA to extract data from server ?
Is it possible to do it in Excel(VBA) ?
I have attached sample how to extract the data from server ?
Currently been doing it manually and its time consuming. please help if can as need it urgently.
Any help much appreciated. thanks in advance.

This thread is an extension of another in which Ozgrid member Barb-B helped create working code to extract data from files in a user selected folder. I wish to extend this so that data is extracted from all files within the user selected folder AND subfolders.

Here is a link to the Solved thread:
http://www.ozgrid.com/forum/showthread.php?t=153891

Here is the working code to extract files from the root folder.

	VB:
	
 CollectDataBits() 
     
    Dim fname As String 
    Dim fpath As String 
    Dim r As Long 
    Dim c As Long 
    Dim clarray 
    Dim datasht As Worksheet 
    Dim destsht As Worksheet 
    Application.ScreenUpdating = False 
     
    With Application.FileDialog(msoFileDialogFolderPicker) 
        .AllowMultiSelect = False 
        If .Show Then fpath = .SelectedItems(1) & "" Else Exit Sub 
    End With 
     
    clarray = Array("B8", "B9", "B10", "B12", "F8", "G8", "C2") 
    r = Range("A" & Rows.Count).End(xlUp).Row 
    Set destsht = ActiveSheet 
    Application.DisplayAlerts = False 
    For r = 2 To r 
        fname = fpath & Cells(r, 1) 
        Set datasht = Nothing 
        On Error Resume Next 
        Set datasht = Workbooks.Open(fname, , True).Sheets(1) 
        On Error Goto 0 
        If Not datasht Is Nothing Then 
            For c = LBound(clarray) To UBound(clarray) 
                destsht.Cells(r, c + 3) = datasht.Range(clarray(c)) 
            Next c 
            datasht.Parent.Close 
        End If 
    Next r 
    Application.DisplayAlerts = True 
     
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
So essentially what i want to happen is the user to select the folder and all files in that and sub folder are opened, data extracted and closes. Any help would be appreciate.

I need some help for VBA macro for Ecxel: just to extract data.
There is one workbook with multiple sheets; I just want extract some data from this sheets and put this data into separate worksheet, as table.
i've attached the sample.

Data which need be extracted is inside red frames.
The only problem is that, probably, some the location of some data may differes a little: i.e. in cells I35, I38, I39 -
due formattings problem it can migrate around this cells(near ~I36, I39, I40). So I just want that macro show empty cell in result table(not error) if find this incorrect (empty) cell in some workshets.

Thought it looks not difficult, i still can no solve this myself.
Thanks.

I am trying to use a dynamic VBA code to import data into a xcel sheet from another sheet. I currently have it working but in the code I have the directory and the file name listed for the file to be imported. I would like to change this to a cell referance inside my sheet so that I can make changes to the name of the file or the directory location and the VBA code would use the cell reference as the source data.

I hope that makes sense.
Thank you
Ken

How can I get VBA code to locate and open only the latest edition of an .xls or .doc file within a folder ?

We use Excel 2003

The Excel files are in one path and the Word files in another.

Almost daily there is a "latest edition" of files placed in a folder along with all of their predecessor file editions. At times there are 2 or 3 revisions within one day.

To do this manually I double-click on the "Modified" column heading to bring the latest file to the top and then I open it. Once open, I input a header, search for certain numbers or text and then print the page that meets the "find" criteria.

I have a daily schedule that I fill out by using my Data/Form/New method from the menu bar. I have increased the number of items to enter to 36, exceeding the maximum field capacity of 32. Is there any way to write VBA code to do the same thing the Data/Form does? I would like to be able to start at the first available blank row, fill in the 36 information items, and go on to the next row or close out of the procedure. The VB method would actually be set up to prompt the user, making it easier to fill out the forms, but I don't know where to start to allow me to fill out an unlimited number of rows, beginning at the first blank one and going one row at a time from there on. Any suggestions?

Thank you
marley

Hello. I am new to this, and trying to figure out a VBA code that will work for my set of data. I have attached a much smaller version of the data I am working with.

I am trying to make a 'tool' sheet in Excel that can search all of the data from the 'raw data' sheet and return specific cells from that 'raw data' sheet. Specifically, in the attached workbook, I want users to be able to enter values into the yellow highlighted cells (Test #s and Class rank) on the 'tool' sheet, press the "Go" button, and from there I need the VBA to:

1. Go to the 'raw data' sheet and first search for the row in Column A that matches the Test # given in the yellow highlighted cell. Secondly, if the correct test # is found, I need the code to also search in Column B for the class to also match the value that was entered in the yellow highlighted cell on the 'tool' sheet.

2. Once both columns match, I want the VBA code to go over to column E and to copy the cell directly across from the first row that has both correct values, and also to copy the second cell. For example, if test #1 and freshman were entered in the yellow cells, the code would find that row 2 matches the requirements, so it would go over and copy E2 and E3 on the sheet.

3. Lastly, I want the code to go back and paste the 2 copied cells in the specified green highlighted cells on the 'tool sheet'.

4. In the case that the user enters something that does not exist, such as "senior" in the yellow class cell, I also need to code to come back and say something like "invalid entry".

Thanks so much for any help you can give!!!!!
Mindy

Hi,
I am not an expert when it comes to VBA programing; however I often use the macro recorder, and delete items in the code that seem unnecessary.
Below is the code that I have.

The problem that I am facing is that the file name "NAV SHEET 070910" changes everyday.

The 070910 represents the date. The macro will not work unless the file is named "NAV SHEET 070910". How can I change the code to accomodate the changing file name.

Thanks in advance.
Windows("NAV SHEET 070910.xls").Activate
    Range("BA3").Select
    Selection.Copy
    Windows("Trial Browse.xls").Activate
    Range("F8:F24").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Application.Run "'Trial Browse.xls'!Start_TTrial"


Hi,

I'm currently using the following code to copy data from a range of worksheets:

Code:
For Each ws In ActiveWorkbook.Worksheets
        If ws.Name  ActiveSheet.Name Then
            ws.Range("A4:V303").Offset(0).Copy
            With Range("A65536").End(xlUp).Offset(1, 0)
                .PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
                              False, Transpose:=False
                .PasteSpecial Paste:=xlFormats, Operation:=xlNone, SkipBlanks:= _
                              False, Transpose:=False
            End With

        End If
    Next
this code works fine, but I need to modify it so that it only copies data from specific worksheets. In this case I only want to copy data from worksheets who's name starts "Weekly Call"

Can anyone help me with modifying this code?

Regards

Mark

Can someone help me with a code that will :
1) extract data from a excel file to create a formated text file with specific keywords in it

Excel Format:
PartID Location Date QTY
1 abs 1/1/2011 12

Text Format :
PRODUIT P L D NB
1 abs 1/1/2011 12
2) runs a DOS application with the created file. This application will generate some output

Thank You

I would appreciate if someone could help me with the following question. Thanks.
I have a big database from which I have written VBA codes to extract choice data. I then save the output file as “Volume.xls”. Now I want to do further analysis on the output data, for example, getting the subtotals. I have given below the macro that gets me what I want. Now my question is, I want to write the statement
Windows(“Volume.xls”).Activate
in such a way that it will recognize any output file without me having to name it “Volume .xls” before running the macro every time.

Sub RunAllSubtotal()
Windows("Volume.xls").Activate
Run "SubTotalByState"
Run "VisibleCells"
Run "CopyVisibleCells"
Run "TestFormat”
End Sub

Thank you very much for any solution to my question.

I am attaching the files for your reference.

MASTER FILE

http://www.box.net/shared/dat9pgal4o

PAF FILE

http://www.box.net/shared/itz4dbg48h

PSI FILE

http://www.box.net/shared/cdj0szqhj3

My datas are in the path "C:Temp"

I have three files named 0001.PAF, 0002.PSI & Master File.xls

The first two files (name of the file will be different each time) are basically CSV files but their extention changed from CSV to PAF & PSI respectively.

0001.PAF has the fields from column A to Column J.
The required fields in the context are Column B,E,F,G,H

0002.PAF has the fields from Column A to Column J (in which column E,F,G,H,I blank)
The required fields in the context are Column B & C

POINTS:

1. PAF Files contains the salary details of the employee Column G+H is the total salary of the employee
2. PSI file contains the name of the employee
3. There is 14 digit no in these two PAF & PSI files which is the employee id and is required to identify the correct employee and correct salary by using VLOOKUP function

My task is to import the data contained in two files that is 0001.PAF & 0002.PSI (basically CSV file only the extension different) into sheet1 and sheet2 of Master File.xls based on the follwoing conditions

1. The data should be imported after using the VLOOKUP function, so as to ensure the accuracy of the data
2. Employee Name should be sorted in the alphabetical order
3. Salary amount should be the total salary ie G+H from PAF FILE
4. Total Salary should be splitted into currency denomination of 500,100,50,20,10,5,1
5. The data should have Header (The co name in the header is there is the last name in PSI file)
6. The data should have Footer (The data always: SALARY PAYOUT by: XYZ CO LTD
7. The alignment and format of the data should be as per the Master File

Kindly note my Output file Master File.xls for your refenece

Thank you all for going through my post. Awaiting for a solution. Thank you

All,

I currently have a VBA macro that I have created. I now need to change this code from what it is, and make it return a new set of data. Here is the current code:

Public Sub Import_TCR_Info1() 
    Dim FName As Variant 
    Dim Sep As String, MyPath As String, LineString As String, TCRDate As String 
    Dim OfficeRange As  Range 
    Dim lngLineCount As Integer 
    Dim MyValue As Double 
    Set OfficeRange = Range("Offices") 
    MyPath = "E:DataRCCTCR ReconTCR Bag Deposit Reports" 
    TCRDate =  Format(Range("TCRDate"), "mmddyy") 
     Sheets("De La Rue Location").Activate 
     
    Range("InfoArea").ClearContents 
    Range("InfoArea").Cells(1, 1).Activate 
     
    For Each cell In OfficeRange 
        SourceFile = MyPath & TCRDate & "_TCR_" & Format(cell.Value, "0##") &
".csv" 
        ActiveCell.Value = cell.Value 
        If Len(Dir(SourceFile)) = 0 Then 
            SourceFile = "" 
            ActiveCell.Offset(0, 1).Range("A1:H1").Value = 0 
        Else 
            lngLineCount = 0 
            fn = FreeFile 
            Open SourceFile For Input As #fn 
             'moves line by line until it reaches the last line (EOF = end of file)
            Do While Not EOF(fn) 
                Line Input #fn, LineString 
                lngLineCount = lngLineCount + 1 
             Loop 
            ParseDelimitedString LineString, "," 
            ActiveCell.Offset(0, 1).Value = MyArray(5) 
            ActiveCell.Offset(0, 2).Value = MyArray(6) 
            ActiveCell.Offset(0, 3).Value = MyArray(7) 
            ActiveCell.Offset(0, 4).Value = MyArray(8) 
            ActiveCell.Offset(0, 5).Value = MyArray(9) 
            ActiveCell.Offset(0, 6).Value = MyArray(10) 
            ActiveCell.Offset(0, 7).Value = MyArray(11) 
            ActiveCell.Offset(0, 8).Value = MyArray(12) 
             
        End If 
        ActiveCell.Offset(0, 9).FormulaR1C1 = "= SUM(RC[-8]:RC[-1])" 
        ActiveCell.Offset(1, 0).Activate 
         
    Next 
    ActiveCell.Offset(1, 0).Activate 
    ActiveCell.Value = "Total" 
    ActiveCell.Offset(0, 1).Range("A1:I1").FormulaR1C1 = "=SUM(R[-" & OfficeRange.Rows.Count + 1
& "]C:R[-1]C)" 
End Sub 

Function ParseDelimitedString(InputString As String, SC As String) As Variant 
    Dim i As Integer, tString As String, tChar As String * 1 
    Dim sCount As Integer, ResultArray() As Variant 
    tString = "" 
    sCount = 0 
    For i = 1 To Len(InputString) 
        tChar = Mid$(InputString, i, 1) 
        If tChar = SC Then 
            sCount = sCount + 1 
            Redim Preserve ResultArray(1 To sCount) 
            ResultArray(sCount) = tString 
            tString = "" 
        Else 
            tString = tString & tChar 
        End If 
    Next i 
    sCount = sCount + 1 
    Redim Preserve ResultArray(1 To sCount) 
    Redim MyArray(1 To sCount) 
    ResultArray(sCount) = tString 
    MyArray = ResultArray 
     
End Function
Basically we have about 80 files that we get each day, and this macro opens all of the files, and gives us the totals in each file. The file names are pretty constant each day, except the date changes on each one (the file is named for each machine).

What I need for it to do now, is to take all data from the file EXCEPT the last line, and put the info into a new tab of the current worksheet. As it takes the data from file B, it would append to the data in set A, and so on so that the worksheet will have all of the data from all of the files in it. The rest of the macro I can write on my own ( sorting the new data set, and subtotaling it by machine number), but I cannot seem to be able to alter this macro to return the data set I need.

Any thoughts, hints, tips, anything at all would be majorly appreciated. Thank you for any help that you can be.

Also, if modifying this code is not the way to go, what are your suggestions?

I have a spreasheet that has email addresses in Col N and the months in row 1. I have a drop down that when I select a month , the column with the applicable month will opeen i.e be unhidden

when A double click on commission the amount in the same row as commision must be extracted as well as the branch code that is in Column A 5 rows above commission

I asked someome to assist me in with the code, but when I double click on Commission, I get run time error 13, type mismatch and the following code is highlighted

zMonth = CDate(temp)

See code & sample data below

Your assistance in resolvinng the problem will be most appreciated


	VB:
	
) 
     
    zRow = Target.Row 'e.g. 21
    temp = "a" & zRow 'e.g. "a21"
    If Range(temp).Value  "Commission" Then Exit Sub 'not on a Commission row
     
     '**************************************************
     'COMMISSION ROW DOUBLE-CLICKED, PROCEED..
     
     'FETCH EMAIL ADDRESS..
    temp = "n" & zRow 'e.g. "n21"
    zMailTo = Range(temp).Value 'e.g. "paul@eci.co.za"
     'test for missing email here, if required etc:
    If Range(temp).Value = "" Then 
         'missing email addess, display message here..
        Exit Sub 
    End If 
     
     'FETCH COMMISSION MONTH..
    zCol = Target.Column 'e.g. 6 ;(= column [F] )
    temp = Cells(1, zCol) '> date is in row 1 (or row 5??)
    zMonth = CDate(temp) 
     'test for missing zMonth here, if required etc.
     
     'FETCH COMMISSION AMOUNT..
    zAmount = Target.Value 'cell that was double-clicked
     'test form missing amount here, if required etc.
     
     'FETCH BRANCH NAME..
    temp = "a" & (zRow - 5) 'e.g. colA, 5 rows above clicked row
    zBranch = Range(temp).Value 'e.g. "Br2"
     'test form missing amount here, if required etc.
     
     'assume all data is present and tested, so prepare email:
     
    prepareEmail zMailTo, zMonth, zBranch, zAmount 'NOTE:in same order as email routine
     
    Cancel = True 'ignore normal double-click-edit-cell
     
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
Commision Template.zip

Hi,
Looking for VBA code that externally links CSV files for MSAccess. Thanks!!

I need to import CSV files that contain over a million entries and importing them using the code below works but the database file becomes very large and the limit is 2. something gb.
DoCmd.TransferSpreadsheet acImport, 8, "MeterFileTbl", MeterFilePath, True, "A1:C2"

I have the code for linking XLS files but can't find the VBA code for the CSV format... Thanks in advance for your help!


	VB:
	
 
Set myDB = CurrentDb() 
stSource = "Sheet1$" 
 'stConnect = "Excel 5.0;HDR=YES;IMEX=1;Extended Properties=text;FMT=Delimited; Database=C:Documents and
Settings202968DesktopMySheet.csv"
 
Set tbl = myDB.CreateTableDef("mySheet") 
tbl.Connect = stConnect 
tbl.SourceTableName = stSource 
myDB.TableDefs.Append tbl 
LinkExcel = True 
Exit_: 
Set tbl = Nothing 
Set myDB = Nothing 
Exit Function 
Err_: 
LinkExcel = False 
MsgBox Err.Description 
Resume Exit_ 
End Function 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines


Hello.

As you can see from an attached simplified example.

I am trying to create a VBA code that will extract certain information from a pivot table and put this into a table on another sheet.

In the simplified example I am trying to get all the Male (M) entries in the pivot table into a table titled 'Male Table' How do I go about doing this...?

I also have a problem as when the pivot table gets updated with more data, the number of Males entries will go up etc, and therefore data will be in different cells compared to the previous week. So I need the coding to pick up all male entries within the pivot table, regardless of their cell reference, and copy the data across into the 'male Table'

Please advise as I have been struggling with this for some time now.
Thanks for your help in advance

Regards

Carl

Dear Excel Gurus,

I have an issue with vba code, where I need some help from start coders like you!

I have put forth my requirements below and attached the sample file here "Sample.xlsm":

There are two sheets, "WorkshopRegister" and "Data" sheets, which need to be synced and data collated for print purpose. I have three Workshops in which 4 employees work everyday. 1 Engineer, 2 Mechanics and 1 Helper. If anyone takes a leave they'll update the "Data" sheet for that day as "Leave". All I want to do is, for everyday, I need to get the Names of all the employees who are available and mark as Unavailable if anyone is on leave.

I used a 2D Lookup formula to solve this. But I have a few more conditions which I am unable to solve with formulas.Sometimes there will be more number of employees per role (3 mechanics in Workshop2). In such a case the 3rd Mechanic's name should be entered in "Additional Column" for that role (i.e if he is not on leave)In some workshops, there might be no "Engineer", so it should also be marked as "Unavailable".I am going to VBA because, I will be copy leave sheets, change date and re-sync every week. I'll also be making changes manually and print the sheet. So formula will be overwritten.

Please help me with a possible solution to this matrix.

Thanks in advance,

Regards,
Ravi.

PS: Actually I have data for two garages each with 3 workshops. But I'll be able to manage writing the loop