Newbe Dev Stack

Answer : As specified by the top answer, I used the following in the code behind the button control. Private Sub btnClose_Click() Unload Me End Sub In doing so, it will not attempt to unload a control, but rather will unload the user form where the button control resides. The "Me" keyword refers to the user form object even when called from a control on the user form. If you are getting errors with this technique, there are a couple of possible reasons. You could be entering the code in the wrong place (such as a separate module) You might be using an older version of Office. I'm using Office 2013. I've noticed that VBA changes over time. From my experience, the use of the the DoCmd.... method is more specific to the macro features in MS Access, but not commonly used in Excel VBA. Under normal (out of the box) conditions, the code above should work just fine. Without seeing your full code, this is impossible to answer with any certainty. The error...

Answer : Note your code contains Dir("thesentence") which should be Dir(thesentence) . Change your code to this Sub test() thesentence = InputBox("Type the filename with full extension", "Raw Data File") Range("A1").Value = thesentence If Dir(thesentence) <> "" Then MsgBox "File exists." Else MsgBox "File doesn't exist." End If End Sub Use the Office FileDialog object to have the user pick a file from the filesystem. Add a reference in your VB project or in the VBA editor to Microsoft Office Library and look in the help. This is much better than having people enter full paths. Here is an example using msoFileDialogFilePicker to allow the user to choose multiple files. You could also use msoFileDialogOpen . 'Note: this is Excel VBA code Public Sub LogReader() Dim Pos As Long Dim Dialog As Office.FileDialog Set Dialog = Application.FileDialog(msoFileDialogFilePicker...

Answer : A helper function for appending DataFrame to existing Excel file: def append_df_to_excel(filename, df, sheet_name='Sheet1', startrow=None, truncate_sheet=False, **to_excel_kwargs): """ Append a DataFrame [df] to existing Excel file [filename] into [sheet_name] Sheet. If [filename] doesn't exist, then this function will create it. Parameters: filename : File path or existing ExcelWriter (Example: '/path/to/file.xlsx') df : dataframe to save to workbook sheet_name : Name of sheet which will contain DataFrame. (default: 'Sheet1') startrow : upper left cell row to dump data frame. Per default (startrow=None) calculate the last row in the existing DF and write to the next row... truncate_sheet : truncate (remove and recreate) [sheet_name] before ...

Answer : Just to make an answer out of my comment. The rows couldn't size properly because Java was unaware of the font you were trying to use this link should help if you want to install new fonts into Java so you could use something fancier. It also has the list of default fonts that Java knows. Glad this helped and you got your issue solved! This is probably related to this POI Bug which is related to Java Bug JDK-8013716: Renderer for Calibri and Cambria Fonts fails since update 45. In this case changing the Font or using JRE above 6u45 / 7u21 should fix the issue. You can also mtigitate the issue and avoid the columns from being totally collapsed by using a code like this: sheet.autoSizeColumn(x); if (sheet.getColumnWidth(x) == 0) { // autosize failed use MIN_WIDTH sheet.setColumnWidth(x, MIN_WIDTH); } I was also running into this issue and this was my solution. Steps: Create workbook Create spreadsheet Create row Create/Set font t...

Answer : See the documentation for the SXSSFWorkbook constructor that takes the XSSFWorkbook as param. You cannot override or access the initial rows in the template file. You are trying to overwrite an existing row and the API does not support this. Your exception message reflects this. https://poi.apache.org/apidocs/org/apache/poi/xssf/streaming/SXSSFWorkbook.html#SXSSFWorkbook(org.apache.poi.xssf.usermodel.XSSFWorkbook) For your use case, you may want to try http://jxls.sourceforge.net. If you want to read or edit an exist row, you can firstly do it in xssf type, and then create the sxssf file base on the xssf file. The code is something like below... XSSFWorkbook xssfWorkbook = new XSSFWorkbook(new FileInputStream(file)); //do the read and edit operation with xssf...... ...... ...... SXSSFWorkbook sXSSFbook = new SXSSFWorkbook(xssfWorkbook); //do the write operation with sxssf...... ...... ......

Answer : Before Excel 2016 , Excel had the possibilities to have multiple Excel files in a single window. In Excel 2016 , it is one window per application. The problem with your code is that it closes an instance. Based on the fact how the Excel files were opened, this would be either enough or not. E.g., if Excel files were opened in the same instance this would be quite enough. A bit of an amateur myself and I realize this is a bit of an old thread, but I am wondering if you save the workbook (as you do) but also close the workbook and quit Excel, it may clear up the task manager. I notice you save the workbook but don't actually close the workbook, so it stays open. I ran into a similar issue before and I think this finally what fixed it. This is code I use every time I want to quit Excel. Usually I have 2 books open, one is a template (which I don't save) and the other is one that was created with data from the template. ActiveWorkbook.Close SaveChanges:=True Ap...

Answer : Sounds like a job for VLOOKUP! You can put your 32 -> 1420 type mappings in a couple of columns somewhere, then use the VLOOKUP function to perform the lookup. Without reference to the original problem (which I suspect is long since solved), I very recently discovered a neat trick that makes the Choose function work exactly like a select case statement without any need to modify data. There's only one catch: only one of your choose conditions can be true at any one time. The syntax is as follows: CHOOSE( (1 * (CONDITION_1)) + (2 * (CONDITION_2)) + ... + (N * (CONDITION_N)), RESULT_1, RESULT_2, ... , RESULT_N ) On the assumption that only one of the conditions 1 to N will be true, everything else is 0, meaning the numeric value will correspond to the appropriate result. If you are not 100% certain that all conditions are mutually exclusive, you might prefer something like: CHOOSE( (1 * TEST1) + (2 * TEST2) + (4 * TEST3) + (8 * TEST4) ... (2^...

Answer : The find method of a range is faster than using a for loop to loop through all the cells manually. here is an example of using the find method in vba Sub Find_First() Dim FindString As String Dim Rng As Range FindString = InputBox("Enter a Search value") If Trim(FindString) <> "" Then With Sheets("Sheet1").Range("A:A") 'searches all of column A Set Rng = .Find(What:=FindString, _ After:=.Cells(.Cells.Count), _ LookIn:=xlValues, _ LookAt:=xlWhole, _ SearchOrder:=xlByRows, _ SearchDirection:=xlNext, _ MatchCase:=False) If Not Rng Is Nothing Then Application.Goto Rng, True 'value found Else MsgBox "Nothing found" 'value not found End If End With End If End Sub Simplest is to use Match If Not IsError...

Answer : I think your problem is that it's closing the document that calls the macro before sending the command to quit the application. Your solution in that case is to not send a command to close the workbook. Instead, you could set the "Saved" state of the workbook to true, which would circumvent any messages about closing an unsaved book. Note: this does not save the workbook; it just makes it look like it's saved. ThisWorkbook.Saved = True and then, right after Application.Quit To avoid the Save prompt message, you have to insert those lines Application.DisplayAlerts = False ThisWorkbook.Save Application.DisplayAlerts = True After saving your work, you need to use this line to quit the Excel application Application.Quit Don't just simply put those line in Private Sub Workbook_Open() unless you got do a correct condition checking, else you may spoil your excel file. For safety purpose, please create a module to run it. The following are t...

Answer : This doesn't seem to work in Mac Excel 2016. After a bit of digging, it looks like the key combination for entering the array formula has changed from ⌘ + RETURN to CTRL + SHIFT + RETURN . Select the range, press CONTROL + U and then press ⌘ + RETURN .