Excel VBA to Export Data to MS Access Table - Extended

I'm trying to use the method that was described in one of the other threads that I saw posted on stackoverflow here.

When using the method that was described in that thread (that got the green check) I'm getting an error when running the code. The error pop-ups up a blank message box with no contents.

A couple of things to mention:

(1) I've made sure to select and activate the Microsoft Access 14.0 Object Library in Excel.

(2) I am running the sub procedure from my database worksheet in Excel.

(3) I am then running the AccImport procedure within my code procedure from my wizard worksheet in Excel (separate worksheet).

---

EXCEL SPREADSHEET SETUP

I can't use screenshots as of yet as I am new to the community but the database worksheet field range is setup as follows.

B1 (Occurrence Date), C1 (Machine), D2 (Cell), E2 (Status), F2 (Issue), G2(Preventative/Corrective), H2 (Assigned To)

B2 (15-APR-2015), C2(machine1), D2(cell1), E2 (0), F2(Test), G2 (Corrective), H2 (nameexample1)

---

ACCESS DATABASE TABLE IS SETUP AS FOLLOWS:

Table Name: MaintenanceDatabase

ID, Occurrence Date, Machine, Cell, Status, Issue, Preventative/Corrective Assigned To

Here is the code that I am running from the Database worksheet in Excel:

Sub AccImport()

    Dim acc As New Access.Application
    acc.OpenCurrentDatabase "C:\Users\brad.edgar\Desktop\DASHBOARDS\MAINTENANCE\MaintenanceDatbase.accdb"
    acc.DoCmd.TransferSpreadsheet _
        TransferType:=acImport, _
        SpreadsheetType:=acSpreadsheetTypeExcel12Xml, _
        TableName:="MaintenanceDatabase", _
        Filename:=Application.ActiveWorkbook.FullName, _
        HasFieldNames:=True, _
        Range:="Database$B1:H2"
    acc.CloseCurrentDatabase
    acc.Quit
    Set acc = Nothing

End Sub

Code Snippet from other Worksheet Object that Runs AccImport:

Public Sub DeleteSelectedRecord()
    Dim CurrentSelectedIndex    As Integer

    ' Assign the currently selected index to CurrentSelectedIndex
    CurrentSelectedIndex = [Database.CurrentIndex]

    ' Move the ListBox Selector
    If [Database.CurrentIndex].Value = [Database.RecordCount] Then    
'Last item on the list
        [Database.CurrentIndex].Value = [Database.CurrentIndex].Value - 1
    End If

    'Copy to Access Database

    Database.AccImport

    ' Delete the entry
    Database.ListObjects("Database").ListRows(CurrentSelectedIndex).Delete

End Sub

Hopefully someone could shed some light into why I'm getting an error.

Thanks in advance for any help.

Cheers,

Brad

Excel Trap a Window close?

Is there no way in Excel VBA to detect if a user attempts to close a (certain) window? There is a deactivate event, but that's not the same thing, neither is closing the entire workbook. I'd like to trap an attempt by the user to close a specific window.

need help for pulling website tables to excel

hey guys im new to this and i am having a real hard time finding a way to pull tables off the website to excel. the website that i have access to has a login system with a one time password(OTP) which will be sent to my mobile. is there a way to do it so that i can automate the login and pause for me to enter my OTP then continue the pulling?

i have tried pulling tables off other open websites but this OTP is making it really difficult to work around.

thank you for your time and i really appreciate it if you could help me.

Date Sorting in Excel PivotTables

I have a PivotTable that has a date field in it, call it Mydate.

I have put this as a column label and then grouped the label by Years, Months and Days.

I then have put the years and days in reverse order by clicking on the label and then choosing to Sort Newest to Oldest.

If I then expand the month to show the days and then try to sort the days, the sorting is messed up....it treats the days above 10 as coming before day 2, 3, etc.

Here is what it looks like in the row label section:

    -2015  
         -Apr
             9-Apr
             8-Apr
             ...
             2-Apr
             1-Apr
             16-Apr
             15-Apr
             ...

Is there any way to fix this in the PivotTable or do I need to make an additional "Day" column to get this to work properly?

EDIT: These are properly stored as dates and the sorting works properly if I Ungroup the date field and then sort on just the dates without Years or Months.

How do I call up data from multiple excel files into one constantly updated file?

EDIT: I think this question belongs over at superuser not here at Stackexchange.

What I would like to do is have a single excel file that calls up data from every excel file in a given directory. Specifically if I have a time sheet excel file from multiple people working multiple different job numbers I would like to have that data populated in a single file for everyones times. The directory where the files are stored would be updated weekly so I would want the "master" excel file to reflect the weekly changes automatically...hopefully. Is there an easy way to do this that I would be able to teach someone else?

How to import an excel file into a database table as binary type by using SQL Server Management Studio

I have an excel file. I just would like to import the excel file into a table ExcelFiles without using c# code.

ExcelFiles has 3 columns

ExcelId, ExcelName,    ExcelFile
int       archar(50),  varbinary(max)

I would like to import excel file into this table through SQL Server Management Studio without using c#.

Is this possible?

Generating String from Excel Sheet

I have an excel with three columns and three rows like below:

Field1,Field2,Field3

Row1Value1,Row1Value2,Row1Value3

Row2Value1,Row2Value2,Row2Value3

Row3Value1,Row3Value2,Row3Value3

I want to write a VBA Macro to generate an output like below:

Field1=Row1Value1,Field2=Row1Value2,Field3=Row1Value3
Field1=Row2Value1,Field2=Row2Value2,Field3=Row2Value3
Field1=Row3Value1,Field2=Row3Value2,Field3=Row3Value3

Is there an easy way to do this?

Excel merging two files based on a variable

I want to write a program that will match variable names in two separate workbooks, than copy all information from variable until page break from both workbooks into a new work. Each workbook has multiple pagebreaks not sheets. For example:

Workbook A (Variable = X)    
Persons          Name  
X                    Bill  

Work Book B  
Persons          Nickname  
X                   Billy  

New Workbook  
Page 1  
Persons          Name  
X                   Bill  

Page 2   
Persons          Nickname  
X                  Billy  

I was using the code at this site to merge the two selected workbooks, but I cannot figure out how to match by name and than copy to page break. Can anyone have suggestions or can help direct me? Thank you!

Create power point using excel macro

I have an interesting problem I am unsure of. I have not worked with power point and have little excel macro experience. I have found many similar issues to mine but none of them quite fit the bill. I am helping my local charity with a fund raiser and need a way to make a triva sort of game. The game will be displayed with powerpoint, and all the questions, choices, and answers are in an excel sheet. The way it is laid our is one question per row, and the columns are: Question, options, answers and category.

I have managed the category sorting quite easy enough, but now I need to somehow work with creating power point slides in such a way so that the question is the title, with the options being the content, and then the following slide is the answer to that question. Therefore each question creates two slides, a question and answer slide.

Example row (| denote column):

Which of these was an italian sculptor? | Michelangelo, tintoretto, da vinci, galilleo | michelangelo | Art

So the result would be a side with title "Which of these was an italian sculptor?" and content a) Michelangelo, b) tintoretto, c) da vinci, d) galilleo

the following slide simply being "Michelangelo"

Hide/Show newly created tab in Microsoft Excel

I am new to VBA Script. I am using excel 2010 version. I am trying to create a new Tab(Ex:Trends) beside Data tab. I want to show/Hide Tab(Trends) based on the column selected. If I select Column A,the tab(Trends) should show and if i select column B the tab(Trends) should hide.

How I can achieve the above functionality? Do we need a macro to achieve this or any alternative.

Macro to warn when a record will expire within 30 seconds

I found this one which had a same problem. I notice that the problem was in formated sheet. I take out the formated and now it work except that when the expire date is far away and there is no need to do anythig it popup the "test text" only with out names and days. If the expires dates is near the range "40" it give the right message with the "test text" and names and days.

   Sub popup()

Dim lstRow As Long
Dim i As Long
Dim msg As String
Dim ws As Worksheet
Set ws = ThisWorkbook.Worksheets("sheet1") ' rename as required

With ws
msg = "test text " & vbCrLf & vbCrLf
lstRow = .Cells(.Rows.Count, "S").End(xlUp).Row
For i = 2 To lstRow
If .Range("S" & i) - (Date) <= 40 Then
msg = msg & .Range("B" & i).value & " in " & .Range("S" & i).value - Date & " days" & vbCrLf



End If

Next i

End With
MsgBox msg
'Call settimer
End Sub

Error trying to create excel sheet from xml file using vb.net

I have a pdf form containing 6 rows of radiobuttons and 1 text field on submit it gives me this xml file:

<?xml version="1.0" encoding="UTF-8"?>
<form1
><Table1
><HeaderRow xmlns:xfa="http://ift.tt/1c7vSVL" xfa:dataNode="dataGroup"
/><Row1 xmlns:xfa="http://ift.tt/1c7vSVL" xfa:dataNode="dataGroup"
/><Row2 xmlns:xfa="http://ift.tt/1c7vSVL" xfa:dataNode="dataGroup"
/><Row3 xmlns:xfa="http://ift.tt/1c7vSVL" xfa:dataNode="dataGroup"
/><Row4 xmlns:xfa="http://ift.tt/1c7vSVL" xfa:dataNode="dataGroup"
/><Row5 xmlns:xfa="http://ift.tt/1c7vSVL" xfa:dataNode="dataGroup"
/></Table1
><Table2
><Row1 xmlns:xfa="http://ift.tt/1c7vSVL" xfa:dataNode="dataGroup"
/></Table2
><Table3
><Row1 xmlns:xfa="http://ift.tt/1c7vSVL" xfa:dataNode="dataGroup"
/><Row1 xmlns:xfa="http://ift.tt/1c7vSVL" xfa:dataNode="dataGroup"
/></Table3
><QualityWork
>1</QualityWork
><Ontime
>2</Ontime
><QualityReport
>3</QualityReport
><Needs
>4</Needs
><Comm
>5</Comm
><Global
>6</Global
><Comments
>This is a comment</Comments
></form1
>

I'm trying to create a vb.net program that will create an excel sheet with the data from the xml file.

I tried using this vb.net code

 Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
    Dim filePath = create_xml.Text()

    Dim m_xmld As XmlDocument
    'Create the XML Reader
    m_xmld = New XmlDocument()
    m_xmld.Load(filePath)

    Dim m_nodelist As XmlNodeList
    Dim m_node As XmlNode

    m_nodelist = m_xmld.SelectNodes("/form1")


    Dim total_untokenized = ""

    For Each m_node In m_nodelist

        total_untokenized = m_node.ChildNodes.Item(0).InnerText
        comments = m_node.ChildNodes.Item(1).InnerText
    Next

    Dim total_tokenized As Integer
    total_tokenized = 0

    For i = 1 To 6
        total_tokenized = total_tokenized + Strings.Mid(total_untokenized, i, 1)
    Next


    Dim total_col = 7

    Dim MyArrayList = New ArrayList(total_col)

    MyArrayList.Add(comments)
    For i = 1 To 6
        MyArrayList.Add(Strings.Mid(total_untokenized, i, 1))
    Next
    MyArrayList.Add(total_tokenized)


    Dim MyArrayList2 = New ArrayList(total_col)


    MyArrayList2.Add("QualityWork")
    MyArrayList2.Add("Ontime")
    MyArrayList2.Add("QualityReport")
    MyArrayList2.Add("Needs")
    MyArrayList2.Add("Comm")
    MyArrayList2.Add("Global")
    MyArrayList2.Add("Comments")


    Dim oExcel As Object
    Dim oBook As Object
    Dim oSheet As Object
    oExcel = CreateObject("Excel.Application")
    oExcel.Visible = True
    oBook = oExcel.Workbooks.Add
    oSheet = oBook.Worksheets(1)

    For col = 0 To total_col - 1
        oSheet.Cells(1, col + 1) = MyArrayList2.Item(col)
    Next

    oSheet.Rows("1:1").Font.Bold = True
    For col = 0 To total_col - 1
        oSheet.Cells(2, col + 1) = MyArrayList.Item(col)
    Next
 End Sub 

But this m_nodelist = m_xmld.SelectNodes("/form1") seems to stay empty, I always get an error at this line MyArrayList.Add(Strings.Mid(total_untokenized, i, 1))

Do I have to change the design of my form or it's only a error in vb?

XSSF (POI) - Adding "formula" column to pivot table

I am using POI 3.12-beta1:

<!-- Apache POI (for Excel) -->
<dependency>
    <groupId>org.apache.poi</groupId>
    <artifactId>poi</artifactId>
    <version>3.12-beta1</version>
</dependency>

<dependency>
    <groupId>org.apache.poi</groupId>
    <artifactId>poi-ooxml</artifactId>
    <version>3.12-beta1</version>
</dependency>

<dependency>
    <groupId>org.apache.poi</groupId>
    <artifactId>ooxml-schemas</artifactId>
    <version>1.1</version>
</dependency>

I am trying to create a calculated pivot table column which is defined as: = 'Ended' / 'Generated' * 100.

I went ahead and manually edited the sheet in Excel to get this to work, and when I reversed the *.xlsx file into a ZIP directory and looked through it, I found the following code in \xl\pivotCache\pivotCacheDefinition1.xml:

<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<pivotCacheDefinition xmlns="http://ift.tt/K9sZsw" xmlns:r="http://ift.tt/1bA4cfb" r:id="rId1" refreshOnLoad="1" refreshedBy="vyasrav" refreshedDate="42110.580247453705" createdVersion="3" refreshedVersion="3" minRefreshableVersion="3" recordCount="352">
    <cacheSource type="worksheet">
        <worksheetSource ref="A1:O353" sheet="Data"/>
    </cacheSource>
    <cacheFields count="16">
        <!-- OMITTED -->
        <cacheField name="Avg Pct Processed" numFmtId="0" formula="'Ended' / 'Generated' * 100" databaseField="0"/>
    </cacheFields>
</pivotCacheDefinition>

So I went back to my java program and added the following code to generate it automatically, but it isn't registering data column "15" and I am getting an IndexOutOfBounds error.

// Add pivot (pivot table):
Sheet pivotSheet = workbook.createSheet("Pivot");
LOGGER.trace("Created sheet: '" + String.valueOf(pivotSheet) + "'.");

XSSFPivotTable pivotTable = ((XSSFSheet)pivotSheet).createPivotTable(new AreaReference(tableRange), new CellReference("A1"), dataSheet);
CTPivotTableDefinition ctPivotTableDefinition = pivotTable.getCTPivotTableDefinition();
CTPivotTableStyle ctPivotTableStyle = ctPivotTableDefinition.getPivotTableStyleInfo();
ctPivotTableStyle.setName("PivotStyleMedium4");

// Row Labels:
pivotTable.addRowLabel(...); // ...
...

// Add column 15 (this is a calculated column):
CTCacheFields ctCacheFields = pivotTable.getPivotCacheDefinition().getCTPivotCacheDefinition().getCacheFields();
CTCacheField ctCacheField = ctCacheFields.addNewCacheField();
ctCacheField.setName("Avg Pct Processed");
ctCacheField.setFormula("'Ended' / 'Generated' * 100");

// Column Labels:
pivotTable.addColumnLabel(DataConsolidateFunction.SUM, 8, "Sum of Generated");
pivotTable.addColumnLabel(DataConsolidateFunction.SUM, 12, "Sum of Ended");
pivotTable.addColumnLabel(DataConsolidateFunction.SUM, 13, "Sum of Unended");
pivotTable.addColumnLabel(DataConsolidateFunction.SUM, 15, "Average of Processed Percent");
...

The StackTrace of the IndexOutOfBoundsException which occurs on the bolded line above is:

Exception in thread "main" java.lang.IndexOutOfBoundsException
    at org.openxmlformats.schemas.spreadsheetml.x2006.main.impl.CTPivotFieldsImpl.setPivotFieldArray(Unknown Source)
    at org.apache.poi.xssf.usermodel.XSSFPivotTable.addDataColumn(XSSFPivotTable.java:372)
    at org.apache.poi.xssf.usermodel.XSSFPivotTable.addColumnLabel(XSSFPivotTable.java:296)
    at com...

Does anyone know how can I use POI to generate this column?

EDIT:

I tried using both:

CTPivotTableDefinition ctPivotTableDefinition = pivotTable.getCTPivotTableDefinition();

and

CTCacheField ctCacheField = ctCacheFields.insertNewCacheField(15);

and in either scenario, I get the same exception when this line executes:

pivotTable.addColumnLabel(DataConsolidateFunction.SUM, 15, "Average of Processed Percent");

As a note, I did try commenting out the line where I add the new column label, and when I do that, if I open up the workbook in Excel 2010, I get the following error message when it starts up:

Removed Feature: PivotTable report from /xl/pivotTables/pivotTable1.xml part (PivotTable view)
Removed Records: Workbook properties from /xl/workbook.xml part (Workbook)

Thanks!

How to create spreadsheet like rows and colums headers and data

i was trying to create a spreadsheet in as3 , i tried to do it with grid column , but cant able to add any row header as it is not possible , my requirement was to create a spreadsheet like format with column headers and row headers and i have to add editable data to each cell, please help me out

how i show null in data set while importing excel sheets in .net

    string SheetName = dtExcelSchema.Rows[0]["TABLE_NAME"].ToString();

    connExcel.Close();


    //Read Data from First Sheet
    connExcel.Open();
    cmdExcel.CommandText = "SELECT * From [" + SheetName + "]";

    oda.SelectCommand = cmdExcel;
    oda.Fill(dt);
    connExcel.Close();
    GridView1.Caption = Path.GetFileName(FilePath);
    GridView1.DataSource = dt;
    GridView1.DataBind();

i am importing an excel sheet but in place of null value data set showing nothing i want to show null

Copying an entire column from one CSV file to another using Powershell

I have two CSV files: File1.csv has one column with 4000+ rows. File2.csv has 200 columns with 10000+ rows of content. I want to add the one column in file1.csv as an additional column on File2.csv. I am OK adding it to the end (rightmost) of the existing file. I have found several options online, but none has worked as desire. I can get it done with the Input-CSV cmdlet and adding a Property but that is taking more than ~1 hour to execute. Is there any way to do this without having to convert the CSV content into PSobjects? I have used Get-Content and Set-Content in the past, but that will append one file to the bottom of the other one. Is there any way I could do something similar but appending to the right of the existing file?

Here is the piece of code that has gotten me closer to what I need. The problem with this one is Excel is not saving or closing. Any ideas on how this problem can be solved either by fixing the code below or an easier/more efficient way to do it?

$source = "C:\Users\Desktop\Script_Development\04-16-2015\Bit.csv"
$dest =  "C:\Users\Desktop\Script_Development\04-16-2015\MergedwithHeader_04-16-2015.csv"
$Excel = New-Object -ComObject Excel.Application 
$Excel.visible = $false
$Workbooksource = $excel.Workbooks.open($source) 
$Worksheetsource = $Workbooksource.WorkSheets.item("Bit") 
$Worksheetsource.activate()  
$range = $Worksheetsource.Range("A1").EntireColumn 
$range.Copy() | out-null 

$Workbookdest = $excel.Workbooks.open($dest) 
$Worksheetdest = $Workbookdest.Worksheets.item("MergedwithHeader_04-16-2015") 
$Range = $Worksheetdest.Range("FT1") 
$Worksheetdest.Paste($range)  
$Workbookdest.SaveAs("C:\Users\Desktop\Script_Development\04-16-2015\MergedwithHeader_04-16-2015.xls")
$Excel.quit()

Automatic add reference to VBA Excel macro via c#

I add a macro by c# code to excel for running stored procedure and execute it but becuase it has not refrence of ActiveX Data Objects 2.5 library gets an error "complie error : user-defined type not defiend " when i add that refrence manualy it will be ok but i want to add it via code because user cant refrence it.

then I need to programaticly check the users PC to see if a reference to

Microsoft ActiveX Data Objects 2.5 Library or later

Exists and if it does not, create it by C# code or VBA code in my macro

ListView not exporting data correctly to Excel

I am getting an error in excel relating to the data I am trying to export from a listview (ASP & C#).

To clarify, I am converting the ListView to a DataTable as I was getting errors before hand.

All of this is handled on a button click, the excel document is created but when opening it there is an error and only the headers are shown.

Any idea as to what I'm doing wrong? My suspicion is that adding the dt in LoadFromDataTable is causing this, but nothing is coming up in debugging as an error - any pointers would be gratefully received.

Excel Error: We found a problem with some content in 'Test_List.xlsx'. Do you want us to try to recover as much as we can? If you trust the source of this workbook, click Yes.

C# Code behind

protected void csv_Click(object sender, EventArgs e)
    {
        DataTable dt = new DataTable();
        dt.Columns.Add(new DataColumn("Ref", typeof(string)));
        dt.Columns.Add(new DataColumn("Company", typeof(string)));
        dt.Columns.Add(new DataColumn("Email", typeof(string)));
        dt.Columns.Add(new DataColumn("Telephone", typeof(string)));

        foreach(ListViewDataItem li in ListView1.Items)
        {
            if (li.ItemType == ListViewItemType.DataItem)
            {
                DataRow dr = dt.NewRow();
                dr["Ref"] = ((Label)li.FindControl("lblRef")).Text;
                dr["Company"] = ((Label)li.FindControl("lblCmp")).Text;
                dr["Email"] = ((Label)li.FindControl("lblEmail")).Text;
                dr["Telephone"] = ((Label)li.FindControl("lblTele")).Text;
                dt.Rows.Add(dr);
            }
        }

        using (ExcelPackage pck = new ExcelPackage())
        {
            // creating worksheet
            ExcelWorksheet ws = pck.Workbook.Worksheets.Add("Test_List");

            // load database to sheet, start from A1, print column names on row 1
            ws.Cells["A1"].LoadFromDataTable(dt, true);
            //loop through rows in datatable to rows in excel

            Response.ContentType = "application/vnd.openxmlformats-officedocument,spreadsheetml.sheet";
            Response.AddHeader("content-disposition", "attachment; filename=Test_List.xlsx");
            Response.BinaryWrite(pck.GetAsByteArray());
        }
    }

Turning the visibility of chart series on/off using excel Macros/vba

I am making a line graph (chart) in Excel with several data series being plotted onto the same chart.

I need to create a macro/VBA solution that can turn the visibilty of these series on/off via the pressing of a button (or tick box etc)

Similar to this picture (manually done through the excel menu system)

I have tried to look through all the member vars/methods on

http://ift.tt/1NXFN9q

but haven't had much luck.

I have tried playing around with bits like

Charts("Chart1").SeriesCollection(1)

and

Worksheets("Graphical Data").ChartObjects(1)

but I can neither get the chart object ( I get a subscript out of range error) nor able to find any method that would allow me to turn on/off the visibility of individual series.

Any Ideas?

Placed a button and named as backspace,when clicked button should act as actual backspace on a textbox

I am creating a multilanguage keyboard using vba excel 2007,I have added buttons to display the character in a textbox ,so If i want to type A then i have written code as Private Sub CommandButton88_Click() TextBox1.Text = ChrW(65) End Sub Now I have placed a button for backspace,another for enter,another for escape ,f1,etc...But how to write codes if I want the button named backspace to work exactly as backspace button in actual keyboard and enter as enter and so on....Please reply,seen keyascii and application.sendkeys({backspace}),but how to apply it on the textbox to work as backspace,enter etc...please reply,for backspace found as chr(8),but how to apply?

VBA: How to transform a one column full dictionary into one column per letter?

I have a full dictionary. All the words (360 000) are in one column.

I'd like to have Column B with all words starting with "a", column C with all words starting with b...

I am trying to do a loop or something... but... It is just too long.

Any tips? Or did someone already do this vba macro?

Tks,

Stéphane.

VBA increasing dynamic range in VLOOKUP

I'm having trouble with setting properly dynamic range for VLOOKUP which should be increasing by 58 with each loop (I have 96 different ranges). VLOOKUP table is in other ("Gefco") sheet. The error I'm getting is:

Application-defined or object-defined error

Code:

Sub vlookup_rates()

Dim a As Long
Dim b As Long
Dim c As Long
Dim d As Long
Dim e As Long
Dim rr, dupa As Range
Dim ws, wws As Worksheet
Dim wb As Workbook


c = 1
a = 2
b = 59
d = 2
e = 59

Set wb = ActiveWorkbook
Set ws = wb.Sheets("Gefco")
With ws
Set rr = Range(Cells(d, 4), Cells(e, 8))
End With

Set wws = wb.Sheets("Waberers")
wws.Activate

    Do While c < 97
        Cells(4, a).Select
        Cells(4, a).Formula = "=VLOOKUP($A$4;" & rr.Address & ";5;0)"

        c = c + 1
        a = a + 1
        b = b + 1
        d = d + 58
        e = e + 58

    Loop
End Sub

I think range is somehow wrongly defined but I can't crack it. Please halp.

Thanks!

Adjust rows with the same value (e.g. date) to be in the same column in Excel

I've got a spreadsheet with columns, that has values (e.g. dates) in the first row. There are many pairs of rows, that has dates in the first row with values assigned to them in the second row. Everything is clearly visible in screenshots.

How to adjust those values to the reference dates (from the first row)?

I was thinking about some VBA macro, but it's beyond my skills.

Current State:

Desired State:

Selecting Range for Excel chart in VBScript

I have two questions:
1. If we can pass an array to VBSCript using java. I am able to pass single variables to VBSCript using following command

Runtime.getRuntime().exec("wscript openChartsDevice.vbs " + fileName + " " + range);

But when I pass a String array, it says type mismatch. I am catching the array passed in

Dim arr()

any Suggestions?

2. I am using following Vbscript to create chart in excel

    Dim oExl,excelPath,objWriteSheet,objWriteWorkbook
    Dim oMychartProcs
    Set oExl=CreateObject("Excel.Application")
    Set objWriteWorkbook = oExl.Workbooks.Open("SomeExcelfile.xlsx")
    Set objWriteSheet = objWriteWorkbook.Worksheets(1)
    Set oMychartProcs = objWriteWorkbook.Charts.Add
    oMychartProcs.SetSourceData objWriteSheet.Range(Cells(2,1),Cells(7,6))
    oMychartProcs.ChartType = 4
    oMychartProcs.Name = "ChartName"
    oMychartProcs.Activate

I have given the range as A2:F7. when I enter

oMychartProcs.SetSourceData objWriteSheet.Range("A2:F7")

the chart is created perfectly but when I use the

 Range(Cells(2,1),Cells(7,6))

whole excel sheet is converted to chart. I want to provide the range through parameters so I want above formula to work. I've searched a lot and could not find a definitive way for it. Thank you.

Retrieving the COM class factory for component with CLSID {00024500-0000-0000-C000-000000000046} failed due to the following error: 80040154

i am getting below error when i try to run below line of code.

code:- private excel.Application app = new excel.Application();

error:- Retrieving the COM class factory for component with CLSID {00024500-0000-0000-C000-000000000046} failed due to the following error: 80040154.

This code is working fine when microsoft office is install on dev server. but company did not wants to install microsoft office on Production server. So, i have uninstall microsoft office from dev machine.

I have install microsoft office 2010 primary interop assembly(PIARedist.exe). But, still getting same error.

Please help me on this issue.

Reading an Excel file UsedRange error

I basically need my code to read one by one all cells in an excel file and then upload then to a database.

I've read on several answers to this question I should use the UsedRange but everytime I do I get an error saying there is no definition for UsedRange.

I added a reference to the excel interop but no dice.

Any advice would be appreciated. And I know the code looks terrible now but I just wanted to test if I could read data from an excel file.

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using Excel = Microsoft.Office.Interop.Excel;

namespace ConsoleApplication28
{
    class Program
    {
        static void Main()
        {
            Excel.Application excelApp = new Excel.Application();
            excelApp.Visible = true;

            string workbookPath = "C:/Users/Sidney/Desktop/CrystalViewer-11.xls";
            Excel.Workbook excelWorkbook = excelApp.Workbooks.Open(workbookPath,
                    0, false, 5, "", "", false, Excel.XlPlatform.xlWindows, "",
                    true, false, 0, true, false, false);

            Excel.Sheets excelSheets = excelWorkbook.Worksheets;
            string currentSheet = "Sheet1";

            Excel.Worksheet excelWorksheet = (Excel.Worksheet)excelSheets.get_Item(currentSheet);

            Excel.Range range;
            range = excelSheets.UsedRange;
            int rows_count = range.Rows.Count;
            string output = null; 
        }
    }
}

Adding a data validation list where entries have commas

I am using this code to add a defined list to a cell. The range of entries which I wish to populate this list have commas within their names. The code then recognises this line as two or more items within the TempList string. Is there a way to include the comma within the items and not have it split out?

Sub DefinedPartnerListPop()

   Dim rList As String
   Dim MyCol As Collection
   Dim i, n, k, j, a As Integer
   Dim TempList, Partner As String
   Dim WSHT, WSHT2 As Worksheet
   Dim Swap1, Swap2



   Set WSHT = Sheet1

   With WSHT
   a = WorksheetFunction.CountA("A1:A13")
   k = WorksheetFunction.CountA(.Range("A:A")) - a


 Set MyCol = New Collection
        For i = 1 To k
        If Len(Trim(.Range("B" & 13 + i).Value)) <> 0 And .Range("A" & 13 + i) <> "" Then
            On Error Resume Next
          Partner = .Range("B" & 13 + i).Text
            MyCol.Add Partner
            On Error GoTo 0
        End If
    Next i




    For n = 1 To MyCol.Count
        TempList = TempList & "," & MyCol(n)
    Next

    TempList = Mid(TempList, 2)


End With
With WSHT

.Range("B12").ClearContents: .Range("B12").Validation.Delete
With .Range("B12")
 With .Validation
'Call UnProtectChecklistSht
    .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
    xlBetween, Formula1:=TempList
    .IgnoreBlank = True
            .InCellDropdown = True
            .InputTitle = ""
            .ErrorTitle = ""
            .InputMessage = ""
            .ErrorMessage = ""
            .ShowInput = True
            .ShowError = True

End With
End With
End With



End Sub

Using VB.net and excel (combo box & search)

New to programming and need to make a program in VB to interact with the Excel in the background

The Idea is to have two combo boxes, one "country", the other "city" (If country chosen, list of cities would be reduced to that country; if city is chosen, country would be selected automatically);

I have a DataGridView which displays two columns from the excel speadsheet, the person's fist name and the last name.

So given the city or a country/city a list of people would be displayed living in that country.

Also I'm trying to make a searchbox, whereby when you type into a textfield, given what you have typed so far it would filter the first name (containing what you have typed) in the GridView.

So far I have managed to get other things working, such as radiobuttons and checkboxes with different options.

I could not find anything that would work in regards to the comboboxes and the search field (without a button, so "live" filtering process would need to take place).

I am comfortable with SQL so I've been using sql so far to send queries to the excel and retrieve info for the grid view.

Could you please help? (may be provide with the template of how I should code it....)

---

I am using Visual Basic 2012 and OleDB 4.0 (If i'm not mistaken) to make the connection.

VBA written in Excel for Windows not working on Mac

I have a set of macros to hide and unhide columns based on the contents of a specific row. They were all written in Excel 2013 for Windows (running in parallels on my MBA, if that's relevant) and work fine there. But when I open the worksheet in Excel 2011 for Mac, the macros give odd results. The "unhide all columns" macro works fine; the other functions get as far as hiding all columns but not as far as unhiding the ones I want to see.

I can only assume Excel for Mac is having a problem with what's in the FOR EACH loop, but I can't figure out what! I'd appreciate any guidance: I need to get this system working on both Windows and Mac.

Thanks! Code below.

This function works:

Sub GANTT_Filter_Show_All()

Dim rngDates As Range

Set rngDates = Range("GANTT_Dates")

rngDates.EntireColumn.Hidden = False

End Sub

But this one only hides all the columns:

Sub GANTT_Filter_This_Quarter()

Dim intCurrentMonth As Integer, intCurrentYear As Integer, rngDates As Range, cell As Range
Dim intCurrentQuarterMonths(3) As Integer

Set rngDates = Range("GANTT_Dates")
intCurrentMonth = DatePart("m", Date)
intCurrentYear = DatePart("yyyy", Date)

'loading months of current quarter into an array intCurrentMonth

Select Case intCurrentMonth
    Case 1 To 3
        intCurrentQuarterMonths(0) = 1
        intCurrentQuarterMonths(1) = 2
        intCurrentQuarterMonths(2) = 3
    Case 4 To 6
        intCurrentQuarterMonths(0) = 4
        intCurrentQuarterMonths(1) = 5
        intCurrentQuarterMonths(2) = 6
    Case 7 To 9
        intCurrentQuarterMonths(0) = 7
        intCurrentQuarterMonths(1) = 8
        intCurrentQuarterMonths(2) = 9
    Case 10 To 12
        intCurrentQuarterMonths(0) = 10
        intCurrentQuarterMonths(1) = 11
        intCurrentQuarterMonths(2) = 12
    End Select

'hiding all columns

rngDates.EntireColumn.Hidden = True

'comparing each column to array of months in current quarter and hiding if false

For Each cell In rngDates
    For Each v In intCurrentQuarterMonths
        If v = DatePart("m", cell.Value) And DatePart("yyyy", cell.Value) = intCurrentYear Then cell.EntireColumn.Hidden = False
    Next v
Next cell

Application.Goto Reference:=Range("a1"), Scroll:=True

End Sub

Not able to apply specific cell data format using POI

I am using Apache poi to write data into excel. I am trying to write big decimal number to an excel cell and i am using the following methods to achieve..

> 1.        CellStyle style= wb.createCellStyle();
            style.setDataFormat(df.getFormat("000.0000000"));

    2.      CellStyle style= wb.createCellStyle();
    .       HSSFCreationHelper createHelper = wb.getCreationHelper();
            style.setDataFormat(
            createHelper.createDataFormat().getFormat("000.0000000"));

Below is the required Format:

0.0000000--->000.0000000
1.0000000--->001.0000000
10.0000000--->010.0000000
101.0068721--->101.0068721
111.0034360--->111.0034360
010.0034526--->010.0034526
100.1452672--->100.1452672

But i am ending up with the below output using above data format

0.0000000  --->   000.0000000
1.0000000--->    000.0000000
10.0000000--->000.0000000
101.0068721--->000.0068721
111.0034360--->000.0000000
010.0034526--->000.0034526
100.1452672--->000.1452672 

Please suggest me the correct data format. Thanks in advance :)

Can SSRS use Excel from Sharepoint document list as data source?

Question like in title. How can I use Excel in document library on some sharepoint site as data source?

ODBC support local excels. SSRS support sharepoint lists. But can not find anything about excels in document libraries.

MVC Embedding Interactive Excel

Need to embed Excel in web page in MVC intranet site.

Constraints are,

1. We cannot use Interop automation.
2. we cannot publish the document to public sky drive.

Is there any other possibility to Embed an interactive Excel in a web page?

Thanks in advance.

how to find substring in a list and show a cell from matching line in excel

I have a list of id's in column A (gene1, gene2,...) followed by list of description in column B (gene_id=1234 gene_locus=gene1, ...) and also a sub string(1234, ...) which is in column C, I would like to search the column B for the substring of column C and if matches show the column A's matching line in column D.

For example here, I would like to get gene1 in column D in the same line where you see 1234.

I have already tried to count the matching line but wont do the thing I want

=COUNTIF(A1:A7775,"*"&C1&"*")>1

Conditionally removing duplicates in Excel based on highest value in a Column

I have a 2-column file in excel that that looks like this: The first column is a value, and the Second column contains a corresponding word. However, I need to remove all of the duplicates for which their value in Column 1 is not the highest. Essentially, cleaning up this data sheet, that has millions of entries.

-2  cat
-2  cat
-1  cat
-3  dog
-2  dog
-1  dog
-2  milk
-1  milk
-2  juice
-1  juice

I want to remove all duplicates in Column 2 that do not have the highest value in Column 1. The columns are ordered firstly by Column 2 (therefore the words are in alphabetical order: A-Z) and secondly by Column 1 (from highest to lowest, therefore the highest value is always the first value for each word).

I have been trying to do this using the advanced filtering in Excel for Mac 2011, but I cannot seem to include the condition of removing all duplicate entries except for the entry that has the highest value in Column 1.

The desired output is as follows:

-2  cat
-3  dog
-2  milk
-2  juice

I have been searching for answers based on the query conditionally removing duplicates, here and here, however each of the solutions provided seems to be based on a boolean condition (i.e. there is information or not), and not determining which value is the highest as the condition for which to remove the duplicate or not.

Any ideas on how to go about solving this?

Run Excel VBA module for all the values of a Combo Box

I want to run a macro in Excel 2010 that uses each of the values in a combo box automatically until all the values in the combo box have been run.

For example if the combo box has the values 1,2,3,4 the code should run for each of these values passing them into a macro.

Excel style categories

Is is possible to manage new style categories in Excel 2010, i.e. rename, delete and create new categories?

I can add a style but no option for category: ActiveWorkbook.Styles.Add Name:="Style 1"

I can delete a style but no option for category: ActiveWorkbook.Styles("Style 1").Delete

Excel VBA: adding a row to a variant array in one line

I´d like to add a row to a variant array:

Dim arrMod As Variant
arrMod(numberOfRow) = Array(myValue1, myValue2, myvalue3)

The execution of this code results into an exception: Error 13: type mismatch How can I do it without iterating each column?

Thanks,

Regards

Dynamically creating a dropdown, with values from SQL server

I want to create a feature within my program that when i load in an excel file, it counts how many columns in the spreadsheet and dynamically creates a dropdown for each and every column, to select what header it should be, and bind it to the GridView .Data from SQL server will populate the dropdowns. Some problems i have run into is, how do i have a button where it asks for the file location? i have tried this way to load in a hardcoded location, but it doesnt work.

Sub SubmitBtn_Excel(Sender As Object, E As EventArgs)
        Try
            Dim MyConnection As System.Data.OleDb.OleDbConnection
            Dim dataSet As System.Data.DataSet
            Dim MyCommand As System.Data.OleDb.OleDbDataAdapter
            Dim path As String = "C:\\spreadsheet.xlsx"

            MyConnection = New System.Data.OleDb.OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + path + ";Extended Properties=Excel 12.0;")
            MyCommand = New System.Data.OleDb.OleDbDataAdapter("select * from [Sheet1$]", MyConnection)

            dataSet = New System.Data.DataSet
            MyCommand.Fill(dataSet)
            GridView1.DataSource = dataSet.Tables(0)

            MyConnection.Close()
        Catch ex As Exception

        End Try

    End Sub

I also have a method to call on the database with the table i have created to populate the data, should i put this method all in one method with the excel call?? considering that the dropdown id was dynamically created, it doesnt know how to find it because its not id'd in the HTML

Protected Sub Page_Load(sender As Object, e As EventArgs)
        If Not Page.IsPostBack Then
            FillDeptDropdownList()
        End If
    End Sub
    Protected Sub FillDeptDropdownList()
        Dim con As New SqlConnection(ConfigurationManager.ConnectionStrings("MyDbCon").ConnectionString)

        Dim cmd As New SqlCommand("Select * from demofeepay.dbo.catagories", con)
        Dim adp As New SqlDataAdapter(cmd)
        Dim dt As New DataTable()
        adp.Fill(dt)
        ddlDynamic.DataSource = dt
        ddlDynamic.DataTextField = "catagory"
        ddlDynamic.DataValueField = "ID"
        ddlDynamic.DataBind()
        ddlDynamic.Items.Insert(0, "Select Catagory")

    End Sub

This is the method i have for creating the dropdown

Sub SubmitBtn_Excel(Sender As Object, E As EventArgs)
        Try
            Dim MyConnection As System.Data.OleDb.OleDbConnection
            Dim dataSet As System.Data.DataSet
            Dim MyCommand As System.Data.OleDb.OleDbDataAdapter
            Dim path As String = "C:\\Users\\John\\Documents\\John\\EPP\\WorldNet_Notes\\CHRISTIAN_BROTHERS_HIGH_SCHOOL.xlsx"

            MyConnection = New System.Data.OleDb.OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + path + ";Extended Properties=Excel 12.0;")
            MyCommand = New System.Data.OleDb.OleDbDataAdapter("select * from [Sheet1$]", MyConnection)

            dataSet = New System.Data.DataSet
            MyCommand.Fill(dataSet)
            GridView1.DataSource = dataSet.Tables(0)

            MyConnection.Close()
        Catch ex As Exception

        End Try

    End Sub

This is the HTML

<form id="form1" runat="server" style="overflow-x:hidden; width:100%">
<div>
    <asp:button id="butOK" text="Add Dropdowns" onclick="SubmitBtn_Click" runat="server"/>
    <asp:button id="BtnAddExcel" text="Add Spreadsheet" OnClick="SubmitBtn_Excel" runat="server"/>
</div>
<div id ="ddlDynamic">
<!-- Dynamic Dropdowns -->

    <asp:GridView ID="GridView1" runat="server"></asp:GridView>
</div>
</form>

I am aware that this is all over the place, as you can see im a bit lost. I dont really want anybody to spoon fed me the answer, im just unsure how to structure it, or could anybody point me in the right direction to find out more. New to vb. Thanks in advance :-)

automation server can't object - Excel.Application

Am facing this issue while trying to export excel file via JAVASCRIPT

"automation server can't object"

Also my Internet Explorer is well configured (Activex Control). Do i have to register any DLL ? or to do something else ?

Thanks for your help.

every possible combination of the contents of 2 columns in excel

Suppose I have 2 Columns:

1st column(contains 1000 rows):

U-0001

U-0002 

2nd column(contains 10 rows):

B01

B02

B03

Now, I want to generate two columns like this(with 10*1000 = 10000 rows):

U-0001 B01

U-0001 B02

U-0001 B03

U-0002 B01

U-0002 B02

U-0002 B03

How to move multiple files FROM access to excel

Basicly, theres a ton of access files that need to be changed to excel for easier use(dont question it). But there are so many tables in the single access that it would take forever to copy paste those over or use the "export" function. Quick count theres about 60 tables in the file im working on. I havent found any good answers over the interwebs and couldnt cant figure out the macros. Everyone wants to move data from excel to access, not vice versa.

Loop does not recognize Do

I have been editing/writing this code to merge multiple workbooks into one. However I get a "Loop without Do" compile error. The possible duplicate does not say what went wrong, only gives a new code, so that is not a answer to my question but a solution.

Sub MergeAllWorkbooks()
    Dim SummarySheet As Worksheet
    Dim FolderPath As String
    Dim NRow As Long
    Dim OCol As Long
    Dim FileName As String
    Dim WorkBk As Workbook
    Dim SourceRangeCult As Range
    Dim DestRangeCult As Range
    Dim SourceRangeYield As Range
    Dim DestRangeYield As Range
    Dim SourceRangeLoc As Range
    Dim DestRangeLoc As Range
    Dim SourceRangeDRipe As Range
    Dim DestRangeDRipe As Range
    Dim LastRow As Integer
    Dim LastColumn As Integer
    Dim col As Integer

    Set SummarySheet = Workbooks.Add(xlWBATWorksheet).Worksheets(1)

    FolderPath = "M:\My Documents\MSC Thesis\United Kingdom\Winter Barley\Merge excel\"

    NRow = 1
    OCol = 2
    OColD = OCol + 48


    FileName = Dir(FolderPath & "*.xl*")


        Do While FileName <> ""

            Set WorkBk = Workbooks.Open(FolderPath & FileName)


            SummarySheet.Range("A" & NRow).Value = FileName


            LastRow = ActiveSheet.UsedRange.Rows.Count
            LastColumn = ActiveSheet.UsedRange.Columns.Count

                For col = 2 To 49

                Set SourceRangeLoc = WorkBk.Worksheets(1).Range("A1:A" & LastRow)
                Set DestRangeLoc = SummarySheet.Range("C" & NRow)
                Set DestRangeLoc = DestRange.Resize(SourceRangeLoc.Rows.Count, 1)

                Set SourceRangeCult = WorkBk.Worksheets(1).Range(OCol & "1:" & OCol & "1")
                Set DestRangeCult = SummarySheet.Range("B" & NRow)
                Set DestRangeCult = DestRange.Resize(SourceRangeLoc.Rows.Count, 1)

                Set SourceRangeYield = WorkBk.Worksheets(1).Range(OCol & "2:" & OCol & LastRow)
                Set DestRangeYield = SummarySheet.Range("D" & NRow)
                Set DestRangeYield = DestRange.Resize(SourceRangeLoc.Rows.Count, 1)

                Set SourceRangeDRipe = WorkBk.Worksheets(1).Range(OColD & "2:" & OColD & LastRow)
                Set DestRangeDRipe = SummarySheet.Range("E" & NRow)
                Set DestRangeDRipe = DestRange.Resize(SourceRangeLoc.Rows.Count, 1)


                DestRangeCult.Value = SourceRangeCult.Value

                Exit For

                NRow = NRow + DestRange.Rows.Count
                OCol = OCol + 1

            WorkBk.Close savechanges:=False

            FileName = Dir()

        Loop

    SummarySheet.Columns.AutoFit

End Sub

How to embed Excel with Macro in ASP.NET C# Web Page?

I would like to embed an Excel with Macros + a SSAS Connection to a ASP.NET web page. Is this possible? If it is, is there any tutorial you know so it will make my job easier?

Excel, comparing two specific rows in two sheets and highlighting the differences

Before commenting on saying that there are similar questions, Ive tried them but they do not work unfortunately

Hi, this is the first time I am on S.O, rest assured, I have spent hours looking for a solution for this. I have a status column which shows statuses such as, deleted, new, changed. When the status is "changed", I would like to compare that particular row from column E to the last possible column in Excel (XFD) in Sheet3 to columns A to the last possible column in Excel (XFD) in Sheet1 and highlight the cells which are different.

I have found this solution:-

Dim diffB As Boolean
  Dim r As Long, c As Integer, m As Integer
  Dim lr1 As Long, lr2 As Long, lc1 As Integer, lc2 As Integer
  Dim maxR As Long, maxC As Integer, cf1 As String, cf2 As String
  Dim rptWB As Workbook, DiffCount As Long
  Application.ScreenUpdating = False
  Application.StatusBar = "Creating the report..."
  Application.DisplayAlerts = True
  With Sheet1.UsedRange
    lr1 = .Rows.Count
    lc1 = .Columns.Count
  End With
  With Sheet3.UsedRange
    lr2 = .Rows.Count
    lc2 = .Columns.Count
  End With
  maxR = lr1
  maxC = lc1
  If maxR < lr2 Then maxR = lr2
  If maxC < lc2 Then maxC = lc2
  DiffCount = 0
  For c = 1 To maxC
    For i = 2 To lr1
      diffB = True
      Application.StatusBar = "Comparing cells " & Format(i / maxR, "0 %") & "..."
        For r = 2 To lr2
          cf1 = ""
          cf2 = ""
          On Error Resume Next
          cf1 = Sheet1.Cells(i, c).FormulaLocal
          cf2 = Sheet3.Cells(r, c).FormulaLocal
          On Error GoTo 0
          If cf1 = cf2 Then
            diffB = False
            Sheet1.Cells(i, c).Interior.ColorIndex = 19
            Sheet1.Cells(i, c).Select
            Selection.Font.Bold = True
            Exit For
          End If
        Next r

     If diffB Then
       DiffCount = DiffCount + 1
       Sheet1.Cells(i, c).Interior.ColorIndex = 0
       Sheet1.Cells(i, c).Select
       Selection.Font.Bold = False
     End If
    Next i
  Next c3
Application.StatusBar = "Formatting the report..."
'Columns("A:IV").ColumnWidth = 10
m = maxR - DiffCount - 1
Application.StatusBar = False
Application.ScreenUpdating = True
MsgBox m & " cells contain same values!", vbInformation, _
"Compare " & Sheet1.Name & " with " & Sheet3.Name

However, this compares columns and I do not know how to limit the comparison to column E-XFD in sheet1 to column A-XFD in sheet2.

There are also several sheets in this workbook but I only want to compare sheet1 and sheet2.

It will be much appreciated if you guys can help me out :)

Thanks!

import excel sheet data to sql database java

I have an excel sheet with 3 columns. I want to insert those data to sql database. But I need only my 1st and 3rd columns to insert. How I skip 2nd column and save data? I want the java code and some description about that.

Get the image position/location of an image in .xlsx file using Apache POI

I can only get the image data through this method .getAllPictures()

List<XSSFPictureData> lst = (List)workbook.getAllPictures();

Is it possible or is there a way so I can get the image position/location in a .xlsx file?

Excel VBA: copy lots of columns

I´d like to copy all data from a Sheet to another sheet of another new Excel file. I have tried this:

Set wkb = Workbooks.Add
wkb.SaveAs myNewFile
ThisWorkbook.Worksheets("Sheet 2").Activate
Set Ticker = ThisWorkbook.Worksheets("Sheet 2").Range("A1").CurrentRegion
Ticker.Copy
wkb.Worksheets(1).Activate
Cells(1, 1).PasteSpecial xlPasteAll
wkb.Save
wkb.Close

The problem I´ve found is that it won´t work when there are a great amount of columns (for instance, from A to OV). Do you know other way to to this?

Adding filename while importing txt file in VBA

I just wrote a program that imports .txt files to excel.

I try to import the filename (custName) to the first row of the sheet and the .txt to start below that. My filename is imported lagging 2 columns behind the associated .txt file and the first imported filename is always missing.

Am I missing some sort of offset or is it something with how the first for loop is running?

Function import(shtraw)

With Application.FileDialog(msoFileDialogFolderPicker)
.Title = "Please select a folder"
    .Show
    .AllowMultiSelect = False
    If .SelectedItems.Count = 0 Then
        MsgBox "You did not select a folder"
        Exit Function
    End If
    MyFolder = .SelectedItems(1)
End With

Set fileSystemObject = CreateObject("Scripting.FileSystemObject")
Set folderObj = fileSystemObject.getfolder(MyFolder)

shtraw.Select
For Each fileObj In folderObj.Files 'loop through files

If (fileSystemObject.GetExtensionName(fileObj.Path) = "txt") Then

    If Not fileObj.Attributes And 2 Then
        arrFileName = Split(fileObj.Path, "\")
        Path = "TEXT:" & fileObj.Path
        filename = arrFileName(UBound(arrFileName))

        'Get the filename without the.mtmd
        CustName = Mid(filename, 1, InStr(filename, ".") - 1)
        shtraw.range("$A$1").value = CustName

        With ActiveSheet.QueryTables.Add(Connection:="TEXT;" & fileObj.Path, Destination:=range("$A$2"))
            .name = filename
            .FieldNames = True
            .RowNumbers = False
            .FillAdjacentFormulas = False
            .PreserveFormatting = True
            .RefreshOnFileOpen = False
            .RefreshStyle = xlInsertDeleteCells
            .SavePassword = False
            .SaveData = True
            .AdjustColumnWidth = True
            .RefreshPeriod = 0
            .TextFilePromptOnRefresh = False
            .TextFilePlatform = 437
            .TextFileStartRow = 1
            .TextFileParseType = xlDelimited
            .TextFileTextQualifier = xlTextQualifierDoubleQuote
            .TextFileConsecutiveDelimiter = False
            .TextFileTabDelimiter = True
            .TextFileSemicolonDelimiter = False
            .TextFileCommaDelimiter = False
            .TextFileSpaceDelimiter = False
            .TextFileColumnDataTypes = Array(1, 1, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9)
            .TextFileTrailingMinusNumbers = True
            .Refresh BackgroundQuery:=False
        End With
    End If 'end if hidden if statement
    End If 'end of txt
Next fileObj 'close loop

range("$A$1:$B$1").Delete shift:=xlToLeft

End Function

Excel VBA Transpose contiguous range each empty cell

I have an excel File with contiguous cells in rows separate by empty Rows ex : Name Adresse Tel Fax Web -- EMPTY ROW -- Name Adress1 Adress2 Tel Web -- EMPTY ROW -- ...

I need to take each contiguous range and transpose it in columns on the right of each range Actually i need to select the range by the hand and run a shortcut macro to transpose it with this code :

ActiveCell.Offset(0, 1).Select
Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
    False, Transpose:=True

Could you help me in vba to select a first range and transpose it then take the next range after the empty row and transpose it and again until the end of the file ?

Thanks in advance

Run a macro inside to an excel file without opening excel program [on hold]

I have an excel consists of 10 sheets.

Each sheet uses XML feed data connections as tables.

Inside there is a macro - to update the XML databinding and to remove duplicate items.

But, because of size and processing problems it's becoming very difficult for me to keep open excel file always.

I have googled as much as possible but confused all the way!

Can any one help me "how to run update macro inside my excel" from any where with some simple command like CMD - run command.

I don't mind whether it is C#, .Net (sorry for third party tools)!

Thanks for your time and efforts!

I am sorry to hear this "my question is very broad!"

But unfortunately, I didn't find any starting clue to step on re-search about it's solution to get on my own. Also, I don't have enough knowledge in programming.

I have this code in my excel sheet.

Sub Macro_Refresh()
Dim ws As worksheet
Dim xmltable As ListObject
ActiveWorkbook.RefreshAll
alertTime = Now + TimeValue("00:30:00")
Appication.OnTime alertTime, "Macro_Refresh"
For Each ws In ThisWorkbook.Worksheets
    If ws.ListObjects.Count <> 0 Then
        Set  xmltable = ws.ListObjects(1)
        xmltable.Range.RemoveDuplicates Columns:=Array(4), Header:=xlYes
    End If
Next ws
End sub

I want to run this macro which is inside to excel file - without opening the excel file.

PHPExcel Redirect Page After Creating Excel File

I would like to know how to redirect the page after creating a file using PHPExcel. File created is ok but redirection to another page is not. Below are some codes.

$excel -> setActiveSheetIndex(0);
$_SESSION["information"] = "Motor Line Checking Completed.";
header("Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
header("Content-Disposition: attachment;filename='".$report_name.".xlsx'");
header("Cache-Control: max-age=0");
$writer = PHPExcel_IOFactory::createWriter($excel, "Excel2007");
$writer -> save('php://output');
exit;
header("Location: ".$_SERVER["HTTP_HOST"]."/../../");
exit;

NOTES:

• If I remove exit;after save();, xlsx file will be corrupted.


• If I move header("Location:...");before save();, it will redirect but will not create xlsx.


• I also tried redirecting using javascript.

What I want is to create xlsx and redirect the page after.

For Next Loop not working in VBA

So I've done SOME work with VBA in the past with moderate success, but am still very much an amateur. I am attempting to automate the pull of data from multiple pages of a website into a workbook. On one sheet titled "Events" I have 3 columns, the third of which (column C) is where I want the data pulled from, in the format "URL;http://ift.tt/1DrzSyj". I attempted to use the following code to go down my 108 rows, fetching all data:

Sub GetData()
For x = 1 To x = 108
ActiveWorkbook.Worksheets("Events").Select
ActiveWorkbook.Worksheets("Events").Activate
mystr = Cells(x, 3)
Worksheets.Add(After:=Worksheets(Worksheets.Count)).Name = Cells(x, 2)
 With ActiveSheet.QueryTables.Add(Connection:= _
        mystr, Destination:=Range( _
        "$A$1"))
        .Name = "rankings"
        .FieldNames = False
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .BackgroundQuery = False
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .WebSelectionType = xlAllTables
        .WebFormatting = xlWebFormattingNone
        .WebPreFormattedTextToColumns = True
        .WebConsecutiveDelimitersAsOne = True
        .WebSingleBlockTextImport = False
        .WebDisableDateRecognition = False
        .WebDisableRedirections = False
        .Refresh BackgroundQuery:=False
    End With
Next x
End Sub

unfortunately, upon running it, nothing happens. I've attempted to isolate it, and by setting x to any single number, it will work and create a single sheet. When attempting the run the loop however, as I said, I don't get a result. Any suggestions would be VERY appreciated.

BONUS question: I'm sure this wouldn't likely be TOO difficult, but if instead of creating individual sheets for each new import, I wanted the data from all webpages to go into the same sheet, how might I go about that? (All data will be in the same format, (that is to say, same number of columns, though the amount of rows differes from page to page.

Thank you so much for any help you can provide!

Range.MergeCells in Matlab

I am trying to perform a cell merge on an Excel file from Matlab. I don't know anything about this, but tried to use some code I found elsewhere online and adapted it a bit:

Excel = actxserver('Excel.Application');
Workbooks = Excel.Workbooks;
Excel.Visible = 0;
Workbook = Excel.Workbooks.Open('C:\Users\path&filename*.xlsx');
for k=1:length(B)-1
    rng = [ExcelCol((k-1)*MaxH+2),num2str(1),':',ExcelCol(k*MaxH+2),num2str(1)];
    procrng = [rng{:}];
    Range = Excel.Range(procrng);
    Range.Select;
    Range.MergeCells = True;
    Range.HorizontalAlignment = xlCenter;
end

ExcelCol is a user-defined function I found online that converts column number to Excel alphabet notation. It works - no doubt about it.

But when I run & step through the code, I get an error at Range.MergeCells = True. I get: "Undefined function or variable True'.

Can you please help?

XSSF (POI) - Adding "formula" column to pivot table

I am using POI 3.12-beta1:

<!-- Apache POI (for Excel) -->
<dependency>
    <groupId>org.apache.poi</groupId>
    <artifactId>poi</artifactId>
    <version>3.12-beta1</version>
</dependency>

<dependency>
    <groupId>org.apache.poi</groupId>
    <artifactId>poi-ooxml</artifactId>
    <version>3.12-beta1</version>
</dependency>

<dependency>
    <groupId>org.apache.poi</groupId>
    <artifactId>ooxml-schemas</artifactId>
    <version>1.1</version>
</dependency>

I am trying to create a calculated pivot table column which is defined as: = 'Ended' / 'Generated' * 100.

I went ahead and manually edited the sheet in Excel to get this to work, and when I reversed the *.xlsx file into a ZIP directory and looked through it, I found the following code in \xl\pivotCache\pivotCacheDefinition1.xml:

<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<pivotCacheDefinition xmlns="http://ift.tt/K9sZsw" xmlns:r="http://ift.tt/1bA4cfb" r:id="rId1" refreshOnLoad="1" refreshedBy="vyasrav" refreshedDate="42110.580247453705" createdVersion="3" refreshedVersion="3" minRefreshableVersion="3" recordCount="352">
    <cacheSource type="worksheet">
        <worksheetSource ref="A1:O353" sheet="Data"/>
    </cacheSource>
    <cacheFields count="16">
        <!-- OMITTED -->
        <cacheField name="Avg Pct Processed" numFmtId="0" formula="'Ended' / 'Generated' * 100" databaseField="0"/>
    </cacheFields>
</pivotCacheDefinition>

So I went back to my java program and added the following code to generate it automatically, but it isn't registering data column "15" and I am getting an IndexOutOfBounds error.

<pre><code>// Add pivot (pivot table):
Sheet pivotSheet = workbook.createSheet("Pivot");
LOGGER.trace("Created sheet: '" + String.valueOf(pivotSheet) + "'.");

XSSFPivotTable pivotTable = ((XSSFSheet)pivotSheet).createPivotTable(new AreaReference(tableRange), new CellReference("A1"), dataSheet);
CTPivotTableDefinition ctPivotTableDefinition = pivotTable.getCTPivotTableDefinition();
CTPivotTableStyle ctPivotTableStyle = ctPivotTableDefinition.getPivotTableStyleInfo();
ctPivotTableStyle.setName("PivotStyleMedium4");

// Row Labels:
pivotTable.addRowLabel(...); // ...
...

<b><i><span style='background:FFFF00;'>// Add column 15 (this is a calculated column):
CTCacheFields ctCacheFields = pivotTable.getPivotCacheDefinition().getCTPivotCacheDefinition().getCacheFields();
CTCacheField ctCacheField = ctCacheFields.addNewCacheField();
ctCacheField.setName("Avg Pct Processed");
ctCacheField.setFormula("'Ended' / 'Generated' * 100");</span></i></b>

// Column Labels:
pivotTable.addColumnLabel(DataConsolidateFunction.SUM, 8, "Sum of Generated");
pivotTable.addColumnLabel(DataConsolidateFunction.SUM, 12, "Sum of Ended");
pivotTable.addColumnLabel(DataConsolidateFunction.SUM, 13, "Sum of Unended");
<b><i>pivotTable.addColumnLabel(DataConsolidateFunction.SUM, 15, "Average of Processed Percent");</i></b>
...
</code></pre>

The StackTrace of the IndexOutOfBoundsException which occurs on the bolded line above is:

Exception in thread "main" java.lang.IndexOutOfBoundsException
    at org.openxmlformats.schemas.spreadsheetml.x2006.main.impl.CTPivotFieldsImpl.setPivotFieldArray(Unknown Source)
    at org.apache.poi.xssf.usermodel.XSSFPivotTable.addDataColumn(XSSFPivotTable.java:372)
    at org.apache.poi.xssf.usermodel.XSSFPivotTable.addColumnLabel(XSSFPivotTable.java:296)
    at com...

Does anyone know how can I use POI to generate this column?

Thanks!

Excel 2013 Windows Class Names

Have to learn a little API for my VBA project so am experimenting. The interwebs suggests this code should work in a sub to find a windowshandle

Private Declare PtrSafe Function FindWindowA Lib "user32" (ByVal lpClassName    As String, ByVal lpWindowName As String) As LongPtr

Public Sub Test()
Dim P As String
P = Windows(1).Caption
MsgBox P
MsgBox FindWindowA("", P)
End Sub

With a sheet open with caption "Book1.xlsm" when I run this code I get "Book1.xlsm" but then "0"

What am I doing wrong? If I try "EXCEL7" as a class name instead of the null string, I get the same thing.

Thanks to those older and wiser

Added later after discussion below.... This isn't really an answer to my question as it's not clear on the face of it why windows can't be found via FindWindowA (that's what it claims to do, as I read it) but further research suggests that I can't get the window handles directly via FindWindowA, but have to take into account that they might be child windows. So this code at least find my window handle:

Private Declare Function FindWindow _
Lib "user32" _
    Alias "FindWindowA" ( _
        ByVal lpClassName As String, _
        ByVal lpWindowName As String) _
As Long

Private Declare Function FindWindowEx _
Lib "user32" _
    Alias "FindWindowExA" ( _
        ByVal hWnd1 As Long, _
        ByVal hWnd2 As Long, _
        ByVal lpsz1 As String, _
        ByVal lpsz2 As String) _
 As Long
Dim hWndParent As Long, hWndDskTop As Long, hWndChild As Long, hWndMine As        Long
Sub Test()
hWndParent = FindWindow("XLMAIN", vbNullString)
hWndDskTop = FindWindowEx(hWndParent, 0&, "XLDESK", vbNullString)
hWndMine = FindWindowEx(hWndDskTop, 0&, "EXCEL7", vbNullString)
MsgBox hWndMine

That is finding the window handle of Book1.xlsm as I originally wanted.

Excel formula maybe Vlookup?

Ok so this is what I want to do but I want a formula(I think it might be =Vlookup maybe?) I know thhe process but am quite new to excel so any help would be appreciated.

If the value of cell B2 is ANYWHERE ELSE in that column and has a value in the same row but next column(C2) take the value in column D in the same row and put it in the original row’s (i.e row 2) column E.

Thanks for having a look!

Creating a button on Excel Ribbon to open Userform Window

I have developed a VBA userform interface that allows the user to input values, have it calculate results, and then print them out into the worksheet. I am wondering if there is any possible way that I can have a button on the excel ribbon, say under the "Data" tab for instance, that would elicit the userform interface to open.

Currently the only way I can use the interface is by Developer>>Visual Basic>>Clicking on Userform>>F5. This also limits the userform in that it can only be used in one particular workbook as opposed to many. Or if it was somehow possible, to make it an add-in instead.

Any help is greatly appreciated!

CountIfs not working with decimals

I have a worksheet with about 7000 rows of data which I want to analyze using CountIfs. The data consists of decimal numbers such as "0,05234234" (the decimal "point" is a comma due to country settings). My CountIfs code looks like this:

    For i = 1 To 6
        lastRow = w3.Worksheets(i).Cells(w3.Worksheets(i).Rows.count, 1).End(xlUp).Row
        For j = 1 To n
            tmpRow = 2
            Set rng = w3.Worksheets(i).Range(w3.Worksheets(i).Cells(2, 1 + j), w3.Worksheets(i).Cells(lastRow, 1 + j))
            Do While w1.Worksheets(i).Cells(tmpRow, 1) <> ""
                If tmpRow = 2 Then
                    above = CDbl(-100) 'first element
                Else
                    above = w1.Worksheets(i).Cells(tmpRow - 1, 1)
                End If
                curr = w1.Worksheets(i).Cells(tmpRow, 1)
                ccount = Application.WorksheetFunction.CountIfs(rng, ">" & above, rng, "<=" & curr)
                w1.Worksheets(i).Cells(tmpRow, 1 + j) = ccount
                tmpRow = tmpRow + 1
            Loop
            above = w1.Worksheets(i).Cells(tmpRow - 1, 1)
            w1.Worksheets(i).Cells(tmpRow, 1 + j) = Application.WorksheetFunction.CountIf(rng, ">" & above)
            tmpRow = tmpRow + 1
        Next j
    Next i

The values of "above" and "curr" are evenly spaced like this -2,-1,99,..,0,..,1,99,2.

However, it's not working and it's driving me crazy. The value of "ccount" is 0 for every iteration, except the very last after the loop. For that one, "above" is equal to 2, an integer. It turns out the CountIfs will only work if "above" and "curr" are integers. Why is that happening and how do I fix this?

Thanks!

Return a Value On Two Criteria

I have a master data spreadsheet and a summary cover. The master contains a list of names as the rows and dates as the columns. The data in the cell is sales.

On the summary, I have the names as the rows, then this week and last weeks ending date as columns. These are calculated using =TODAY() and =TODAY()-7.

I would like the cover sheet to do some kind of look up to the master data, to look up by name, then return last weeks and this weeks values. I know these two values would be different formulas.

Is there a lookup that can use the two week formulas to return values from the master data, using the name as well? I have tried vlookup and hlookup to no avail.

Thanks in advance

Issue with Excel VBA range as global variable

I'm having a UserForm, which i use for fast data entry. When the UserForm get initialized some shortcuts are assigned for different macros. One of these macros deletes whole lines from my data sheet. Another macro should be used to restore such a deletion. For this i use a range as global variable for backup of the deletion. However, the range is not in scope..

Please see the relevant code snippets and the error:

Form Code:

Public Sub UserForm_Initialize()

    'set shortcuts
    '...

    Application.OnKey "{BACKSPACE}", "delete_row"
    Application.OnKey "^z", "redo_row"
    '...

    '<curRow> is global variable (in Module1) which is needed for PicBro Userform..
    curRow = ActiveCell.Row

    '...       
End Sub

---

Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer)

    '...

    'release shortcuts
    Application.OnKey "{BACKSPACE}"
    Application.OnKey "^z"
    '...

End Sub

Module 1 code:

'global variables in module1
'****************************
Public curRow As Long
Public rngBU As Range
'**************************** 
Public Sub delete_row()

    '...
    Set rngBU = Data.Rows(curRow)
    MsgBox "Copied row " & rngBU.Row & " for backup.."

    Data.Rows(curRow).delete Shift:=xlUp
    Data.Cells(curRow, 1).Activate

    '...
End Sub

---

Public Sub redo_row()
    MsgBox "Restoring deleted row " & rngBU.Row & " from backup.." 'ERROR runtime 424
    rngBU.Copy
    Data.Rows(curRow).Insert Shift:=xlDown
End Sub

Median/average does not return the right values

Picture for reference

I'm trying to achieve the following: if(cell A1 is found in list 1), for each row in which it's found and if(C4:C10 > B4:B10), then median(the subtraction between C and B values, for every row that has text1).

I've tried two 2 different formulas:

1 - {=MEDIAN(IF(AND((C4:C10>B4:B10);(B4:B10=A1));(C4:C10-B4:B10)))}

2 - {=MEDIAN((C4:C10>B4:B10)*(B4:B10=A1)*(C4:C10-B4:B10))}

For median it always returns 0 and for the average really small values that aren't accurate. I'm sure the median and the averages aren't correct.

What would the problem be?

Also, how would I use something like: {=MEDIAN((C4:C10>B4:B10)*(B4:B10=A1)*(C4:C10-B4:B10))}

If one the columns had text in some rows? (which isn't the case for the former problem, but it has arisen before).

excel vba mysql ado connection

I'm trying to establish an ADO connection between excel on my local machine and a MySQL database on my server.

In the examples I've seen (here and here, for instance) there's a driver of the form MySQL ODBC 5.x Driver. It seems that after installing the latest mysql connector / odbc download the relevant registry driver files HKEY_LOCAL_MACHINE\SOFTWARE\ODBC\ODBCINST.INI\ODBC Driver\ are now named 'SQL Server' and 'SQL Server Native Client 11.0.' I'm not having success establishing a connection to MySQL with either of these.

My VBA

Sub connect()
Dim Password As String
Dim SQLStr As String
Dim Server_Name As String
Dim User_ID As String
Dim Database_Name As String

Set rs = CreateObject("ADODB.Recordset") 'EBGen-Daily
Server_Name = "184.154.225.243"
Database_Name = "*******" ' Name of database
User_ID = "********" 'id user or username
Password = "*******" 'Password
Port = "3306"

SQLStr = "SELECT * FROM *******"

Set Cn = CreateObject("ADODB.Connection")
Cn.Open "Driver={SQL Server};Server=" & _
        Server_Name & ";Port=" & Port & ";Database=" & Database_Name & _
        ";Uid=" & User_ID & ";Pwd=" & Password & ";"

rs.Open SQLStr, Cn, adOpenStatic

Upon running the above, I receive error [Microsoft][ODBC SQL Server Drive][DBNETLIB]SQL Server does not exist or access denied. The error for the 'native client 11.0' driver is Could not open a connection to SQL Server[53].

I've tested the connection parameters in MySQL workbench and all is functional. What's going on?

Excel Rows (1st column) as .php files + Excel Row 1 (2nd column to infinty) as data of all files

I found relevent things but not the exact match and I don't know how fix them.

I have an excel file with only 1 row(with many columns) and 1 column (with many rows).

Now I would like to use vba and create files from all the rows of 1st column and , put the same data (i.e. cloumn b to infinty of 1st row) inside all the files.

Just an example:

excel file:

a 2 3 4
b
c

files would be:

a.php, b.php, c.php

with same data inside them:

2 3 4

Thanks in advance

Workbook.CheckIn always produces an error the first time it's called?

I have some workbooks stored in a document library on Sharepoint 2007. I want to check out a workbook, modify it, and check it back in.

Using the following code:

Option Explicit

Public Declare Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As Long)

Sub test()

Dim bk As Workbook
Dim path As String

path = "http://sharepoint/sites/test/TEST_Relink.xlsm"

If Workbooks.CanCheckOut(path) Then
    Application.DisplayAlerts = False
    Workbooks.CheckOut path
    DoEvents
    Set bk = Workbooks.Open(path, False)
    bk.Sheets("test").Range("h1").Value = "modified " & Now
    DoEvents
    Sleep 10000
    bk.checkIn True
    Application.DisplayAlerts = True
End If


End Sub

The bk.checkIn call always produces the following run-time error:

Method 'CheckIn' of object '_Workbook' failed

After I go into Debug, I press F5 to continue and the check-in always occurs successfully.

I added the 10-second delay with Sleep 10000 because I was thinking that maybe the check-out was taking a while to propagate to the server. But no matter how much time I set for Sleep, this same issue keeps occurring. Any thoughts?

convert simple VB script to MATLAB

I have some problem converting simples VB scripts (formating) into MATLAB:

VB script:

Range("A1").Select
Selection.Font.Italic = True
With Selection.Borders(xlEdgeBottom)
    .LineStyle = xlContinuous
End With

I tried:

xlswrite('test.xls',1,'A1');
Excel = actxserver('Excel.Application');
Excel.Workbooks.Open('test.xls');

Range = Excel.Range('A1');
Range.Font.Italic = True; % Doesnt work
Range.Border.Item('xlEdgeRight').LineStyle = 1; % Doesnt work

Excel.Visible = 1;

Any workaround? Thanks

Search the xcel data for the number in a cell. If yes then copy a cell from that row to another cell

Hi the below data is in xcel. I need to put the downstream number eg.6.25(cell L2) into Cell L1. But this has to be found from the database. So if Cell B1 matches anywhere in column B copy the L value from that row and copy to the L column in the original row i.e L1.

I'm really struggling with this one and have a deadline today!! Thanks in advance!

PIPE 17888 Upstream Pit ID 20447 Concrete Circular Pipe 1/04/2015 380 6.36 img008.jpg

PIPE 17888 Downstream Pit ID 22105 Concrete Circular Pipe 1/04/2015 380 Downstream 6.25 img010.jpg

PIPE 17854 Downstream Pit ID 22105 Concrete Circular Pipe 1/04/2015 380 Downstream 6.36 img010.jpg US PIPE NOT FOUND PIPE 17811 Upstream Pit ID 22105 Concrete Circular Pipe 1/04/2015 380 6.25 img010.jpg

PIPE 18105 Downstream Pit ID 23037 Concrete Circular Pipe 1/04/2015 600 Downstream 5.94 img014.jpg

POST website form data and retrieve results

I have been trying to write a VBA code to copy these three tables as shown in the web source code below. These tables show monthly weather data. Could someone please help me write a code to copy this data and paste it in a Excel sheet? I have written a VBA code to access this data but could not copy and paste this data. Thank you very much in advance.

The webpage source code:

 </table>
</div>
<hr><big><b><i>Parameters for Sizing and Pointing of Solar Panels and for Solar Thermal Applications:</i></b></big>
<hr width="80%">
<a name="clr_sky"></a>
<div align="center"><table border=1 summary="Monthly Averaged Clear Sky Insolation Incident On A Horizontal Surface " width="95%">
<caption><b>Monthly Averaged Clear Sky Insolation Incident On A Horizontal Surface (kWh/m<sup>2</sup>/day)</b></caption><tr><td>Lat 32 <br> Lon 75</td>
<td>Jan</td><td>Feb</td><td>Mar</td><td>Apr</td><td>May</td><td>Jun</td>
<td>Jul</td><td>Aug</td><td>Sep</td><td>Oct</td><td>Nov</td><td>Dec</td>
<td>Annual<br>Average</td></tr>
<tr><td>22-year Average     </td><td align="center" nowrap>4.17</td><td align="center" nowrap>5.29</td><td align="center" nowrap>6.64</td><td align="center" nowrap>7.92</td><td align="center" nowrap>8.65</td><td align="center" nowrap>8.78</td><td align="center" nowrap>8.31</td><td align="center" nowrap>7.48</td><td align="center" nowrap>6.60</td><td align="center" nowrap>5.63</td><td align="center" nowrap>4.44</td><td align="center" nowrap>3.82</td><td align="center" nowrap>6.48</td>
</tr></table></div>
<div align="center"><b><i>
<a href="/sse/text/definitions.html#clr_sky" onClick="window.open('/sse/text/definitions.html#clr_sky','Definitions','menubar=yes,resizable=yes,scrollbars=yes,toolbar=yes,width=600,height=400'); return false">Parameter Definition</a> &nbsp;  &nbsp;  &nbsp;  &nbsp;  &nbsp;

</i></b></div>
<hr width="80%">
<a name="clr_kt"></a>
<div align="center"><table border=1 summary="Monthly Averaged Clear Sky Insolation Clearness Index " width="95%">
<caption><b>Monthly Averaged Clear Sky Insolation Clearness Index (0 to 1.0)</b></caption><tr><td>Lat 32 <br> Lon 75</td>
<td>Jan</td><td>Feb</td><td>Mar</td><td>Apr</td><td>May</td><td>Jun</td>
<td>Jul</td><td>Aug</td><td>Sep</td><td>Oct</td><td>Nov</td><td>Dec</td>
</tr>
<tr><td>22-year Average     </td><td align="center" nowrap>0.75</td><td align="center" nowrap>0.76</td><td align="center" nowrap>0.76</td><td align="center" nowrap>0.77</td><td align="center" nowrap>0.77</td><td align="center" nowrap>0.76</td><td align="center" nowrap>0.73</td><td align="center" nowrap>0.71</td><td align="center" nowrap>0.72</td><td align="center" nowrap>0.75</td><td align="center" nowrap>0.75</td><td align="center" nowrap>0.74</td>
</tr></table></div>
<div align="center"><b><i>
<a href="/sse/text/definitions.html#clr_kt" onClick="window.open('/sse/text/definitions.html#clr_kt','Definitions','menubar=yes,resizable=yes,scrollbars=yes,toolbar=yes,width=600,height=400'); return false">Parameter Definition</a> &nbsp;  &nbsp;  &nbsp;  &nbsp;  &nbsp;

</i></b></div>
<hr><big><b><i>Meteorology (Other):</i></b></big>
<hr width="80%">
<a name="col_precip"></a>
<div align="center"><table border=1 summary="Monthly Averaged Total Column Precipitable Water " width="95%">
<caption><b>Monthly Averaged Total Column Precipitable Water (cm)</b></caption><tr><td>Lat 32 <br> Lon 75</td>
<td>Jan</td><td>Feb</td><td>Mar</td><td>Apr</td><td>May</td><td>Jun</td>
<td>Jul</td><td>Aug</td><td>Sep</td><td>Oct</td><td>Nov</td><td>Dec</td>
<td>Annual<br>Average</td></tr>
<tr><td>22-year Average     </td><td align="center" nowrap>0.66</td><td align="center" nowrap>0.76</td><td align="center" nowrap>0.97</td><td align="center" nowrap>1.18</td><td align="center" nowrap>1.49</td><td align="center" nowrap>2.19</td><td align="center" nowrap>3.28</td><td align="center" nowrap>3.31</td><td align="center" nowrap>2.20</td><td align="center" nowrap>1.08</td><td align="center" nowrap>0.74</td><td align="center" nowrap>0.66</td><td align="center" nowrap>1.54</td>
</tr></table></div>
<div align="center"><b><i>
<a href="/sse/text/definitions.html#col_precip" onClick="window.open('/sse/text/definitions.html#col_precip','Definitions','menubar=yes,resizable=yes,scrollbars=yes,toolbar=yes,width=600,height=400'); return false">Parameter Definition</a> &nbsp;  &nbsp;  &nbsp;  &nbsp;  &nbsp;

What I have written is this:

Set elemCollection = IE.Document.getElementsByTagname("table")

For t = 0 To (elemCollection.Length - 1)
    For r = 0 To (elemCollection(t).Rows.Length - 1)
        For c = 0 To (elemCollection(t).Rows(r).Cells.Length - 1)

        ThisWorkbook.Worksheets(1).Cells(r + 1, c + 1) = elemCollection(t).Rows(r).Cells(c).innertext
        Next c
        Next r
        Next t

End With

Set IE = Nothing

However, no data comes to the spreadsheet. Could you please suggest what is wrong with the code? Thanks!

Based on Jeeped's suggestion, I tried this code:

Sub extractSolData()
Dim IE As Object
Dim r As Integer, c As Integer, t As Integer
Dim iTD As Long, iTR As Long, eTR As MSHTML.IHTMLElement, ecTRs As IHTMLElementCollection

Set IE = CreateObject("InternetExplorer.Application")

latitude = InputBox("Enter Latitude of the location")
longitude = InputBox("Enter Longitude of the location")

With IE
IE.Visible = True
IE.navigate ("http://ift.tt/1D0KeET")

While IE.readyState <> 4
DoEvents
Wend

IE.document.getElementsByName("lat").Item.innerText = latitude
IE.document.getElementsByName("lon").Item.innerText = longitude

IE.document.getElementsByName("submit").Item.Click
Do While IE.Busy: DoEvents: Loop

For Each obj In IE.document.all.Item("swv_dwn").Options
If obj.Value = "clr_sky" Then
obj.Selected = True
End If

If obj.Value = "clr_kt" Then
obj.Selected = True
End If
Next obj

For Each obj In IE.document.all.Item("RH10M").Options
If obj.Value = "col_precip" Then
obj.Selected = True
End If
Next obj

IE.document.getElementsByName("submit").Item.Click
Do While IE.Busy: DoEvents: Loop

If CBool(IE.document.getElementsByTagName("table").Length) Then
    For iTBL = 0 To (IE.document.getElementsByTagName("table").Length - 1)
        Set ecTRs = IE.document.getElementsByTagName("table")(iTBL).getElementsByTagName("tr")
        For iTR = 0 To (ecTRs.Length - 1)
            If CBool(ecTRs(iTR).getElementsByTagName("th").Length) Then
                For iTD = 0 To (ecTRs(iTR).getElementsByTagName("th").Length - 1)
                    ThisWorkbook.Sheets("Sheet1").Cells(iTR + 1, iTD + 1) = ecTRs(iTR).getElementsByTagName("th")(iTD).innerText
                Next iTD
            ElseIf CBool(ecTRs(iTR).getElementsByTagName("td").Length) Then
                For iTD = 0 To (ecTRs(iTR).getElementsByTagName("td").Length - 1)
                    ThisWorkbook.Sheets("Sheet1").Cells(iTR + 1, iTD + 1) = ecTRs(iTR).getElementsByTagName("td")(iTD).innerText
                Next iTD
            End If
        Next iTR
        Set ecTRs = Nothing
    Next iTBL
End If
End With
Set IE = Nothing

End Sub

However, no data is being copied into the excel file.

Searching and comparing various values using VBA

I have to create a macro which will:

1. Get the value from the cell A1 search this value in column C.


2. If the value in cell A1 exists in column C, the macro needs to be compare the value in cell B1 with values in column D.


3. If the value in cell A1 exists in column C AND the value in cell B1 exists in column D, then the text "Values found" should appear in cell E1.

The above needs to happen for all non empty rows in column A.

I was trying to use the following formula:

=IF(ISERROR(MATCH(A2,$C$2:$C$138,0)),"Load number not found","Load number found")

But it not working as I want. I have limited access to internet so I can't check all web sites. Would you please help me. Thanks a lot.

How to write and IF statement--comparing a column with date-time value with now() in Google Sheets?

I know if date value()

Like Column B has due dates. I need it to say if the date-time listed in Column B is less than now (past the due date), blablabla

So how do i represent the value of the date-time cell so I can compare it with B?

This works for JUST the date: datevalue(B:B)

but it needs to be date-time value and I can't find a function for that! I'm a newb, bear with me...

Convert an excel macro to matlab

I've recorded a macro mi MS Excel. I would like to extract the VBA code and store it into an .m file or a variable in MATLAB. At the end, I want to be able to run the macro from MATLAB (even without having access to the original excel file where my macro was recorded) and create a new excel fileand execute the macro. Thank you.

Sum cells values with a separation of 3 columns in Excel

I would like to create a formula that sums all the values that I'm going to add in the future in some cells of the same row. For example, I would like to add cells D3,G3,J3,M3 and so on (separated 3 rows) in cell D1.

Best regards,

How do I regularly post excel data to a web service?

I have a user requirement that I have been battling with for a while with no success. I need to write an add-in that can read around 100 formula-driven cells (of a specific spreadsheet) once every couple of minutes, and send to a web service.

I'm more than happy to use Excel-DNA or VSTO, but everything I've tried so far causes the user interface to hang for an instant. Would this always be the case if the data is being read from the active spreadsheet (even from a different thread) ?

Excel 2013 VBA CommandBar dropdown empty

I have a problem with an old Excel add-in using an xla file with some VBA code and user interface via forms, and a dll that handles communication to a server. The add-in downloads data and inserts it into the active Excel sheet. This application was written for Office 97 and is working well on all versions of Excel since that except for (some installations of) Excel 2013. Actually, the application works fine also in Excel 2013, except for one little detail. There are a few dropdowns (CommandBarComboBox) that is supposed to be loaded with some information when the sheet is loaded with data, but this doesn't work in all Excel 2013 installations. For example in my own Excel 2013 (32-bit), this happens:

Case 1: I have installed the dll in C:\Windows and the xla file in the XLStart folder of Office 2013. The dropdowns are empty.

Case 2: I have installed the dll in C:\Windows, no xla file in XLStart. Instead I open the xla file after starting Excel from the start menu (I'm running 64-bit Windows 7). After opening, I am asked if I want to enable macros in the file I've loaded. After answering yes and downloaded data, I have information in the dropdowns.

Any ideas anyone?

How to get a specific cell in a user selected range in Excel using C#

I create my frist Excel Add-In and want to finde specific cells in a range the user select.

I know the Columns in which the values are that I need. So I test it with:

Excel.Range selection = Globals.ThisAddIn.Application.Selection as Excel.Range;
Excel.Range firstValue = selection.Columns["I"].Find("*", Type.Missing, Excel.XlFindLookIn.xlValues, Excel.XlLookAt.xlPart,Excel.XlSearchOrder.xlByColumns,Excel.XlSearchDirection.xlNext, false, Type.Missing,Type.Missing);

now I have the first value from column "I"

this works fine when the "selection" the user makes begins in column "A"

If the user select for excample from C2 to H20 the code cant find column "I" I become than an other range, not the "I" column.

I think for him is "I" always "9". When the user select from "C2" to "H20" in the new range is colum "C" = "0" and column "L" = 9 so the code select column "L" even if I write "I" in the code.

Have anyone an idea how can I read the specific column or cell I want inner the selection of the user no matter where the user Begins with his selection?

thank you in advance

Excel-VBA: How to keep the data from Monte Carlo simulation results in the same chart?

Not sure if this is more of an Excel or VBA sort of question but here's my issue:

I'm looking into making something like the following, but can't find any information on how this can be done with VBA without copy/pasting each run to another spreadsheet.

Could someone please let me know if this is possible, or better yet, tell me what's the technical term for keeping the results from a dynamic range in memory and then plotting the results at the end so I can research it myself?

Cheers!

Is cell value between any of a set of non-consecutive ranges?

I have a column of data representing the time stamp of all events that occurred in computer-task (ranging from 0s to 380s) for a psychology experiment.

I am trying to ascertain whether any of the recorded event times occurred during the presentation of the fixation cross.

The fixation cross was presented every 2.5 seconds (starting at 0), and lasted for 1 second. So, the fixation crosses were presented between 0 - 1, 2.5 - 3.5, 5 - 6.5 etc, until 382.5-383.5 .

Is there a way to, for example, use LOOKUP to see if each event-time lies between any fixation cross range? I can only think of writing a huge IF AND formula for this at the moment i.e., IF event-time is equal to, or greater than 0s and less than 1s, then that was during the 1st fixation cross, ALL the way to the last fixation cross! e.g.:

=IF(AND(A2>=B2,A2< C2),TRUE,IF(AND(A2>=B3,A2< C3),TRUE,....IF(AND(A2>=B150,A2< C150),TRUE,)

(with A = event time; B & C = fixation cross start- and end-time, respectively)

This obviously can't be the most efficient way!

Re-calculate formula

I have a Sub with variable "porcen" like this:

Dim porcen as Integer
porcen = Cells(1, 1).Value

After some calculations I have 3 variables. Var1, Var2 and Var3 depend on the variable porcen.

ActiveCell.FormulaR1C1 = "=(IF(R[" & var1 & "]C="""","""",((COUNTIF(R[" & var1+ 1 + var2 & "]C:R[" & var1 + 1 & "]C,R[" & var1 & "]C))-(COUNTIF(R[" & var3 & "]C:R[-5]C,R[" & var1 & "]C)))/(" & var2 & ")))"

I don't know why if I change the value in cell(1,1), the result of the formula remains unchanged.

I wish that when I change the value in Cell(1,1) the result also change.

how to get values from one excel database that do not match with the values in other database in java?

I have two excel databases. first one is database:

Sl_no   Species         State       District        Local_equation                  General_equation    Specific_gravity
1       Cassia fistula  Uttrakhand  Haldwani        0.16019-2.81861*D+16.9328*D*D   (-0.046597*H)^1/2   0.74
2       Shorea robusta  Uttrakhand  Almora           (-0.046597+2.227173*D)^1/2     (-0.046597*D)^1/2   0.48

and second one is Field_Data_1 and it contains two sheets: Plot and Data this is Plot sheet:

Plot_ID Latitude Longitude Altitude Location District State PlotSize_ha Date P1 37.6 40.6 640 Barkot RF, South from flux tower Dehradun Uttrakhand 0.1 31.05.2014

this is data sheet:

Plot_ID Sl_no   Species         Gbh_cm  Height
P1        1     Cassia fistula    13       1
P1        2     Shorea robusta    206      7

Using select query I want to display the names of those species whose district and state does not match with the second and third one.I dont have any idea which qquery to use. I have done this much till now.

if(selected1.contains(tmp1))
        {

                sql10 = "select * from [Plot$],[Data$] where [Data$].Plot_ID = ? ";
                pst10=con.prepareStatement(sql10);
                pst10.setString(1, tmp1);
                rs14=pst10.executeQuery();

                while(rs9.next() && rs14.next())
                {
                    HSSFRow row=sheet.createRow((short)i); 
                    st=rs14.getString("State");
                    dis=rs14.getString("District");
                    sp=rs9.getString("Species");

                    sql11="select * from [database$] where State = ?  and District = ? and Species = ?";
                    pst11=con1.prepareStatement(sql11); 
                    pst11.setString(1, st);
                    pst11.setString(2, dis);
                    pst11.setString(3, sp);
                    rs15=pst11.executeQuery();

                    //if(rs15.next())
                    //{    
                        while(rs15.next())
                        {
                            System.out.println(sp);
                            gbh=rs9.getDouble("Gbh_cm");
                            gbh1=RoundTo2Decimals(gbh);
                            gbhm=gbh/100.0;
                            gbhm1=RoundTo2Decimals(gbhm);
                            diameter=gbh/3.14;
                            diameter1=RoundTo2Decimals(diameter);
                            diameterm=diameter/100.0;
                            diameterm1=RoundTo2Decimals(diameterm);

                            System.out.print(gbh1+" ");
                            System.out.println(diameter1);

                            exp2=rs15.getString("Local_equation");
                            System.out.println(exp2);

                            //System.out.println(exp2);
                            String ss=Double.toString(diameterm1);
                            String regex="D";
                            String strrep=exp2.replaceAll(regex,ss);
                            System.out.println(strrep);
                            d2=eval(strrep);
                            d21=RoundTo2Decimals(d2);
                            System.out.println("Volume is:"+d21);
                            sg=rs15.getDouble("Specific_gravity");
                            System.out.println(sg);
                            biomass=d21*sg;
                            biomass1=RoundTo2Decimals(biomass);
                        }
                    //}
                        if(!rs15.next())
                        {
                            JOptionPane.showMessageDialog(this,"Match not found");
}