Search Results

Search found 4901 results on 197 pages for 'excel macros'.

Page 54/197 | < Previous Page | 50 51 52 53 54 55 56 57 58 59 60 61  | Next Page >

• Insert blank row on two conditions

  - by lost_my_wallet_in_el_segundo

  I have a spreadsheet with a large number of rows. There are two rows grouped together (for each customer). In column A, the first row has an account number. The second row should be blank. The spreadsheet has lots of customers listed where there is no second row. I need to insert a blank line to create a second row for each customer that doesn't have one. Here is the VBA script I cobbled together, but it gets a syntax error. Sub Macro1() ' ' Macro1 Macro ' For myrow = 1 To Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row If Cells(myrow, 1) <> "" and Cells(myrow+1, 1) <> "" Then Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove End Sub

  Read the article

• "interface not found" in WCF Moniker without registration for excel

  - by tbischel

  I'm trying to connect excel to a WCF service, but I can't seem to get even a trivial case to work... I get an Invalid Syntax error when I try and create the proxy in excel. I've attached the visual studio debugger to excel, and get that the real error is "interface not found". I know the service works because the test client created by visual studio is ok... so the problem is in the VBA moniker string. I'm hoping to find one of two things: 1) a correction to my moniker string that will make this work, or 2) an existing sample project to download that has the source for both the host and client that does work. Here is the code for my VBA client: Dim addr As String addr = "service:mexAddress=net.tcp://localhost:7891/Test/WcfService1/Service1/mex, " addr = addr + "address=net.tcp://localhost:7891/Test/WcfService1/Service1/, " addr = addr + "contract=IService1, contractNamespace=http://tempuri.org, " addr = addr + "binding=NetTcpBinding_IService1, bindingNamespace=""http://tempuri.org""" MsgBox (addr) Dim service1 As Object Set service1 = GetObject(addr) MsgBox service1.Test(12) I have the following service: [ServiceContract] public interface IService1 { [OperationContract] string GetData(int value); } public class Service1 : IService1 { public string GetData(int value) { return string.Format("You entered: {0}", value); } } It has the following config file: <?xml version="1.0" encoding="utf-8" ?> <configuration> <system.web> <compilation debug="true" /> </system.web> <!-- When deploying the service library project, the content of the config file must be added to the host's app.config file. System.Configuration does not support config files for libraries. --> <system.serviceModel> <services> <service behaviorConfiguration="WcfService1.Service1Behavior" name="WcfService1.Service1"> <endpoint address="" binding="netTcpBinding" bindingConfiguration="" contract="WcfService1.IService1"> <identity> <dns value="localhost" /> </identity> </endpoint> <endpoint address="mex" binding="mexTcpBinding" bindingConfiguration="" contract="IMetadataExchange" /> <host> <baseAddresses> <add baseAddress="net.tcp://localhost:7891/Test/WcfService1/Service1/" /> </baseAddresses> </host> </service> </services> <behaviors> <serviceBehaviors> <behavior name="WcfService1.Service1Behavior"> <serviceMetadata httpGetEnabled="false" /> <serviceDebug includeExceptionDetailInFaults="false" /> </behavior> </serviceBehaviors> </behaviors> </system.serviceModel> </configuration>

  Read the article

• Is it possible to get event after macros is enabled in Excel

  - by Sathish

  Is it possible to get event after macros is enabled in Excel in VSTO

  Read the article

• Purpose of IF, ELSE, FOR macros ?

  - by psihodelia

  I have a source code of a library which has a lot of strange IF, ELSE, FOR, etc. macros for all common C-keywords instead of using just usual if,else,for,while keywords. These macros are defined like this: #define IF( a) if( increment_if(), a) where increment_if() function is defined so: static __inline void increment_if( void) { // If the "IF" operator comes just after an "ELSE", its counter // must not be incremented. ... //implementation } I don't really understand, what is the purpose of such macros? This library is for a real-time application and I suppose that using such macros must slow-down an application.

  Read the article

• What is the logic behind defining macros inside a struct?

  - by systemsfault

  As apparent in the title, I'm questioning the reason behind defining the macros inside a struct. I frequently see this approach in network programming for instance following snippet: struct sniff_tcp { u_short th_sport; /* source port */ u_short th_dport; /* destination port */ tcp_seq th_seq; /* sequence number */ tcp_seq th_ack; /* acknowledgement number */ u_char th_offx2; /* data offset, rsvd */ #define TH_OFF(th) (((th)->th_offx2 & 0xf0) >> 4) u_char th_flags; #define TH_FIN 0x01 #define TH_SYN 0x02 #define TH_RST 0x04 #define TH_PUSH 0x08 #define TH_ACK 0x10 #define TH_URG 0x20 #define TH_ECE 0x40 #define TH_CWR 0x80 #define TH_FLAGS (TH_FIN|TH_SYN|TH_RST|TH_ACK|TH_URG|TH_ECE|TH_CWR) u_short th_win; /* window */ u_short th_sum; /* checksum */ u_short th_urp; /* urgent pointer */ }; This example is from sniffex.c code in tcpdump's web site. Is this for enhancing readability and making code clearer.

  Read the article

• Is it a good idea to apply some basic macros to simplify code in a large project?

  - by DoctorT

  I've been working on a foundational c++ library for some time now, and there are a variety of ideas I've had that could really simplify the code writing and managing process. One of these is the concept of introducing some macros to help simplify statements that appear very often, but are a bit more complicated than should be necessary. For example, I've come up with this basic macro to simplify the most common type of for loop: #define loop(v,n) for(unsigned long v=0; v<n; ++v) This would enable you to replace those clunky for loops you see so much of: for (int i = 0, i < max_things; i++) With something much easier to write, and even slightly more efficient: loop (i, max_things) Is it a good idea to use conventions like this? Are there any problems you might run into with different types of compilers? Would it just be too confusing for someone unfamiliar with the macro(s)?

  Read the article

• Using ExcelPacke to create Excel sheets on server

  - by DigiMortal

  In one of my community projects I needed to output some listings as Excel file. As installing Excel to server is non-sense that I was easily able to avoid I found simple solution for Excel 2007 files – open-source project called ExcelPackage. In this posting I will show you hot to create simple event attendees report in Excel 2007 format using ExcelPackage. Cautions Although ExcelPackage works well for me here are some things you should be aware of. ExcelPackage needs file system access because compression library it uses is designed so. There is only very old source code available and it is published under GPL. So if you are writing application to your customers then you cannot use this library unless you make your whole application open-source. ExcelPackage has also some technical problems and it is not very easy to use in simple cases. Authors have not provided any new releases since the beginning of 2007 so I have good reason to consider this project as abandoned. You may find the extensive package EPPlus also useful as there are new versions coming over time. EPPlus is also published under GPL (because ExcelPackage is under GPL), so you can use it only on very limited manner. If you don’t afraid some s*itfight with technology and GPL is okay for your system then let’s go on. Exporting event attendees list to Excel Suppose we have list with event attendees and we want to export it to Excel. We are behaving normally and we don’t install Excel desktop software to our web server. Here is the code. void ExportToExcel(Event evt) {     var fileInfo = new FileInfo(Path.GetTempPath() + "\\" +                                  DateTime.Now.Ticks + ".xlsx");       using (var xls = new ExcelPackage(fileInfo))     {         var sheet = xls.Workbook.Worksheets.Add(evt.Title);           sheet.Cell(1, 1).Value = "First name";         sheet.Cell(1, 2).Value = "Last name";         sheet.Cell(1, 3).Value = "E-mail";         sheet.Cell(1, 4).Value = "Phone";         sheet.Cell(1, 5).Value = "Registered";         sheet.Cell(1, 6).Value = "Live Meeting";           var i = 1;         foreach(var attendee in evt.Attendees)         {             i++;               var profile = attendee.Profile;             sheet.Cell(i, 1).Value = profile.FirstName;             sheet.Cell(i, 2).Value = profile.LastName;             sheet.Cell(i, 3).Value = profile.Email;             sheet.Cell(i, 4).Value = profile.Phone;             sheet.Cell(i, 5).Value = att.Created.ToString();             sheet.Cell(i, 6).Value = att.LiveMeeting.ToString();         }           xls.Save();      }       Response.Clear();     Response.ContentType = "application/vnd.openxmlformats";     Response.AddHeader("Content-Disposition",                        "attachment; filename=" + fileInfo.Name);     Response.WriteFile(fileInfo.FullName);     Response.Flush();       if (fileInfo.Exists)         fileInfo.Delete(); } And here is the result. Although it is possible to make this list more effective and nice it works and users can start using it until all the nice bells and whistles are coming. Conclusion After some fighting with technology it was not very hard to get nice Excel 2007 sheets coming out from our server. We used ExcelPackage library to create list of event attendees and our event organizers can now simply download data to Excel if they need to contact with attendees or manage their data using Excel tools.

  Read the article

• Exception using Querytables in Excel Automation

  - by sam

  Hi, I'm using Automation to populate a range in Excel using a querytable.. However, when I try to add a querytable to the qorksheet I get an exception. I checked the connection string and its working fine.. Can some one please help me with this?? public void writeproc1() { try { Worksheet ws = (Worksheet)wb.Worksheets.Add(Missing.Value, Missing.Value, Missing.Value, Missing.Value); Range rng = ws.get_Range("A1", "E14"); QueryTable qt = ws.QueryTables.Add("Data Source=(local)\\SQLEXPRESS;initial catalog=temp;Integrated Security=SSPI;", rng, "Select * From Table_1"); qt.RefreshStyle = XlCellInsertionMode.xlInsertEntireRows; qt.Refresh(false); } catch (Exception ex) { Console.WriteLine(ex.ToString()); Console.ReadKey(); } } Exception Thrown System.Runtime.InteropServices.COMException (0x800A03EC): Exception from HRESULT: 0x800A03EC at System.RuntimeType.ForwardCallToInvokeMember(String memberName, BindingFlags flags, Object target, Int32[] aWrapperTypes, MessageData& msgData) at Microsoft.Office.Interop.Excel.QueryTables.Add(Object Connection, Range Destination, Object Sql) at tmp.Program.writeproc1() in ...Projects\tmp\tmp\Program.cs:line 25

  Read the article

• Open an excel file using COM and save it as .xml file

  - by chupinette

  Hi. Im trying the following code: <?php $workbook = "D:\b2\\test.XLS"; $sheet = "Sheet1"; #Instantiate the spreadsheet component. $ex = new COM("Excel.sheet") or Die ("Did not connect"); #Get the application name and version print "Application name:{$ex->Application->value}<BR>" ; print "Loaded version: {$ex->Application->version}<BR>"; #Open the workbook that we want to use. $wkb = $ex->application->Workbooks->Open($workbook) or Die ("Did not open"); #Create a copy of the workbook, so the original workbook will be preserved. $ex->Application->ActiveWorkbook->SaveAs("D:\b2\Ourtest.xml"); #$ex->Application->Visible = 1; #Uncomment to make Excel visible. #Optionally, save the modified workbook $ex->Application->ActiveWorkbook->SaveAs("D:\Ourtest.xml"); #Close all workbooks without questioning $ex->application->ActiveWorkbook->Close("False"); unset ($ex); ?> This actually works and creates the Ourtest.xml file. But im getting characters like: ÐÏࡱá þÿ þÿÿÿ I have tried with SaveAs("D:\Ourtest.pdf") and it says the file has been corrupted or incorrectly decoded. Can anyone help me please?Thanks

  Read the article

• import txt files using excel interop in C# (QueryTables.Add)

  - by kite

  Hi all, I am trying to insert text files into excel cell using Querytables.Add; no error, but the worksheet is empty. except for the single cell manipulation using Value2 property. I already using macro to record the object used. Can you help me on this(I am using vs2008, C# , excel 2003 and 2007; both shown empty cell). Below is my code; thanks for your help Application application = new ApplicationClass(); try { object misValue = Missing.Value; wbDoc = application.Workbooks.Open(flnmDoc, misValue, misValue, misValue, misValue, misValue, misValue, misValue, misValue, misValue, misValue, misValue, misValue, misValue, misValue); wsRefDocBudgetOwner = (Worksheet)wbDoc.Worksheets[2]; Range lRange = wsRefDocBudgetOwner.get_Range("B2", "B25"); var temp2 = wsRefDocBudgetOwner.QueryTables; var temp = temp2.Add(@"TEXT;d:\temp\config ssas.txt", lRange, Type.Missing); //temp.RefreshStyle = XlCellInsertionMode.xlInsertDeleteCells; //temp.RefreshOnFileOpen = true; wsRefDocBudgetOwner.get_Range("B1", "B1").Value2 = "Lgfdgast adsffdafadfads"; wbDoc.Save(); //wbDoc.SaveAs(flnmDoc2, misValue, misValue, misValue, misValue, misValue, XlSaveAsAccessMode.xlExclusive, // misValue, misValue, misValue, misValue, misValue); wbDoc.Close(Missing.Value, Missing.Value, Missing.Value); } finally { application.Quit(); }

  Read the article

• Basic Steps in reading Excel files into matlab

  - by user3693727

  >> [NUM,TXT,RAW]=xlsread('C:\Users\Lincoln Wachn\Google Drive\Summer time\Book1') ??? Error using ==> xlsread at 219 XLSREAD unable to open file C:\Users\Lincoln Wachn\Google Drive\Summer time\Book1. File C:\Users\Lincoln Wachn\Google Drive\Summer time\Book1.xls not found. This is the error that I have received when I try to read a simple Excel file into MATLAB. This is a snapshot of the spreadsheet I would like to load in. Could guide me the basic know-how to extract these data? I have looked through the other questions pertaining to reading Excel files into MATLAB, but I am still very confused. I ultimately wish to extract the file below for my project using the same method. The second image shows the data I have to extract which I could not do. Its file type seems to be different, it is comma separated values file which is not xls. Hence, I am also confuse about whether different file type prevents extraction of data. Thanks you for helping(:

  Read the article

• excel:mysql: rs.Update crashes

  - by every_answer_gets_a_point

  i am connecting to mysql from excel and updating a table. as soon as i get to .update (rs.update) excel crashes. am i doing something wrong? Option Explicit Dim oConn As ADODB.Connection Private Sub ConnectDB() Set oConn = New ADODB.Connection oConn.Open "DRIVER={MySQL ODBC 5.1 Driver};" & _ "SERVER=localhost;" & _ "DATABASE=employees;" & _ "USER=root;" & _ "PASSWORD=pas;" & _ "Option=3" End Sub Function esc(txt As String) esc = Trim(Replace(txt, "'", "\'")) End Function Private Sub InsertData() Dim dpath, atime, rtime, lcalib, aname, rname, bstate, instrument As String Dim rs As ADODB.Recordset Set rs = New ADODB.Recordset ConnectDB With wsBooks rs.Open "batchinfo", oConn, adOpenKeyset, adLockOptimistic, adCmdTable Worksheets.Item("Report 1").Select dpath = Trim(Range("B2").Text) atime = Trim(Range("B3").Text) rtime = Trim(Range("B4").Text) lcalib = Trim(Range("B5").Text) aname = Trim(Range("B6").Text) rname = Trim(Range("B7").Text) bstate = Trim(Range("B8").Text) ' instrument = GetInstrFromXML(wbBook.FullName) With rs .AddNew ' create a new record ' add values to each field in the record .Fields("datapath") = dpath .Fields("analysistime") = atime .Fields("reporttime") = rtime .Fields("lastcalib") = lcalib .Fields("analystname") = aname .Fields("reportname") = rname .Fields("batchstate") = bstate ' .Fields("instrument") = "NA" .Update ' stores the new record End With ' get the last id Set rs = oConn.Execute("SELECT @@identity", , adCmdText) 'MsgBox capture_id rs.Close Set rs = Nothing End With End Sub

  Read the article

• Excel VBA Userform Combobox problem

  - by Marc

  I'm having difficulties with a Combobox in a userform in an Excel document. The combobox either doesn't appear in the userform, or the combobox remains blank, and when I enter any character in it, the list of items appears, but 2 or 3 times, instead of just once. When I select an item, the chosen item doesn't appear in the box. It seems as if Excel^picks one at random, and whichever item I choose from the list, it's always the same one that ends up being displayed in the box. Can anyone help me on this one? Thanks a lot!!! This is the code I used: Private Sub ComboBox1_Change() Select Case ComboBox1.Text Case "Een nieuwe start" Case "Alles heeft zijn tijd" Case "De wereld aan je voeten" Case "Een levend boek" Case "Drempels" Case "Kerstmis" Case "Confituur of choco" Case "Hoe groot is de hemel?" Case "Ongelovige Thomas" Case "Feesten" Case "Er is er één jarig!" Case "Eén van hart" Case "Ervoor gaan" Case "Groen gras" Case "RELatie" Case "Vele plaatjes" Case "Iedereen fan" Case "Schattenjacht" Case "Lichtbakens" Case "Rijke Luis" Case "Hemel op aarde" Case "Op bezoek" Case Else End Select End Sub Private Sub UserForm1_Initialize() ComboBox1.Clear ComboBox1.AddItem "Een nieuwe start" ComboBox1.AddItem "Alles heeft zijn tijd" ComboBox1.AddItem "De wereld aan je voeten" ComboBox1.AddItem "Een levend boek" ComboBox1.AddItem "Drempels" ComboBox1.AddItem "Kerstmis" ComboBox1.AddItem "Confituur of choco" ComboBox1.AddItem "Hoe groot is de hemel?" ComboBox1.AddItem "Ongelovige Thomas" ComboBox1.AddItem "Feesten" ComboBox1.AddItem "Er is er één jarig!" ComboBox1.AddItem "Eén van hart" ComboBox1.AddItem "Ervoor gaan" ComboBox1.AddItem "Groen gras" ComboBox1.AddItem "RELatie" ComboBox1.AddItem "Vele plaatjes" ComboBox1.AddItem "Iedereen fan" ComboBox1.AddItem "Schattenjacht" ComboBox1.AddItem "Lichtbakens" ComboBox1.AddItem "Rijke Luis" ComboBox1.AddItem "Hemel op aarde" ComboBox1.AddItem "Op bezoek" ComboBox1.Text = ComboBox1.List(0) End Sub

  Read the article

• Excel Reader ASP.NET

  - by user304429

  I declared a DataGrid in a ASP.NET View and I'd like to generate some C# code to populate said DataGrid with an Excel spreadsheet (.xlsx). Here's the code I have: <asp:DataGrid id="DataGrid1" runat="server"/> <script language="C#" runat="server"> protected void Page_Load(object sender, EventArgs e) { string connString = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=c:\FileName.xlsx;Extended Properties=""Excel 12.0;HDR=YES;"""; // Create the connection object OleDbConnection oledbConn = new OleDbConnection(connString); try { // Open connection oledbConn.Open(); // Create OleDbCommand object and select data from worksheet Sheet1 OleDbCommand cmd = new OleDbCommand("SELECT * FROM [sheetname$]", oledbConn); // Create new OleDbDataAdapter OleDbDataAdapter oleda = new OleDbDataAdapter(); oleda.SelectCommand = cmd; // Create a DataSet which will hold the data extracted from the worksheet. DataSet ds = new DataSet(); // Fill the DataSet from the data extracted from the worksheet. oleda.Fill(ds, "Something"); // Bind the data to the GridView DataGrid1.DataSource = ds.Tables[0].DefaultView; DataGrid1.DataBind(); } catch { } finally { // Close connection oledbConn.Close(); } } </script> When I run the website, nothing really happens. What gives?

  Read the article

• How to implement Excel Solver functionality in C#?

  - by Vic

  Hi, I have an application in C#, I need to do some optimization calculations, like Excel Solver Add-in does, one option is certainly to write my own solver implementation, but I'm kind of short of time, so I'm looking into libraries that already exist that can help me with this. I've been trying the Microsoft Solver Foundation, which seems pretty neat and cool, the problem is that it doesn't seem to work with the kind of calculations that I need to do. At the end of this question I'm adding the information about the calculations I need to perform and optimize. So basically my question is if any of you know of any other library that I can use for this purpose, or any tutorial that can help to do my own solver, or any idea that gives me a lead to solve this issue. Thanks. Additional Info: This is the data I need to calculate: I have 7 variables, lets call them var1, var2,...,var7 The constraints for these variables are: All of them need to be 0 <= varn <= 0.5 (where n is the number of the variable) The sum of all the variables should be equal to 1 The objective is to maximize the target formula, which in Excel looks like this: (MMULT(TRANSPOSE(L26:L32),M14:M20)) / (SQRT(MMULT(MMULT(TRANSPOSE(L26:L32),M4:S10),L26:L32))) The range that you see in this formula, L26:L32, is actually the range with the variables from above, var1, var2,..., varn. M14:M20 and M4:S10 are ranges with data that I get from different sources, there are more likely decimal values. As I said before, I was using Microsoft Solver Foundation, I modeled pretty much everything with it, I created functions that handle the operations of the target formula, but when I tried to solve the model it always fail, I think it is because of the complexity of the operations. In any case, I just wanted to show these data so you can have an idea about the kind of calculations that I need to implement.

  Read the article

• Name Values in Excel Object model

  - by mcoolbeth

  I am using VSTO to create an Excel add-in. My plan is to persist objects inside of excel workbooks by serializing them to strings and assigning those strings to be the values of names in the workbook. However, when I call the API function to add the new name, I get a mysterious exception from the COM library. More precisely, I am calling _app.ActiveWorkbook.Names.Add(name, value, true, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing); where name = "an_object" and value = "TestTemplate|'Sheet1'!$A$1| 1Cube=(0,1):(1,2)| 2EntryNumber=(1,1):(2,2)| 3Description=(2,1):(3,2)| 4Group=(4,1):(5,2)| 5Repost=(3,1):(4,2)| 6Debit=(13,3):(16,4)| 7Credit=(13,2):(16,3)|Company=(6,1):(7,2)|Company Partner=(7,1):(8,2)|Time Monthly=(8,1):(9,2)|Currency=(9,1):(10,2)|Version=(10,1):(11,2)|Department=(13,0):(16,1)|Account=(13,1):(16,2)|" A hypothesis is that the value string does not qualify as a string that can be stored in a name (illegal characters, too long, etc) but I cannot find any documentation about what the restrictions are. Does anyone know what is going wrong here? The error message, in case anyone wants it, is Exception from HRESULT: 0x800A03EC Thanks alot.

  Read the article

• Use Excel VBA to fill out and submit Google Docs form

  - by guitarthrower

  I'm trying to do something like this post but with Excel VBA. I would like to submit a response on a google docs form each time a button is pressed on an Excel add-in. The addin will be an XLA file and written in VBA. I want to be able to collect what features the users are using. If someone has a better solution, I'm open. ---Edit--- This is the form I am trying to write to (excerpt of the code for one of the fields.) <div class="errorbox-good"> <div class="ss-item ss-item-required ss-text"> <div class="ss-form-entry"> <label for="entry_0" class="ss-q-title"> UserName <span class="ss-required-asterisk">*</span> </label> <label for="entry_0" class="ss-q-help"></label> <input type="text" id="entry_0" class="ss-q-short" value="" name="entry.0.single"> </div> </div> </div>

  Read the article

• Excel VBA SQL Import

  - by user307655

  Hi All, I have the following code which imports data from a spreadsheet to SQL directly from Excel VBA. The code works great. However I am wondering if somebody can help me modify the code to: 1) Check if data from column A already exists in the SQL Table 2) If exists, then only update rather than import as a new role 3) if does not exist then import as a new role. Thanks again for your help Sub SQLIM() ' Send data to SQL Server ' This code loads data from an Excel Worksheet to an SQL Server Table ' Data should start in column A and should be in the same order as the server table ' Autonumber fields should NOT be included' ' FOR THIS CODE TO WORK ' In VBE you need to go Tools References and check Microsoft Active X Data Objects 2.x library Dim Cn As ADODB.Connection Dim ServerName As String Dim DatabaseName As String Dim TableName As String Dim UserID As String Dim Password As String Dim rs As ADODB.Recordset Dim RowCounter As Long Dim ColCounter As Integer Dim NoOfFields As Integer Dim StartRow As Long Dim EndRow As Long Dim shtSheetToWork As Worksheet Set shtSheetToWork = ActiveWorkbook.Worksheets("Sheet1") Set rs = New ADODB.Recordset ServerName = "WIN764X\sqlexpress" ' Enter your server name here DatabaseName = "two28it" ' Enter your database name here TableName = "COS" ' Enter your Table name here UserID = "" ' Enter your user ID here ' (Leave ID and Password blank if using windows Authentification") Password = "" ' Enter your password here NoOfFields = 7 ' Enter number of fields to update (eg. columns in your worksheet) StartRow = 2 ' Enter row in sheet to start reading records EndRow = shtSheetToWork.Cells(Rows.Count, 1).End(xlUp).Row ' Enter row of last record in sheet ' CHANGES ' Dim shtSheetToWork As Worksheet ' Set shtSheetToWork = ActiveWorkbook.Worksheets("Sheet1") '** Set Cn = New ADODB.Connection Cn.Open "Driver={SQL Server};Server=" & ServerName & ";Database=" & DatabaseName & _ ";Uid=" & UserID & ";Pwd=" & Password & ";" rs.Open TableName, Cn, adOpenKeyset, adLockOptimistic For RowCounter = StartRow To EndRow rs.AddNew For ColCounter = 1 To NoOfFields rs(ColCounter - 1) = shtSheetToWork.Cells(RowCounter, ColCounter) Next ColCounter Next RowCounter rs.UpdateBatch ' Tidy up rs.Close Set rs = Nothing Cn.Close Set Cn = Nothing End Sub

  Read the article

• Accessing an excel file throws OleDbException but keeps handle on file

  - by Jonn

  Really odd that I'd get an oledbexception but turns out that the file's handle is still with the original file. I've been searching through google and keep finding the same problem but no solutions. Connection String: "Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source=" + filePath + ";" + "Extended Properties=Excel 8.0;"; Note that it works on every other file except a particular excel file. Exception: System.Data.OleDb.OleDbException: No error information available: E_UNEXPECTED(0x8000FFFF). And then I have exception handling like this: try { IEnumerable<string> worksheetNames = GetWorkbookWorksheetNames(connString); DataSet ds; foreach (string worksheetName in worksheetNames) { OleDbDataAdapter dataAdapter = new OleDbDataAdapter("SELECT * FROM [" + worksheetName + "]", connString); ds = new DataSet(); dataAdapter.Fill(ds, "ExcelInfo"); DataTable dt = ds.Tables["ExcelInfo"]; entityList.AddRange(GetDataFromDataTable(dt, worksheetName)); } } catch (OleDbException ex) { File.Move(filePath, filePath + ".invalidFormat.xls"); } Has anyone else encountered this behavior? And I'm not sure how to handle an error that keeps the handle on the file I'm supposed to process. It sort of freezes everything in place.

  Read the article

• SQL Server 2005 Import from Excel

  - by user327045

  I'd like to know what my best option would be to import data from an excel file on a weekly or monthly basis. At first, I thought I would use SSIS, but after much struggle with seemingly simple tasks, I'm starting to rethink my plan. Would it be better/easier to just write the SQL by hand or use the services of an SSIS package? The basic process will be as follows: A separate process will download an .xls file to a local fileshare. The xls file will have a filename like: 'myfilename MON YY'. I will need to read the month and year from the the filename, reformat it to a sql date and then query a DimDate table to find the corresponding date key. For each row (after the first 2 header rows), insert the data with the date key, unless the row is a total row, then ignore. Here are some of the issues I've been encountering with SSIS: I can parse the date string from a flat file datasource, but can't seem to do it with an excel data source. Also, once parsed, i cannot seem to convert the string to a date in order to perform the lookup for the date key. For example, I want to do something like this: select DateKey from DimDate where ActualDate = convert(datetime, '01-' + 'JAN-10', 120) but i don't think it is possible to use the 'convert' or 'datetime' keywords in an expression builder. I have been also unable to find where I can edit the SQL to ignore the first 2 rows of data. I'm very skeptical of using SSIS because it seems like a Kludgy way of doing something that can probably be accomplished more efficiently writing the SQL yourself, but I may be forced to use SSIS. Thoughts?

  Read the article

• Problem registering a COM server written for Excel registered on client machine (can't set full path

  - by toytrains

  In this previous question <How to get COM Server for Excel written in VB.NET installed and registered in Automation Servers list? there is an example of how to create the full path to a registry key using VS 2008. Everything in the previous answer works correctly except the full path that I am setting (using the registry editor in VS) for mscoree.dll is not working (meaning it seems to do nothing). The full registry path is: HKEY_CLASSES_ROOT\CLSID{my_GUID}\InprocServer32(default) and the value I am setting is: [SystemFolder]mscoree.dll I can put anything (including hardcoding the full path) but the setting does not seem to matter and the registry always contains mscoree.dll without any path. I have tried adding another value to the registry path via VS and that works correctly including having the full path as specified by [SystemFolder]. The reason I need the full path (as explained in the previous question) is that without the path, Excel generates an error when the automation server is selected as it cannot find mscoree.dll (interestingly even though I receive an error the registration works OK). I am doing the install via a setup project which otherwise works fine. I am installing on a VISTA*64 system but have gotten the same error on other OS's. Does anyone know what I am doing wrong?

  Read the article

• Interpolating data points in Excel

  - by Niels Basjes

  Hi, I'm sure this is the kind of problem other have solved many times before. A group of people are going to do measurements (Home energy usage to be exact). All of them will do that at different times and in different intervals. So what I'll get from each person is a set of {date, value} pairs where there are dates missing in the set. What I need is a complete set of {date, value} pairs where for each date withing the range a value is known (either measured or calculated). I expect that a simple linear interpolation would suffice for this project. If I assume that it must be done in Excel. What is the best way to interpolate in such a dataset (so I have a value for every day) ? Thanks. NOTE: When these datasets are complete I'll determine the slope (i.e. usage per day) and from that we can start doing home-to-home comparisons. ADDITIONAL INFO After first few suggestions: I do not want to manually figure out where the holes are in my measurement set (too many incomplete measurement sets!!). I'm looking for something (existing) automatic to do that for me. So if my input is {2009-06-01, 10} {2009-06-03, 20} {2009-06-06, 110} Then I expect to automatically get {2009-06-01, 10} {2009-06-02, 15} {2009-06-03, 20} {2009-06-04, 50} {2009-06-05, 80} {2009-06-06, 110} Yes, I can write software that does this. I am just hoping that someone already has a "ready to run" software (Excel) feature for this (rather generic) problem.

  Read the article

• Excel 2007 UDF keeps failing, why?

  - by Zan

  I've been trying to browse info about this for a while now. The user defined functions in Excel 2007 just make no sense to me whatsoever. According to all the tutorials, it should be really simple. Just press alt + f11, insert a new module, and enter the VBA code there. At this point typing '=FUNCTIONNAME(parameters)' into any cell should make it work. It did, at first. Then upon closing and reopening the worksheet it stopped working. I checked to see that macros were enabled, but either way, that didn't help. Then I browsed the graphic UI from the function button to locate user defined functions. I found it there by name of 'MyExcelWorksheet.xls!FUNCTIONNAME.FUNCTIONNAME' and clicking that, it started working again. Now, a day later, I get back to working on this and it's broken, again. I just get #NAME? error or some "Function Arguments - Function doesn't use any arguments" -popup (translated from Finnish Excel, not sure what that msg is in English) when I select the function from the GUI. So what does it take to just create a function, and actually make it work and KEEP working? For the life of me I can't figure out why this has to be so difficult. :) I'm starting to think my company has some weird settings enabled or whatever, because judging by the lack of information I've found on the subject, this isn't supposed to happen.

  Read the article

• How to prevent ADO.NET from altering double values when it reads from Excel files

  - by Khnle

  I have the following rows in my Excel input file: Column1 Column2 0-5 3.040 6 2.957 7 2.876 and the following code which uses ADO.NET to read it: string fileName = "input.xls"; var connectionString = string.Format("Provider=Microsoft.Jet.OLEDB.4.0; data source={0}; Extended Properties=Excel 8.0;", fileName); var dbConnection = new OleDbConnection(connectionString); dbConnection.Open(); try { var dbCommand = new OleDbCommand("SELECT * FROM [Sheet1$]", dbConnection); var dbReader = dbCommand.ExecuteReader (); while (dbReader.Read()) { string col1 = dbReader.GetValue(0).ToString(); string col2 = dbReader.GetValue(1).ToString(); } } finally { dbConnection.Close(); } The results are very disturbing. Here's why: The values of each column in the first time through the loop: col1 is empty (blank) col2 is 3.04016411633586 Second time: col1 is 6 col2 is 2.95722928448829 Third time: col1 is 7 col2 is 2.8763272933077 The first problem happens with col1 in the first iteration. I expect 0-5. The second problem happens at every iteration with col2 where ADO.NET obviously alters the values as it reads them. How to stop this mal-behavior?

  Read the article

• Excel - Best Way to Connect With Access Data

  - by gamerzfuse

  Hello there, Here is the situation we have: a) I have an Access database / application that records a significant amount of data. Significant fields would be hours, # of sales, # of unreturned calls, etc b) I have an Excel document that connects to the Access database and pulls data in to visualize it As it stands now, the Excel file has a Refresh button that loads new data. The data is loaded into a large PivotTable. The main 'visual form' then uses VLOOKUP to get the results from the form, based on the related hours. This operation is slow (~10 seconds) and seems to be redundant and inefficient. Is there a better way to do this? I am willing to go just about any route - just need directions. Thanks in advance! Update: I have confirmed (due to helpful comments/responses) that the problem is with the data loading itself. removing all the VLOOKUPs only took a second or two out of the load time. So, the questions stands as how I can rapidly and reliably get the data without so much time involvement (it loads around 3000 records into the PivotTables).

  Read the article

< Previous Page | 50 51 52 53 54 55 56 57 58 59 60 61  | Next Page >