Cannot open Excel.Application workbook

This is from a macro in Word that accesses an Excel workbook: Dim objExcel As New Excel.Application Dim exWb As Excel.Workbook ExcelFilename = CurDir() & “\” & ThisDocument.CustomDocumentProperties(“Source”) ‘This tests if ExcelFilename is a valid file: If Dir(ExcelFilename) = “” Then Stop Set exWb = objExcel.Workbooks.Open(ExcelFilename) It worked until today. Now, the last line gives […]

How to copy the Data form the Specified address and copy it to the next specified location using excel vba

I have a worksheet which contains the details of the each product. Here i have crested a button (ADD), by clicking on it i want to copy all the details of the CONTROL POWER TRANSFORMERS block and copy it to below (i mean copy it from B20). I have written a code to pinpoint the […]

Transpose Excel spreadsheet to Qlikview readable format

I am new to VBA coding and I am trying to convert the following Excel file: Table1 into a format that is readable by Qlikview. Hence, I want table1 to become table2: Table2 I have color coded several examples above. For example, for everything in red, I want ‘Canada Region’ and ‘NonPar Non-adjustable’ copied for […]

How to prefix cell with 0, dependant on character length

Private Sub Worksheet_Change(ByVal Target As Range) If Len(Target) = 8 Then Exit Sub End If If Range(“AF1:AF1000”).Value = “Q” Then Exit Sub End If On Error Resume Next Application.EnableEvents = False If Target.Column = 30 Then lTarget = Len(Target) Target.NumberFormat = “@” For i = 1 To 8 – lTarget Target.Value = “0” & Target.Value […]

Button to Save Excel range of cells (not manually selected, but automatically detected) as “.csv” with same name as original “.xls” file

Here you have some information regarding my Excel file so you know better my worksheet layout: it is a normal excel sheet (.xlsx) which starts at row 5 and has many different entries for a table. Some of this entries are automatically calculated from a function which uses values from cells from the same row. […]

ActiveWorkbook.SaveAs excel 2013 1004 error

I am getting a Run-time error ‘1004’ Method ‘SaveAs’ of object ‘_Workbook’ failed. The code works in excel 2010. I only get this error message in excel 2013. The error message appears after trying to run the follow line. ActiveWorkbook.SaveAs FolderPath & SaveName & NewSaveExt, 52 Background: The spreadsheet is an .xls When using the […]

Get the reverse formula for another column based on existing column's formula in excel

I have a list of base price but don’t have the list price. Below are the formulas under column W and column AN. Formulas: W: =ROUND(T3+(ROUND(T3*V3,2)),2) AN: =ROUND(W3-(ROUND( (ROUND(W3*Y3,2)) + (ROUND(W3*AD3,2)),2)),2) In order to get the list price, I need to use trial and error method to find out the list price for each items […]

How can I delete rows from excel 2010 Programatically

I have an .xls file. I’m going through the file line by line and deleting lines I don’t need. For some reason after I find the line I want to delete, I can’t delete it. This should be simple but I haven’t been able to get it to work for me. I’ve tried Range(“1:2”).Cut (just […]

Running subsetting function over multiple excel sheets and multiple files

I have inherited a bunch of excel files, each containing multiple sheets. Each sheet looks something like this: str(mydata) ‘data.frame’: 29 obs. of 23 variables: $ Gyffin : Factor w/ 11 levels “% of total activity added”,..: 9 NA 11 4 7 5 6 8 NA NA … $ X2.5.hours: Factor w/ 19 levels “-0.413614051315461”,..: […]

How do I compare two columns, so that if a cell in one column is in the other, a value populates a different column

What I have Several workbooks with three columns of a variety of text, numbers and blank cells. I’m stripping these out and putting them into one column in a new workbook, converting them to a 7 digit number stored as text so it populates with 0s in front, and removing the blanks and text values. […]

MS Excel Spreadsheet is the best Office Software, Excel VBA and Excel Formulas make Spreadsheet work faster.