Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium


Read XLSM file via VB.NET and ASP.NET Getting #REF! where data should be

Posted on 2012-08-20
Medium Priority
Last Modified: 2012-08-20
I have been able to read a XLSM(Excel) file via vb.net and asp.net and pass data to a gridvew. That part works the problem I'm having is. Im getting a #REF! where data should be.

The Excel file works great on its own, after enabling macro contact, but I'm unable to get GridView to take in calculated data it just display #REF! instead.

Code I'm using:
     Dim DBConnection = New OleDbConnection( _
  "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & Server.MapPath("Staffing.xlsm") & ";" & _
  "Extended Properties=""Excel 12.0 Macro;HDR=YES;IMEX=1""")

        Dim SQLString As String = "SELECT * FROM [Staffing$]"
        Dim DBCommand = New OleDbCommand(SQLString, DBConnection)
        Dim DBReader As Data.IDataReader = DBCommand.ExecuteReader()

        gv.DataSource = DBReader


I tried with no imex same problem, screenshot attached
Question by:marcuschang
  • 2

Accepted Solution

marcuschang earned 0 total points
ID: 38314120
Disabling security in spreadsheet solved problem
Thanks though

Author Closing Comment

ID: 38314125
I'm getting smarter :)

Featured Post

[Webinar On Demand] Database Backup and Recovery

Does your company store data on premises, off site, in the cloud, or a combination of these? If you answered “yes”, you need a data backup recovery plan that fits each and every platform. Watch now as as Percona teaches us how to build agile data backup recovery plan.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article describes a serious pitfall that can happen when deleting shapes using VBA.
Windows Explorer lets you open cabinet (cab) files like any other folder. In VBA you can easily handle normal files and folders, but opening and indeed creating cabinet files takes a lot more - and that's you'll find here.
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …

579 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question