Microsoft Excel

137K

Solutions

38K

Contributors

Microsoft Excel topics include formulas, formatting, VBA macros and user-defined functions, and everything else related to the spreadsheet user interface, including error messages.

Share tech news, updates, or what's on your mind.

Sign up to Post

I'm trying Freeze Panes in an Excel file I'm creating from a vb script but am having some difficulty with getting the object references correct.

For reference (this is not the exact code; I'd have to sanitize it first to post, but I'm hopefully providing enough for your reference for now):
Worksheet = 2
IndexNumberCol = 2
Set ExcelObject = CreateObject("Excel.Application")
Set WorkbookObject = ExcelObject.Workbooks.Add()
Set WorksheetObject = ExcelObject.ActiveWorkbook.Worksheets(Worksheet)   'There are 3 worksheets created

'This all works below
      WorksheetObject.Cells(1,1).Value = SignetCompanyName & " - " & WorkSheetName & " - " & StartDate & " thru " & EndDate
      WorksheetObject.Cells(1,1).HorizontalAlignment = -4108      '(-4152 = right, -4131 = Left)
      WorksheetObject.Range(WorksheetObject.Cells(1,1), WorksheetObject.Cells(1, LastColumn + Offset)).Merge
      WorkSheetObject.Cells(1,1).Font.Size = 24
      WorkSheetObject.Cells(1,1).Font.Bold = True
      WorkSheetObject.Cells(1,1).EntireRow.Autofit

        FreezePanes(2)

'This is choking - usually on the Select...
Sub FreezePanes(SheetNumber)
      'WorksheetObject.Range(WorksheetObject.Cells(Row + 1, IndexNumberCol + 1)).ActiveSheet.FreezePanes = True
      ExcelObject.ActiveWorkbook.Worksheets(SheetNumber).Rows(Trim(CStr(Row + 1)) & ":" & Trim(CStr(Row + 1))).Select
      ExcelObject.ActiveWorkbook.Worksheets(SheetNumber).ActiveWindow.FreezePanes = True
      ExcelObject.ActiveWorkbook.Worksheets(SheetNumber).Range("A1").Select
End …
0
Simple process how do you call a macro from a script

C:\TEST.XLSM

Macro called 'Extract'
0
Hello experts,

I have the following procedure reported above which allows me to add string in between for multiple columns.
I would like to take as a reference to perform the following:
>Be able to perform subtraction, summation, multiplication when value is reported at inputbox 2.
Example: I report *1.6 and the various values concerned by the column should be multiplied by 1.6.
If column involved by the operation is not a number exit sub with the following message:
“Unable to proceed as one of the column involved by the operation is not a number.”

If you have questions, please contact me.
Thank you in advance for your help.


Sub Add_String_In_Between_Multiple_Columns()

    Dim sRng As String
    Dim temp_rng As String
    Dim strSpecificChar As Variant    'New declaration in order to add numeric and non numeric values
    Dim strCol As Variant
    Dim strColList As String
    Dim lngLastRow As Long, lngRow As Long
    Dim intWhich As Integer
    Dim intWhich_temp As String

    On Error GoTo Error_Routine

    strColList = InputBox("Please report column letter(s) following by ; in which you want to apply procedure," _
                          & ": A for single column A;C;D for multiple columns", "Choose Column Letter(s)")
    If strColList = vbNullString Then
        MsgBox ("No input!")
        Exit Sub
    End If

    
    temp_rng = Application.InputBox("Please enter value that you want to put in between", Type:=1 + 2)

    If VarType(temp_rng) 

Open in new window

0
Hi,

My requirement is getting Ton per hour with the attached file.

Thanks,

San.TPH.xlsx
0
How would like to extract data from a worksheet into a CSV File

Worksheet = Data

Column A = Names
Column B = Holiday Entitlement
Column C = Holiday Taken
Column D = Holiday Left

So I would like to automatically extract this data into a CSV File with headers

Example

Bob Smith,22,20,2
Ed Smith,22,10,10
0
What are faster built-In alternatives to a timeline slicer? How would I replicate this witj Forms/VBA?

The goal is to give the same functionality as a timeline slicer, but with more control (I.e., A Timeline Slicer lets a user move start/end sliders to update data/charts. How do I make a custom one that is faster and I am able to customize.)
0
Good Afternoon.

I would like some help with a script that takes a range of cells in excel and saves the range as a JPEG image on a local drive.

Then I would like this to cast this image on a TV. So any ideas on what is the best solution to use.. Thinking of a dashboard

Thanks
0
I want to check a variable using typeof. It would either be a collection or a string

    If TypeOf mmbr Is Collection Then
   
    Elseif typeof mmbr is string then

the first one works but the second one does not.

If I use typename() instead of typeof then it works.

What would be the syntax for the string part for typeof?
0
Hello.
I need to find out how to calculate a percentage of between two numbers(range) in excel.
For example, 1st# is -3 , second number is 20. What is percentage number of 5 ? or Any # that can represent location of 5.
Ex) 1st# -3, 2nd# 20. If the number is 20 then 100. if the number is -3 then 0.

Thank you.
0
Dear Experts:

I would like to extract the single characters in these cells using a formula:

Prof. Dr. med. John F Kennedy, i.e. F has to be extracted
Dr. Andrea Skan, i.e. nothing will be extracted
Prof. John B  Sutherland: B will be extracted
James D Sucker, i.e. D will be extracted
etc.

The bolding is just for emphasis, in reality there is no bolding.

Help is much appreciated. Thank you very much in advance.

Regards, Andreas
0
I have a client that is very avid about making a single shared Excel Workbook on their network that 20 people will access/update throughout the day. They don't want a database as I suggested. They want Excel.

My gut tells me there are lots of things that can go wrong and my immediate concern is if the single shared file becomes corrupted all 20 people will lose their work for the day.

*Is there a way to protect the client from my immediate concern?
*Are my concerns valid?
*What other things could go wrong when clients use shared workbooks?
*When is it ideal to use shared workbooks?
0
m Experts:

I got the following sort of strings in Column A:

Prof. Dr. med. John F. Kennedy
Dr. Andrea Skan
Prof. John B.  Sutherland
James D. Sucker
etc.

I would like to insert a formula that replaces the dot after the above single characters with nothing, i.e. after inserting the formula the strings should look like this:

Prof. Dr. med. John F Kennedy
Dr. Andrea Skan
Prof. John B  Sutherland
James D Sucker
etc.

The bolding of these single characters is just for emphasis, in reality there are no bold characters.

Help is much appreciated.

Thank you very much in advance.

Regards, Andreas
0
I need to export an excel file into a Microsoft project. Would you recommend the wizard? What are the steps to import the .xls into ms project?
0
Here's my dilemma.
I have two tables, tblPOAM and tblPOAMMilestones.  PK/FK linked
The tables, queries and forms work just fine.

I can export just the tblPOAM to an excel preformatted template.
But I can not get the tblPOAMMilestones to export correctly.

The Milestones need to show under the POAM item.

See attachment for an example of the output.  Notice that rows 2-4 have the same key and 4 and 5 do not.  

Maybe I am going about this the wrong way so any suggestions would be helpful.
0
Dear Experts:

I need to extract the first name of the following full names with titles using a formula.

Help is much appreciated. Thank you very much in advance.

Regards, Andreas

Priv. Doz. Dr. med. John Meyers
Dr. Jim Jones
Prof. Dr. Winston Churchill
Dr. Dr. Tom Watson
Nathan Meyer
0
I'm trying to update my code to export to Excel in the latest format for .xlsx

I have the following code:

  [HttpPost]
        public void ExportSystemDetails()
        {

            try { 

                Response.Charset = "";
                Response.ClearContent();
                Response.Buffer = true;
                Response.AddHeader("content-disposition", "attachment; filename=Report" + DateTime.Now.Month + "-" + DateTime.Now.Day + "-" + DateTime.Now.Year + ".xls");
                Response.ContentType = "application/ms-excel";

                Response.Output.Write(ExcelExport.exportToExcel().ToString());
                Response.Flush();
                Response.End();


            }
            catch {
                    throw new Exception("There was an error processing your request.  Please try again or contact a member of the Web Team if this error persists.");

            }

        }

Open in new window


I want to update the Response.Content Type or whatever I need to update so the format is now .xlsx
0
how can i integrate excel with access? For example, We have a database file (.accdb) on a sharepoint, that I would like to pull data from into Excel systematically (call it once a day). I then want to use that data and run various analytics off of it/generate reports.
0
Column A has random text information in it. In Column B - I would like it to say "Cash" if the word "Cash" is included in Column A. If not, it says "Not Cash".
0
Hi,

Please find the attached list. In Excel, the list is to be created with 6 continuous days, then 2 days gap, then again 6 days.
Along with that the shifts are to be created in specific order.

Thanks,

San.Date-Shift-List.xlsx
0
I'm writing below codes to create and save workbook and checking sheet is existing in workbook. I didn't test it yet, before that i'd like to ask you what's proper way to declare them. Especially for sheet check and creating new workbook.

    public static bool CreateWorkbook(string filename)
        {
            try
            {
                using (ClosedXML.Excel.XLWorkbook workbook = new ClosedXML.Excel.XLWorkbook())
                {
                    ClosedXML.Excel.IXLWorksheet worksheet = workbook.Worksheets.Add(sheetName: "Default");
                    SaveAs(workbook, filename);
                }

                return true;

            }
            catch (Exception e)
            {
                Console.WriteLine(e);
                throw;
            }

        }

Open in new window



     
  public static bool SaveAs(ClosedXML.Excel.XLWorkbook workbook, string filename)
        {
            try
            {
                workbook.SaveAs(filename);
                return true;
            }
            catch (Exception e)
            {
                Console.WriteLine(e);
                throw;
            }
        }

Open in new window


  public static bool IsSheetExists(ClosedXML.Excel.XLWorkbook workbook, string sheetname)
        {
            try
            {
                bool result = false;

                foreach (ClosedXML.Excel.IXLWorksheet worksheet in workbook.Worksheets)
                {
                    if (worksheet.Name == sheetname)
                    {
                        result = true;
                        break;
                    }
                }

                return result;
            }
            catch (Exception e)
            {
                Console.WriteLine(e);
                throw;
            }
        }

Open in new window

0
Hi

In the spreadsheet shown in the following image and attached I have to find the closest match in Column A to the data shown in column E and F. Columns E and F where the two original "Manufacturer" and "Make" columns. The data was then combined into a new list shown in column A.

1Manufacturer.xlsx
0
Hi,
I'd like a formula to covert the below time format to seconds
Many thanks
Ian


17:23:06
0
Hi,

I would like a formula to replicate from a certain cell in a series.
Please see attached.
I have placed the correct outcomes in column F which refer to column A (yellow)
Please use column G for the formula

Many thanks

Ian
cell-value-formula.xlsx
0
After adding rows into a spreadsheet is there a way to check the spreadsheet to make sure other cells did not change? Maybe after updating the spreadsheet all changes in the worksheet cells including the added rows would be in yellow or flagged in some way along with any other changes in other cells. Maybe a conditional statement or vb code to recognize any changes in the cells after adding records. I would like to use this for testing. If there are no changes to the worksheet cells then only the added records would be in yellow or flagged in some way. Or maybe a way to compare the old and updated worksheet to look for changes and identify what changed. Any ideas thanks
0
Hi,
The below formula finds bottom row.
Can someone please help me modify it to ignore formula cells.
I only want last cell that contains a value.

=ROW(OFFSET(A9,COUNTA(A:A)-1,0))

Many thanks

Ian
0