Openening an Excel file from within Project

Openening an Excel file from within Project

• baderboy51

  Hi Daryl,

  Here's some info on your question from the support engineer:

  Thank you for contacting Microsoft ISV Partner Support.

  If you want to change some cells format,I hope the following can help you.

  These examples set the number format for cell A17, row one, and column C (respectively) on Sheet1.

  ===

  Worksheets("Sheet1").Range("A17").NumberFormat = "General"// type=general

  Worksheets("Sheet1").Range("A17"). NumberFormat = "@" //type=text

  Worksheets("Sheet1").Rows(1).NumberFormat = "hh:mm:ss"

  Worksheets("Sheet1").Columns("C"). _

  NumberFormat = "$#,##0.00_);[Red]($#,##0.00)"

  ===

  -brenda (ISV Buddy Team)

  
  
  

• Cyberjunkie

   

  I have a question that relates to this topic.   I currently have a process that creates and inserts into a .xls excel file.   everything works fine, except I don't know how to force a cell format type = text.   everything is of type=general and it wants to do formatting on my data.

  ex.   I have a text field that happens to be mostly numeric characters and it wants to format it like a number    the value I want is 179662E14  but it in the spreadsheet it is displaying as  1.80E+19

  even if I manually change the format of the cell after the fact, it does not go back to the value I want.

  Note:  sometimes this displays wrong just because the Column is not opened up Large enough, but this is a different issue..

  any suggestions

  thanks,

  Daryl

   

  
  

• Joe Simmonds

  Thank you, thank you, thank you. The bit I was missing was setting the reference.
  
  Best,
  
  Mark Edmonds
  
  
  
  

• nil130180

  Per our support engineer:

  Hi Mark, you can use excel automation from visual basic to open /read/write to an excel file.

  How To Automate Microsoft Excel from Visual Basic

  http://support.microsoft.com/kb/219151/
  
  

  INFO: Using Early Binding and Late Binding in Automation

  http://support.microsoft.com/kb/q245115/

  Automate Excel to Create and Format a New Workbook

  The examples automate Microsoft Excel to create a new workbook and transfer data to a worksheet in the new workbook using arrays. The number of rows in the resulting data is determined at run time.

  Visual Basic example

  1. Start a new project in Visual Basic. Form1 is created by default.

  2. Add a reference to the Excel object library.

  3. Add a CommandButton to Form1.

  4. Add the following code to the module for Form1:

  Const NUMROWS = 20

  Private Sub Command1_Click()

  'Start a new workbook in Excel

  Dim oExcel As Excel.Application

  Dim oBook As Excel.Workbook

  Set oExcel = New Excel.Application

  Set oBook = oExcel.Workbooks.Add

  'Get the first worksheet in the workbook so that you can

  'make changes to it

  Dim oSheet As Excel.Worksheet

  Set oSheet = oBook.Worksheets(1)

  'Add headers to Row 1 of the worksheet

  oSheet.Range("A1:D1").Value = Array("Date", "Order #", _

  "Amount", "Tax")

  'Create an array that is NUMROWS x 3 columns.

  'Column 1 will contain dates, column 2 will contain strings

  'and column 3 will contain numbers

  ReDim vArray(1 To NUMROWS, 1 To 3) As Variant

  Dim i As Integer

  For i = 1 To NUMROWS

  vArray(i, 1) = Format(DateSerial(1999, _

  (Rnd * 100) Mod 12, (Rnd * 100) Mod 28), "m/d/yy")

  vArray(i, 2) = "ORDR" & i + 1000

  vArray(i, 3) = Format(Rnd * 100, "#0.00")

  Next

  'Fill a range, starting at cell A2 with the data from the array

  oSheet.Range("A2").Resize(NUMROWS, 3).Value = vArray

  'Fill the fourth column with a formula to compute the sales tax.

  'Note that the formula uses a "relative" cell reference so that

  'it fills properly

  oSheet.Range("D2").Resize(NUMROWS, 1).Formula = "=C2*0.07"

  'Format the worksheet

  With oSheet.Range("A1:D1")

  .Font.Bold = True

  .EntireColumn.AutoFit

  End With

  'Make Excel visible and give the user control

  oExcel.Visible = True

  oExcel.UserControl = True

  End Sub

  Run the application and click the CommandButton. When the code finishes running, a new workbook in Excel appears containing data .

  ===================================

  -brenda (ISV Buddy Team)

  
  
  
• Openening an Excel file from within Project