A very easy way, is to create a vbs script. Paste the below into a notepad and save as XlsToCsv.vbs. Make sure you give the full path of sourcexlsFile and also destinationcsvfile To use: XlsToCsv.vbs [sourcexlsFile].xls [destinationcsvfile].csv if WScript.Arguments.Count < 2 Then WScript.Echo “Error! Please specify the source path and the destination. Usage: XlsToCsv SourcePath.xls … Read more
This should work: Sub test() Dim wbk As Workbook strFirstFile = “C:\source.xls” strSecondFile = “C:\destination.xls” Range(“A1:HC35”).Copy Set wbk = Workbooks.Open(strSecondFile) With wbk.Sheets(“Sheet1”) Range(“A1”).PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _ False, Transpose:=False End With End Sub solved Macro for copying cells from one workbook to another
One way to tell if the entry has been previously split is as follows If the regex.test fails, then If the results line passes, then the item has been previously split if not, then it is a blank, or a malformed entry Note that a lot of this could be avoided if you were not … Read more
Go to the worksheet of interest and: 1) Click the top left to grey triangle to select all cells 2) Right click Format Cells 3) Ensure Locked box is ticked 4) Go to Review tab and select Protect Sheet and enter your password e.g. “password” In the code associated with the check box you would … Read more
Use a helper column which can then be used for the numerical sort. So in the source data add a helper column next to the days column that goes 1,2,3,4 etc ranking days – you can use a vlookup to pull in the right rank for each days group. Then use this to sort on … Read more
To calculate Years of Increases enter the following formula in Cell L2 =IFERROR(COLUMN(J2)-COLUMN(INDEX(B2:J2,,MATCH(9.99E+307,IF(B2:J2>A2:I2=FALSE,1,””)))),0) and to calculate Years of Steady Profits enter below formula in Cell M2 =IFERROR(COLUMN(J2)-COLUMN(INDEX(B2:J2,,MATCH(9.99E+307,IF(B2:J2>=A2:I2=FALSE,1,””)))),0) Both the above formulas are array formula so commit by pressing Ctrl+Shift+Enter. Drag/Copy down as required. See image for reference. In case you want this formula to be … Read more
Try conditionally formatting A2:A12 with the following formula: =IF(ISERROR(SEARCH(“*”&$A2&”*”, $A$1)>0), FALSE, TRUE) (see screenshots below). 1 solved Highlight duplicate values in excel
I’ve got an answer that will find the first instance of USD and check if TOTAL PURCHASE is 9 rows below it, if found then copy the adjacent cell to D1, if not found then search for the next USD and repeat the checks: Option Explicit Sub vbavjezba() Dim total As Variant Dim usd As … Read more
You can try this, just set ws to whatever worksheet you have your dates in Sub datechecker() Dim inputBoxText As String Dim inputBoxDate As Date Dim lastRow As Integer Dim row As Integer Dim ws As Worksheet Set ws = ThisWorkbook.Worksheets(1) With ws lastRow = .Cells(.Rows.Count, “K”).End(xlUp).row inputBoxText = InputBox(“Please enter a Date:”, “Date Input … Read more
Try this code: OleDbConnection oledbConn = new OleDbConnection(); try { string path = HttpContext.Current.Server.MapPath(“~/virtual path for your file”); if (Path.GetExtension(path) == “.xls”) { oledbConn = new OleDbConnection(“Provider=Microsoft.Jet.OLEDB.4.0;Data Source=” + path + “;Extended Properties=\”Excel 8.0;HDR=Yes;IMEX=2\””); } else if (Path.GetExtension(path) == “.xlsx”) { oledbConn = new OleDbConnection(@”Provider=Microsoft.ACE.OLEDB.12.0;Data Source=” + path + “;Extended Properties=”Excel 12.0;HDR=YES;IMEX=1;”;”); } oledbConn.Open(); OleDbCommand … Read more
I do this on a daily basis saving the file as the previous days date. I modified what I use for what you are wanting: dim name as string name = Cells(1,2).Value & Cells(2,2).Value ActiveWorkbook.SaveAs() Filename:=name 1 solved Using VBA to save a file in excel 2010 with a different name each time [closed]
The issue is that once a row has been deleted, the macro will continue onto the next row (skipping a row). Working backwards through the loop will prevent any rows being missed so something like: Sub DeleteRowsPiuDi40Mega() Dim LastRow As Long Dim ws4 As Worksheet Set ws4 = ActiveWorkbook.Sheets(“atm_hh”) LastRow = ActiveSheet.Range(“C” & ActiveSheet.Rows.Count).End(xlUp).Row For … Read more
Here, this will do what you want. Option Explicit Sub FileListingAllFolder() Dim pPath As String Dim FlNm As Variant Dim ListFNm As New Collection ‘ create a collection of filenames Dim OWb As Workbook Dim ShtCnt As Integer Dim Sht As Integer Dim MWb As Workbook Dim MWs As Worksheet Dim i As Integer ‘ … Read more
Try this code. I’ve commented it in details. But if you have some quesions, ask in comments:). Code saves new wokrbooks in the folder where your current workbook is saved. Sub test() Dim names As New Collection Dim ws As Worksheet, ws1 As Worksheet Dim wb As Workbook Dim lastrow As Long Dim cell As … Read more
To use Excel formula in VBA you have two choices: you can either Evaluate the function string as in @YowE3K’s example given here, or alternatively you can use the WorksheetFunction method to perform most Excel functions. The Evaluate method is best if you want to do a fixed formula like your example suggests and return … Read more