Posts by tmort

I've put toghether some code to open an excel file and change for format (page setup and set columns to autofit) and then save the file. It works except that I want it to work invisisibly.

If I set notifications to true and save it with a prompt the formatting changes are saved as I want. If I set notifications to false the changes aren't saved.

I haven't experimented with application visible true or false.

Is there a way to have it save the changes without going through the prompt.

The code is below:

Private Sub CommandButton1_Click()


Dim oApp As Excel.Application
  Dim oExcel As Excel.Workbook


  Set oApp = CreateObject("Excel.Application")
  Set oExcel = oApp.Workbooks.Open(Filename:="C:\testxl\test.xls")
  
  oApp.Visible = True
  oApp.DisplayAlerts = False


oExcel.Worksheets("Sheet1").Activate
 
 With oExcel.Worksheets("sheet1").Columns
 
    .Columns("A:Z").AutoFit
    
 End With
 
With oExcel.Worksheets("Sheet1").PageSetup
    
    .Zoom = False
    .FitToPagesTall = 200
    .FitToPagesWide = 1
    
End With


 With oExcel.Worksheets("Sheet1")
 
    .PageSetup.Orientation = xlLandscape


End With
 
   
  ActiveWorkbook.Save


    oApp.Quit
    
   
End Sub

Thanks for any help!

I have a client that uses Excel as a database and I have developed an Access database that prepare reports and other things with at least a portion of the data they have in the Excel Sheet.

The Access database allows data to be imported from a tab delimited file.

I'm just wondering if there are any utilities, macros or code that would simplify the creation of delimited files from their worksheets. I have't analyzed the Excel sheets, but for sure not all the columns would have to be used and they would have to be rearranged and probably some repetitive data might have to be copied into each row.

Thanks in advance

I have an older worksheet that was originally written in Lotus with a macro for data entry. I converted it over to Excel in an earlier version of Excel and the data input macro worked. Since that time we have upgraded to Office XP and now I find when I load the sheet (either the excel of lotus version) that the lotus data entry macro no longer works.

Is there some setting or more to install to make this work again. I realize that Excel uses VB, but, the older versions still supported the old style macros.

Thanks in advance

I see your solution and it works with 21 and some other numbers but doesn't appear to always work. The actual number of records could vary so I don't want to tie myself down if I can avoid it.

I tried it with some real data of 74 records and it didn't fix it. I deleted about ten records and then it worked.

Any more ideas?

Thanks

I see where I had an error on the original attachment, but, I see if I add a few rows and values on the right hand side of the one you edited, again it doesn't work. It picks up the fact that there are a new number of values etc but I get the error

I would like to use the probabilty function and am having some difficulty.

I have a lower limit 0 and an upper limit 1400 an array1 with a set of 21 values and another array2 with a set of 21 probability values. The individual probability values are each 1/count(array1).

I'm finding that if I just have 10 or 20 values it works fine, however, if I have 21 it appears that the sum of the probability values doesn't add up to exactly 1

Or something else is going on. Attached is a sheet that started with 21 values and didn't work. I then copied this to the right. I deleted one set of values from the left side and it works with an even number of 20. However, when I do the same thing on the right side it doesn't even though this should be the same thing????

When I just do an example the thing tends to work, but, when I go with real numbers it tends not to.

Any ideas, thanks in advance!

I looked into that and it appears to be something different that what the guidance material is discussing. I used the figures on pp132 and the table of pp133 of the attached document.

As usual I'm over my head. I have some information on using statistics to set the number of samples to take. The guidance I have makes use of tables and I would like to use Excel to do the calucations, but, I can't seem to find a function that duplicates the table they have and I haven't a clue as to how to do integrals in Excel.

One of the table they reference is called "Areas uner standardized normal density function". I can find a density funtion in Excel but it doesn't appear to be the same thing.

I have attached this guidance material, maybe someone more knowledgable about statistics and Excel can help. The formula that makes reference to this table is in section 4.2.2

Thanks in advance