w3hello.com logo
Home PHP C# C++ Android Java Javascript Python IOS SQL HTML videos Categories
Database Query: Importing data from an excel spreadsheet. Without it opening that spreadsheet.
Although I think that this workbook being open could lead to other problems, to get a handle back to your workbook try... If Err.Number = (The error number you get) then Set xlWorkbook = GetObject("C:TestMyWb.xlsx", "Excel.Application") end if

Categories : Database

How do I in C# modify an Excel spreadsheet, save it as another name and delete the original spreadsheet
Where you have this code: ExcelWorkBook.SaveAs(FileName, Excel.XlFileFormat.xlWorkbookNormal, "", "", false, false, Excel.XlSaveAsAccessMode.xlNoChange, Excel.XlSaveConflictResolution.xlUserResolution, true, "", "", ""); Try insert this instead: string oldFileName = ExcelWorkBook.FullName; ExcelWorkBook.SaveAs(FileName, Excel.XlFileFormat.xlWorkbookNormal, "", "", false, false, Excel.XlSaveAsAccessMode.xlNoChange, Excel.XlSaveConflictResolution.xlUserResolution, true, "", "", ""); File.Delete(oldFileName); You keep a copy of the previous filename and delete it. WARNING: Since you did not provide a standalone example, I could not quite test this. Also you may want to edit this to delete only if the save succeeded and if the filename

Categories : C#

How can I use VBA to add a Connection (to an External Data Source) in Excel and Save it to that Excel spreadsheet's list of Connections
You can use a macro recorder to generate a VBA code that will add a connection to your excel instance. I have added the code at the end of this answer, however you can generate your own if you follow the below steps: 1) Start a macro recorder 2) On the ribbon, click on Data tab. Click on the Connections and then choose the Add button like shown in the below screenshot 3) On the next screen, choose your existing DB connection, and follow the steps on the next 2 or 3 screens to configure your connection. 4) Once your connection is established and appears in the connections list, click on the Properties button and on the next screen Export Connection File 5) Stop your macro recorder and open VBE (alt+F11) and edit the code in your Module1 6) Remove these lines from your macro code .Serv

Categories : Excel

Importing data from access into an open excel spreadsheet? (Excel VBA)
The first version won't work because you are attempting to write to the Excel file that you currently have open. Changing to the following line (of the 2nd code) will copy the data to another worksheet: Set TargetRange = Sheets("WhateverName").Range("A1") 'or Set TargetRange = Sheets(2).Range("A1") '..if you know it is the 2nd sheet that 'you want to copy to. Then, Worksheets(2).Name = "1301 Array" You could, alternatively, create a new sheet: Dim wsData As Worksheet Set wsData = Worksheets.Add wsData.Name = "1301 Array" Set TargetRange = wsData.Range("A1")

Categories : SQL

How do I track who uses my Excel spreadsheet?
I think password protection in the method you describe is unnecessarily cumbersome if it is even doable at all. He asked that I password-protect the Excel spreadsheet in such a way that one password does NOT unlock all of the copies that people can download from the site. I can't imagine how this might be possible using only Excel. Maybe an Add-in could do this, but at the file level, I don't think it could be done, at least not easily. I will never be able to ascertain who is using the spreadsheet. It sounds like this is the really important bit. You are not using the password as a security measure, only as a gatekeeping method to determine who is using the file. This can be automated in other ways, easiest of which would be to use certain Environment variables, e.g.: Msg

Categories : Excel

C# not reading excel date from spreadsheet
From http://stackoverflow.com/a/4538367/1397117 You need to convert the date format from OLE Automation to the .net format by using DateTime.FromOADate. double d = double.Parse(b); DateTime conv = DateTime.FromOADate(d); And I echo suggestions below that answer to use .Value instead of .Value2.

Categories : C#

Using data from an Excel spreadsheet in QuickTest Pro
Yes, it's possible. Assuming I understood your question correctly you could do something like this: Set xl = CreateObject("Excel.Application") xl.Visible = True 'set to False for production Set wb = xl.Workbooks.Open("C:path oworkbook.xls") Set ws = wb.Sheets(1) firstRow = ws.UsedRange.Rows(1).Row lastRow = firstRow - 1 + ws.UsedRange.Rows.Count For i = firstRow+1 To lastRow val = ws.Cells(i, 1).Value 'get value from cell 'do stuff with val Next wb.Close xl.Quit

Categories : Excel

When using Access from within an Excel spreadsheet, the PC won't go to sleep
After your Close lines you could Set rs = Nothing Set con = Nothing although I cannot guarantee that this is what is causing your computer to not hibernate - more information would be required.

Categories : Excel

Writing a macro for a spreadsheet in excel
You've defined rngSeek as a range and then are trying to use that range definition with the Range() method of the worksheet. All the lines where you have Range(rngSeek).Offset(... you can replace with rngSeek.Offset(... (One of your formula also references the wrong cells ;-)

Categories : Excel

Excel - testing two conditions in a row throughout spreadsheet
Make this into a pivot table and you can do it quite easily Insert>Pivot Table> select your range> click OK Make the column/row/value labels look like in my picture

Categories : Excel

Freezing row in the middle of Excel spreadsheet
Admittedly based on Excel 2007, you may have several options: Don't freeze it but split it. Move Row20 up to Row1. Use a New Window (example here).

Categories : Excel

How to create Excel like spreadsheet with MVC3
You'd rather look to JS/JQuery spreadsheet and grid plugin rather than being stuck to MVC3. Have a look at this SO question where many plugins have been mentionned. You should find one that fits to your needs.

Categories : Asp Net Mvc

Why can't I see a powerpivot spreadsheet in regular excel
If you have MS-Office 2003 or earlier goto the blow link and download and install the MS-Compatibility pack. http://www.microsoft.com/downloads/details.aspx?FamilyId=941B3470-3AE9-4AEE-8F43-C6BB74CD1466&displaylang=en

Categories : Sql Server

VBA not setting cell text in excel spreadsheet
Since we do not know what comes before that line of code, you may want to debug.print the address of that cell and the status to support it did execute but no values set. Debug.Print Now() & vbTab & Sheets(checkout_sheet_name).Cells(Row_Index, checkout_tool_observation_column).Address & " -> " & status

Categories : Excel

autofit excel column width using spreadsheet gem
Not that I know of. I've had to do it manually by keeping track of the lengths of the strings in each column and then taking the max length and adding a couple more units to it, then setting the width of the column to that calculated value. account_name_lengths = [] # generate each row accounts.each do |account| account_name_lengths << account.name.length # add to sheet here end sheet.column(0).width = account_name_lengths.max + 5

Categories : Ruby On Rails

"Overload" F9 (or shift+F9) in Excel spreadsheet with VBA function?
According to MSDN Application.Volatile : Marks a user-defined function as volatile. A volatile function must be recalculated whenever calculation occurs in any cells on the worksheet. A nonvolatile function is recalculated only when the input variables change. This method has no effect if it's not inside a user-defined function used to calculate a worksheet cell. This (and other) mean that: instruction works only with functions, not Module it will work with all User Defined functions also as a result of pressing F9 it's required to place the instruction at the beginning of UDF. See example of UDF with Application.Volatile: Function ReturnCurrentTimer() Application.Volatile ReturnCurrentTimer = Timer End Function

Categories : Excel

Insert row into Excel spreadsheet using openpyxl in Python
Unfortunately there isn't really a better way to do in that read in the file, and use a library like xlwt to write out a new excel file (with your new row inserted at the top). Excel doesn't work like a database that you can read and and append to. You unfortunately just have to read in the information and manipulate in memory and write out to what is essentially a new file.

Categories : Python

Excel spreadsheet delete all the shaded values
It sounds as though simply adding a filter to the Row Header would fix this issue. Simply add the filter, select the "Filter By Color" option, filter by the color in question, and then highlight and delete cells, or manually change their color to "No Shade/Fill".

Categories : Excel

Delete files from directory that are not in Excel spreadsheet
I would use Dir() to list all the files in the directory into a second column of Excel use For Each to examine each cell in this new column of file names within the For..Each use Excel's MATCH function to check if the file exists in the existing column use Kill to delete the files Delete these two columns when complete. I might store a value of "DELETE" in a third column and do the deletions separately afterwards. (Or, more usefully, just copy the name of the file to delete to this third column.) I am reluctant to provide code though until you have made an attempt yourself. Added Actually, I would probably use COUNTIF() in the third column and use this as the basis to perform file deletions; but there are always a number of approaches to take ;)

Categories : Excel

Open spreadsheet xml, save as Excel by default
An Excel XLS file is not the same format as an Open Spreadsheet XML file, which is why Excel moans at you when it tries to open an XLS and find it is not one. Excel can, of course, open Open Spreadsheet XML files, which it why it still opens it in Excel. But it is an XML file, and so when you save it again, it will still be an XML file. The only way to get around this issue, I think, is to do Save As, and change the format manually to .XLS. For the first issue though, there may be something you can do. Rename the file back as Export.xml, but make sure the following processing instruction appears at the top of your XML file <?mso-application progid="Excel.Sheet"?> This should hopefully make the XML file be opened in Excel, as opposed Internet Explorer. In terms of XSLT though, to

Categories : Xml

Exporting data from Access to an Excel Spreadsheet
Why not just create a new file each month that is essentially a full refresh, exporting the data from all dates? Seems easier than exporting just the last month to a new worksheet, then cut/pasting into the main sheet.

Categories : Excel

Using Excel::XLSX to parse a spreadsheet in Perl
$cell->{Val} is the unformatted value (which is the same as $cell->unformatted()). Try getting $cell->value() (same as $cell-{_Value}), which is the formatted value; Excel stores dates as floats, so if you want to format those, you can use the ExcelFmt() function from Spreadsheet::ParseExcel::Utility, and you will want to use the unformatted value as one of the arguments to that function. You might even be able to get the format argument with $cell->get_format()

Categories : Perl

Importing Excel spreadsheet into sql as a table error
It can be a number of things that went wrong, have your tried your import on a single row that has validated data ? You could be trying to insert a string into a integer column or a Null-value into a column that does not allow Nulls...

Categories : SQL

Searching for a Variable Defined in Excel Spreadsheet Formulas
Open the Find Dialogue with ctrl+f For the Find what, type in: my_variable Click Options to expand the options section For Within, select Workbook For Look In, select Formulas Click Find All

Categories : Excel

Set width and background for cell in excel using spreadsheet rails
You can set background color through: title_format = Spreadsheet::Format.new({ :weight => :bold, :pattern_bg_color => :grey, :size => 8 }) sheet.row(0).set_format(column_index, title_format) The width of the cell you can set like this: sheet1.column(column_index).width = 1 # but i don't really know in what units it is measured

Categories : Ruby On Rails

Formatting columns in exporting SAS table to Excel spreadsheet
The proper way would be to modify how the template processes numbers. You can do that pretty easily in this case. You could even just comment out a line and one block of code, but here's the really proper answer. Open the template in a text editor. We're going to add a couple of parameters, and implement them. First, add the options to the $valid_options array. There are a bunch of lines like these, add these two more (Around line 635 or so): set $valid_options["TEXTPERCENT"] "This value forces percentages to be displayed as text"; set $valid_options["TEXTCURRENCY"] "This value forces currency amounts to be displayed as text"; That text can be whatever you want, this is one interpretation. Now, around line 700 there are some lines setting the defaults, add these two: set $option

Categories : Excel

Generate Excel Spreadsheet with Macros using Microsoft Access
Under the assumption that the macro's in all generated workbooks are the same, create a template containing all VBA code (and optionally constant text like headers, footers, print range definitions, etc. - i.e. "everything except data") create any new workbook from the template insert your data into the WB object save as macro enabled worksheet (Excel 2007/2010) close it example Sub CreateWB() Dim WB As Workbook Set WB = Workbooks.Add("MacroTemp.xltm") ' contains VBA, ActiveX, etc. WB.Worksheets("Sheet1").[A1] = "co-cooo!" ' adding data WB.SaveAs "MyGenWB", xlOpenXMLWorkbookMacroEnabled WB.Close End Sub In Excel 2007/2010 do not forget to save the template as macro enabled template (*.xltm").

Categories : Excel

Fast Export of Large Datatable to Excel Spreadsheet in VB.Net
As when using VBA to automate Excel, you can assign an array directly to the value of a Range object: this is done as a single operation, so you remove the overhead associated with making multiple calls across the process boundaries between your .Net code and the Excel instance. Eg, see the accepted answer here: Write Array to Excel Range

Categories : Vb.Net

How can I enable and disable expensive calculations in an Excel spreadsheet?
You can set the calculation options for Excel both via the GUI and via VBA: Application.Calculation = xlManual Here is some more info... http://www.decisionmodels.com/calcsecretse.htm

Categories : Excel

VBA Access -> Automated Import an Excel Spreadsheet w/ HYPERLINKS
If you have direct access to the Excel files you could add a new column to append hash-signs either side of the hyperlink content: ="#"&A1&"#" Copy this formula down the column, copy and paste-values to remove the formulas. Then re-import to Access. If you don't have direct access to the files then you could import them into a temporary (empty) table, inserting the hyperlink column into a text-field. Then you could run an Append Query that also modifies this column so that it is suitable to be appended to the hyperlink field. If, when importing to the temporary table, the column comes across empty then I'm afraid it would require Excel Automation to open the file(s) and insert the hash-signs.

Categories : Excel

How to export Excel spreadsheet to HTML table with INLINE CSS?
It is not "illegal" to place the style codes on the body, but defenitly not web friendly What usualy goes in the head is a pointer to a .css file I recomend you convert your xls(x) file with dreamweaver and then edit the html result with Sublime Text Editor Available here

Categories : HTML

Efficient Algorithm for reading data from Excel Spreadsheet in Java
The first thing I would suggest is to take the declarations outside of (before) the loops: String party_name = sheet.getCell(2, i).getContents().toString(); // etc. and labelName, dataField, party. Declare before the loops: String party_name = ""; // etc. You haven't said what library you are using for Excel. Some libraries have, for example getUsedRange to narrow down the cells you are searching, and (perhaps) methods to fill an array from a Range.

Categories : Java

Merge two unique excel spreadsheets, remove one spreadsheet when needed
It depends on what the spreadsheets look like and what, exactly, you mean by merge. If, for example, the two worksheets contain a table each, then you could copy/append one table to the bottom of the other and use Excel's Remove Duplicates feature (on the Data tab) to delete rows. The duplicates can be identified either by a single code-number column, all of the columns (meaning that the entire row is duplicated) or a selection of columns. Be aware that it is the first duplicated row that is kept, the subsequent duplicates will be removed. If, on the other hand, you want to find values in the rows of one of the worksheets, based on a code number contained in a column of the other worksheet, and insert them into specific cells, then this requires more effort, perhaps with the help of the

Categories : Excel

Encoding: exporting special characters from an excel spreadsheet into a php array
I have tested your code: header is not important, make sure you have in your php.ini default_charset = "UTF-8" Moreover, you can forget about the Content-Type header leaving this task to the webserver. Nginx, for example, has the charset UTF-8; directive.

Categories : PHP

How can i use vba to push excel spreadsheet range content to a specific form within a URL?
Assuming the IWebBrowser2 works like IE, this should do the trick. I have tested this using IE since I am more familiar with that, but I think it should be the same. Note: the first row in your range B3:I7 needs to contain the headers (Name, Phone Number, etc.) otherwise you will be prompted to validate the addresses, it will be much easier to just make sure your worksheet is formatted properly to send to the form. Option Explicit Sub LogMeIn() 'Requires reference to Microsoft HTML Object Library Dim ie As InternetExplorer Dim htmlDoc As HTMLDocument Dim el As HTMLFormElement Dim pasteValues As String Dim vals As Range Dim r As Long Dim c As Long '## Store the values in a delimited string Set vals = Range("B3:I7") For r = 1 To vals.Rows.Count For c = 1 To vals.Columns.Count

Categories : Excel

Import VBA code from a excel spreadsheet into a access form behind a button
Metaprogramming in VBA can be an interesting topic. You'd want to look up the VBIDE library. I'd probably start with the CodeModule object and Chip Pearson's "Programming The VBA Editor". The example below inserts a module scoped private constant containing the module's name into the active code pane. Private Sub insertModuleName() ' inserts a module name constant into every module in active project Dim oPrj As VBProject Set oPrj = VBE.ActiveVBProject Dim oMod As CodeModule Dim oCom As VBComponent Dim str As String str = "Private Const MODULE_NAME As String = " Dim i As Long For i = 1 To oPrj.VBComponents.count oPrj.VBComponents.Item(i).CodeModule.InsertLines 3, str & Chr(34) & oPrj.VBComponents.Item(i).NAME & Chr(34) Next i End S

Categories : Vba

SQL query Excel spreadsheet and combine multiple rows of data into one
Change the GROUP BY clause, as this: Select [Name],[City],[State],[Country], IIF([Favorites] = "Hot Dogs", 'Yes', 'No') as [Hot Dogs], IIF([Favorites] = "Baseball", 'Yes', 'No') as [Baseball], IIF([Favorites] = "Apple Pie", 'Yes', 'No') as [Apple Pie] From [Sheet1$] Group By [Name],[City],[State],[Country] Plus, I think you need to add , 'No' in your IIF statements, as I did

Categories : SQL

Spreadsheet: Issue with merge()
Got a fail in my code: I firstly merge the A1:D1, then the C1:E1 collumn. So, the script can't merge C1:E1 since the C1 cell is already merge with the A1. The only complain i can make is that the error msg can be more specific: no cell or line are show; just a generic msg "cell can't be merge"

Categories : Google Apps Script

read inserted data from excel spreadsheet while program execution POI, JAVA
Try the following: public GUI () throws Exception{ this.setTitle("NYWM Cross Reference Application"); this.setSize(400,100); this.setVisible(true); this.setLocationRelativeTo(null); buttonHolder = new Panel (new BorderLayout()); this.add(buttonHolder); crossReference = new Button ("CrossReference"); generateHPD = new Button ("Generate HPD"); buttonHolder.add(crossReference, BorderLayout.NORTH); buttonHolder.add(generateHPD, BorderLayout.SOUTH); // NOTE the order below has been flipped!!!! createExcelSheet (log,"C:/Log.xlsx", "Log"); createExcelSheet(sheet,"C:/Spreadsheet.xlsx", "Spreadsheet"); crossReference.addActionListener(new crossReferenceButtonListener()); generateHPD.addActionListener(new generateHPDButtonListener()); } In your original code, yo

Categories : Java

Find merged cell in Excel, split the cells and write those in new spreadsheet?
According to the Javadocs for HSSFSheet, getMergedCellRegionAt() was deprecated in 2008 because the Region it returns was also deprecated, in favor of CellRangeAddress. It suggests that you should use getMergedRegion(int) instead, which returns a CellRangeAddress. The merged region data is not stored directly with the cells themselves, but with the Sheet object. So you do not need to loop through rows and cells looking for whether they are part of a merged region; you just need to loop through the list of merged regions on the sheet, then add the merged region to your new sheet with addMergedRegion(CellRangeAddress). for (int i = 0; i < sheet.getNumMergedRegions(); i++) { CellRangeAddress mergedRegion = sheet.getMergedRegion(i); // Just add it to the sheet on the new workboo

Categories : Java



© Copyright 2017 w3hello.com Publishing Limited. All rights reserved.