with the push to leverage visual studio and dotnet with office based solutions, especially excel, where is the best article or information on how having office sheet with additional binaries and assemblies is sharable. Do this external code get packaged with the spreadsheet what if people start emailing the spreadsheet around. Is th...
I've been trying to pass a sheet in my workbook to a function, but it doesn't seem to be working. The function is Private Sub passToSheet(theData As Variant, Optional mySheet As Worksheet) I have tried doing Dim mySheet as Worksheet Set mySheet = "Results" then to call the function passToSheet theData mySheet but it doesn't seem...
I've got a list of transaction dates and the user id of the person who made the transaction on that date (just 1 Tx/day allowed). For example: I'd like to create a matrix which shows, as of each date, the number of users who have made 1 transaction, 2-10 transactions, 10-20 transactions, etc. For example (note, the below data doesn'...
I want to submit my submissions to this competition automatically from my code. I need to log-in on this page and then submit a file on this page. I'd like to use cURL since it integrates with both of the languages that I am using (R and Python). I am just wondering if this procedure is possible in cURL? and my another question is if I...
I want to extract large amounts of data from Excel, manipulate it and put it back. I have found the best way to do this is to extract the data from an Excel Range in to a large array, change the contents on the array and write it back to the Excel Range. I am now rewriting the application using VB.NET 2008/2010 and wish to take advantag...
Hi all, I'm writing a Excel class using Microsoft.Interropt.Excel DLL. I finish all function but I have an error in my Destructor. I Want to save all changes to my file and I want to release all source. I want to all of them in my destructor. But In my destructor, Excel.ApplicationClass, Workbook and Worksheet objects are fill by an Exc...
I'd like to query an Excel worksheet in VBA and specify conditions. The simple query "SELECT * FROM [PCR$]" works perfectly, but I don't know how to add a WHERE clause. I tried cmd2.CommandText = "SELECT * FROM [PCR$] WHERE ([B1] IS NOT NULL)" but then it complains about missing parameters. This is the complete code: Dim rs2 As New ...
I have some code which I found online and it's working great. It lists any files in a specified folder and their properties. I just need to amend this function which returns a list of files. I need it to look in a given path at ALL files. So if it's a folder, open it and return those files too. I eventually need to get this to mark each ...
Note: In the MySql database, "dob" is varchar and in this format: mm/dd/yyyy Date of birth, Cvrg Effective Date, & Cvrg Expiration Date need to be changed from mm/dd/yyyy to yyyymmdd when it arrives in excel. Any help is greatly appreciated. Please see the code below: <?php if($qry == "ok"){ // get db connection inc...
I am writing an excel class and i want to release this unmanaged object automatically. I'm using IDisposable pattern and write Dispose methods. Example ; class MSExcel : IDisposable { ApplicationClass excel; bool disposed; public MSExcel() { disposed = false; excel = new ApplicationClass(); } ...
I have been trying to import this excel file my mysql database, and it has been rough. There are around 7,000 rows in the .xls file, but when I export it to .csv and parse it with PHP I get one of two issues: Excel does not escape anything, ie. the commas in each row split the data When I change all commas to * then export for some rea...
Hi, I am currently developing a 64-bit C# application that needs to import its data from an Excel spreadsheet. I have heard that the best way to do this is by using ADO data connections. However, I have read (and experienced) that in order to make this work, I must be writing a 32-bit application as there is no OleDb odbc driver for ...
I have a similar question to this one: http://stackoverflow.com/questions/621862/merge-the-contents-of-2-cells-into-another-3rd-cell-using-vba-in-excel But I want to combine a range of cells within a column, eg A2:A50. Sometimes I have over 300 cells to be combined into one. Values are text. Is there any way to modify this macro so tha...
I have 15,000 numbers ranging between 1000-8000. Each number is assigned to one of six categories and subcategories, in some cases there are overlaps. Sample data: value cat1 cat2 2333 WHITE A 2334 ORANGE B 2335 ORANGE A 2335 WHITE B 2336 ORANGE A 2336 WHITE B 2336 RED C 2337 RED A 2338 RED A 2...
MS Query, when connecting to SQL Server through an ODBC Data Source, uses the system procedure "sp_tables" to retrieve a listing of tables in the database. The source for sp_tables can be viewed but not altered. The problem with this procedure is that it will not return a table name for which the connected user has permissions to acces...
Let's say I want to find the value of a cell in an Excel file. In VBA, I would do this: Dim varValue As Variant Dim wb As Workbook Dim ws As Worksheet Set wb = ThisWorkbook Set ws = wb.Worksheets("Sheet1") varValue = ws.Range("A1").Value How do I set up a Visual Studio project so I can access and modify an Excel file in C# rather tha...
I am trying to dynamically get the range of cells in Excel using VS 2005 vb.net. This works oRange = oSheet.Range(oSheet.Cells("A1"), ("U281")).Select, but "U281" will not always be the last cell in the range. So how would I dynamically get the last cell with data in it with the same format as U281. Thanks, Lora ...
I'm developing a kind of template with Excel 2007. Users will use it to create records which fall in 3-level categories. A user should be able to create a new category, specify an existing category or not specify one. A record may belong to multiple categories. I'm wondering what my best choice is to present the category structure to us...
I have a table that contains sales transaction (~20 mil rows). Previously I used SQL Server and export it to an Excel pivot. Data refresh took 10-15 minutes but still do-able. However, after I migrated to MySQL (using XamppLite & ODBC), it took forever to refresh the data in the Excel pivot. Maybe I didn't optimize the MySQL good enough...
Is there a way to hide Excel windows from a user - to prevent acidental closing it. ...