Problem with using OleDbDataAdapter to fetch data from a Excel sheet

First, I want to say that I’m out on deep water here, since I’m just doing some changes to code that is written by someone else in the company, using OleDbDataAdapter to “talk” to Excel and I’m not familiar with that. There is one bug there I just can’t follow. I’m trying to use a […]

VBA Excel macro: use Range to act on a different worksheet

I am very much a beginner when it comes to VBA programming. I have a Macro that hides or shows columns based on the value in one cell: Sub HideColumnsMacro() Range(“b8:o8”).EntireColumn.Hidden = False v1 = Range(“b2”).Value + 1 If v1 < 12 Then With Range(“b8”) Range(.Offset(0,v1), .Offset(0, 12)).EntireColumn.Hidden = True End With End If End […]

Find where named ranges are being used in big workbook

I have a list of 594 named ranges in a workbook with nearly 20 sheets, each sheet has about 200 columns of data. I need to find out where the named ranges are being used so as to remove irrelevant ones. I pasted a list of named ranges onto the sheet and then I tried […]

How to read Excel cell having Date with Apache POI?

I’m using Apache POI 3.6, I want to read an excel file which has a date like this 8/23/1991. switch (cell.getCellType()) { … … case HSSFCell.CELL_TYPE_NUMERIC: value = “NUMERIC value=” + cell.getNumericCellValue(); break; … } But it takes the numeric value type and returns the value like this 33473.0. I’ve tried to use Numeric Cell […]

How excel reads XML file?

I have researched a lot to convert an xml file to 2d array in a same way excel does trying to make same algorithm as excel does when you open an xml file in excel. <items> <item> <sku>abc 1</sku> <title>a book 1</title> <price>42 1</price> <attributes> <attribute> <name>Number of pages 1</name> <value>123 1</value> </attribute> <attribute> <name>Author […]

Extracting Unique values from a list

I have the following code that returns 50 random color-coded numbers: Sub RandomNumberColor() Dim Numbers, i As Integer Dim MyRange As Range Set MyRange = Worksheets(“Rnd”).Range(“A1:A50”) For i = 1 To MyRange.Rows.Count Numbers = Int((10 – 1 + 1) * Rnd + 1) Worksheets(“Rnd”).Cells(i, 1) = Numbers Worksheets(“Rnd”).Cells(i, 1).Interior.ColorIndex = Worksheets(“Rnd”).Cells(i, 1).Value Next i End […]

Get the data from excel files in sub directories

I am new to VBA and to programming in general. This is my first post on this board. I’ve been working on this for a while modifying code I’ve found on the internet and I have the code to do what I want, however I would like to modify it slightly to speed up the […]

“Unable to set the NumberFormat property of the Range class”

This code has been working for ages. I thought maybe I accidently pressed a key but I cannot seem to see it. I suddenly get the error: Unable to set the NumberFormat property of the Range class in the below code: Worksheets(“Sheet1”).Cells(y + k, x + j).Select TempValue = Worksheets(“Sheet1”).Cells(y + k, x + j).Value […]

Can Excel interpret the URLs in my CSV as hyperlinks?

Can Excel interpret the URLs in my CSV as hyperlinks? If so, how?

Data Conversion Issue in SSIS package – Text to GUID

I am developing a SSIS package that will open an Excel spreadsheet and import the data into a database table in SQL Server 2008. When I try to convert the Excel column data type: Unicode String [DT_WSTR] to a unique identifier data type: unique identifier [DT_GUID], I get the following error: “Invalid character value for […]

MS Excel Spreadsheet is the best Office Software, Excel VBA and Excel Formulas make Spreadsheet work faster.