Solved

Create an Excel file when Excel is not installed on the PC?

Posted on 2002-03-15
24
322 Views
Last Modified: 2012-06-21
Hi everyone,

I have a program that creates an Excel spreadsheet every night on my own PC.  I would prefer this process to run on my NT server instead just in case I forget about the scheduled task and switch the PC off.

I have created a package for the exe which includes "Excel9.olb" but the program crashes when it tries to run.

Is it possible for this program to work when Excel is not already installed?  If so, do I need to include any other files in the install package?

Thanks

Gary
0
Comment
Question by:garygreen
  • 9
  • 6
  • 3
  • +4
24 Comments
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 6867724
Acutally, you need Excel installed to run Excel Automation. Thus, as Excel is installed, you (probably) don't need any other files except the vb runtime.

CHeers
0
 
LVL 69

Expert Comment

by:Éric Moreau
ID: 6867872
you may create a .CSV file (simple comma-separated values) that will be recognize from Excel natively. This won't require Excel installed on your server.
0
 
LVL 44

Expert Comment

by:bruintje
ID: 6867929
Hi garygreen, i had found something a few years back but can't find the site of the person who wrote this in the first place

i don't know if it's doing what you want but i used the following class file for writing excel files from a automatic process that had to be logged

---------------->begin code

'Class file for writing Microsoft Excel BIFF 2.1 files.

'This class is intended for users who do not want to use the huge
'Jet or ADO providers if they only want to export their data to
'an Excel compatible file.

'Newer versions of Excel use the OLE Structure Storage methods
'which are quite complicated.

'Paul Squires, October 3, 2000
'rambo2000@canada.com


'enum to handle the various types of values that can be written
'to the excel file.
Public Enum ValueTypes
    xlsinteger = 0
    xlsNumber = 1
    xlsText = 2
End Enum

'enum to hold cell alignment
Public Enum CellAlignment
    xlsGeneralAlign = 0
    xlsLeftAlign = 1
    xlsCentreAlign = 2
    xlsRightAlign = 3
    xlsFillCell = 4
    xlsLeftBorder = 8
    xlsRightBorder = 16
    xlsTopBorder = 32
    xlsBottomBorder = 64
    xlsShaded = 128
End Enum

'enum to handle selecting the font for the cell
Public Enum CellFont
    'used by rgbAttr2
    'bits 0-5 handle the *picture* formatting, not bold/underline etc...
    'bits 6-7 handle the font number
    xlsFont0 = 0
    xlsFont1 = 64
    xlsFont2 = 128
    xlsFont3 = 192
End Enum

Public Enum CellHiddenLocked
    'used by rgbAttr1
    'bits 0-5 must be zero
    'bit 6 locked/unlocked
    'bit 7 hidden/not hidden
    xlsNormal = 0
    xlsLocked = 64
    xlsHidden = 128
End Enum


'set up variables to hold the spreadsheet's layout
Public Enum MarginTypes
   xlsLeftMargin = 38
   xlsRightMargin = 39
   xlsTopMargin = 40
   xlsBottomMargin = 41
End Enum


Public Enum FontFormatting
   'add these enums together. For example: xlsBold + xlsUnderline
   xlsNoFormat = 0
   xlsBold = 1
   xlsItalic = 2
   xlsUnderline = 4
   xlsStrikeout = 8
End Enum


Private Type FONT_RECORD
   opcode As Integer  '49
   length As Integer  '5+len(fontname)
   FontHeight As Integer
   
   'bit0 bold, bit1 italic, bit2 underline, bit3 strikeout, bit4-7 reserved
   FontAttributes1 As Byte
   
   FontAttributes2 As Byte  'reserved - always 0
   
   FontNameLength As Byte
End Type


Private Type PASSWORD_RECORD
   opcode As Integer  '47
   length As Integer  'len(password)
End Type


Private Type HEADER_FOOTER_RECORD
   opcode As Integer  '20 Header, 21 Footer
   length As Integer  '1+len(text)
   TextLength As Byte
End Type


Private Type PROTECT_SPREADSHEET_RECORD
   opcode As Integer  '18
   length As Integer  '2
   Protect As Integer
End Type

Private Type COLWIDTH_RECORD
   opcode As Integer  '36
   length As Integer  '4
   col1 As Byte       'first column
   col2 As Byte       'last column
   ColumnWidth As Integer   'at 1/256th of a character
End Type

'Beginning Of File record
Private Type BEG_FILE_RECORD
  opcode As Integer
  length As Integer
  version As Integer
  ftype As Integer
End Type

'End Of File record
Private Type END_FILE_RECORD
  opcode As Integer
  length As Integer
End Type

'true/false to print gridlines
Private Type PRINT_GRIDLINES_RECORD
  opcode As Integer
  length As Integer
  PrintFlag As Integer
End Type

'Integer record
Private Type tInteger
  opcode As Integer
  length As Integer
  row As Integer     'unsigned integer
  col As Integer
 
  'rgbAttr1 handles whether cell is hidden and/or locked
  rgbAttr1 As Byte
 
  'rgbAttr2 handles the Font# and Formatting assigned to this cell
  rgbAttr2 As Byte
 
  'rgbAttr3 handles the Cell Alignment/borders/shading
  rgbAttr3 As Byte
 
  intValue As Integer  'the actual integer value
End Type

'Number record
Private Type tNumber
  opcode As Integer
  length As Integer
  row As Integer
  col As Integer
  rgbAttr1 As Byte
  rgbAttr2 As Byte
  rgbAttr3 As Byte
  NumberValue As Double  '8 Bytes
End Type

'Label (Text) record
Private Type tText
  opcode As Integer
  length As Integer
  row As Integer
  col As Integer
  rgbAttr1 As Byte
  rgbAttr2 As Byte
  rgbAttr3 As Byte
  TextLength As Byte
End Type

Private Type MARGIN_RECORD_LAYOUT
  opcode As Integer
  length As Integer
  MarginValue As Double  '8 bytes
End Type


Dim FileNumber As Integer
Dim BEG_FILE_MARKER As BEG_FILE_RECORD
Dim END_FILE_MARKER As END_FILE_RECORD



Public Function CreateFile(ByVal FileName As String) As Integer

On Error GoTo Write_Error

    If Dir$(FileName) > "" Then
       Kill FileName
    End If
   
    FileNumber = FreeFile
    Open FileName For Binary As #FileNumber
    Put #FileNumber, , BEG_FILE_MARKER  'must always be written first
   
    'OpenFile = Nothing  'return with no error
   
Exit Function

Write_Error:
    'OpenFile = Err.Number
    Exit Function

End Function

Public Function CloseFile() As Integer

On Error GoTo Write_Error

    Put #FileNumber, , END_FILE_MARKER
    Close #FileNumber

    'CloseFile = 0  'return with no error code
   
Exit Function

Write_Error:
    'CloseFile = Err.Number
    Exit Function

End Function

Public Function OpenFile() As Integer
' added 01112000 Brian

On Error GoTo Write_Error

    If Dir$(FileName) > "" Then
      FileNumber = FreeFile
      Open FileName For Binary As #FileNumber
    End If
     ' OpenFile = 0  'return with no error
   
Exit Function

Write_Error:
    'OpenFile = Err.Number
    Exit Function

End Function

Private Sub Class_Initialize()

'Set up default values for records
'These should be the values that are the same for every record of these types
   
    With BEG_FILE_MARKER  'beginning of file
        .opcode = 9
        .length = 4
        .version = 2
        .ftype = 10
    End With
   
    With END_FILE_MARKER  'end of file marker
        .opcode = 10
    End With
   
   
End Sub


Public Function WriteValue(ValueType As ValueTypes, CellFontUsed As CellFont, Alignment As CellAlignment, HiddenLocked As CellHiddenLocked, lrow As Long, lcol As Long, value As Variant) As Integer

On Error GoTo Write_Error

'the row and column values are written to the excel file as
'unsigned integers. Therefore, must convert the longs to integer.
    If lrow > 32767 Then
       row% = CInt(lrow - 65536) - 1  'rows/cols in Excel binary file are zero based
    Else
       row% = CInt(lrow) - 1
    End If
   
    If lcol > 32767 Then
       col% = CInt(lcol - 65536) - 1  'rows/cols in Excel binary file are zero based
    Else
       col% = CInt(lcol) - 1
    End If

   
    Select Case ValueType
      Case ValueTypes.xlsinteger
         Dim INTEGER_RECORD As tInteger
         With INTEGER_RECORD
           .opcode = 2
           .length = 9
           .row = row%
           .col = col%
           .rgbAttr1 = CByte(HiddenLocked)
           .rgbAttr2 = CByte(CellFontUsed)
           .rgbAttr3 = CByte(Alignment)
           .intValue = CInt(value)
         End With
         Put #FileNumber, , INTEGER_RECORD
   
   
      Case ValueTypes.xlsNumber
         Dim NUMBER_RECORD As tNumber
         With NUMBER_RECORD
           .opcode = 3
           .length = 15
           .row = row%
           .col = col%
           .rgbAttr1 = CByte(HiddenLocked)
           .rgbAttr2 = CByte(CellFontUsed)
           .rgbAttr3 = CByte(Alignment)
           .NumberValue = CDbl(value)
         End With
         Put #FileNumber, , NUMBER_RECORD
     
     
      Case ValueTypes.xlsText
         Dim b As Byte
         st$ = CStr(value)
         l% = Len(st$)
       
        Dim TEXT_RECORD As tText
        With TEXT_RECORD
          .opcode = 4
          .length = 10
          'Length of the text portion of the record
          .TextLength = l%
       
          'Total length of the record
          .length = 8 + l
       
          .row = row%
          .col = col%
         
          .rgbAttr1 = CByte(HiddenLocked)
          .rgbAttr2 = CByte(CellFontUsed)
          .rgbAttr3 = CByte(Alignment)
       
          'Put record header
          Put #FileNumber, , TEXT_RECORD
       
          'Then the actual string data
          For a = 1 To l%
             b = Asc(Mid$(st$, a, 1))
             Put #FileNumber, , b
          Next
        End With
     
    End Select
   
    WriteValue = 0   'return with no error
   
Exit Function

Write_Error:
    WriteValue = Err.Number
    Exit Function

End Function


Public Function SetMargin(Margin As MarginTypes, MarginValue As Double) As Integer

On Error GoTo Write_Error

    'write the spreadsheet's layout information (in inches)
    Dim MarginRecord As MARGIN_RECORD_LAYOUT
   
    With MarginRecord
      .opcode = Margin
      .length = 8
      .MarginValue = MarginValue 'in inches
    End With
    Put #FileNumber, , MarginRecord

    SetMargin = 0
   
Exit Function

Write_Error:
    SetMargin = Err.Number
    Exit Function

End Function


Public Function SetColumnWidth(FirstColumn As Byte, LastColumn As Byte, WidthValue As Integer)

On Error GoTo Write_Error

    Dim COLWIDTH As COLWIDTH_RECORD
   
    With COLWIDTH
      .opcode = 36
      .length = 4
      .col1 = FirstColumn - 1
      .col2 = LastColumn - 1
      .ColumnWidth = WidthValue * 256  'values are specified as 1/256 of a character
    End With
    Put #FileNumber, , COLWIDTH

    SetColumnWidth = 0
   
Exit Function

Write_Error:
    SetColumnWidth = Err.Number
    Exit Function

End Function


Public Function SetFont(FontName As String, FontHeight As Integer, FontFormat As FontFormatting) As Integer

On Error GoTo Write_Error

    'you can set up to 4 fonts in the spreadsheet file. When writing a value such
    'as a Text or Number you can specify one of the 4 fonts (numbered 0 to 3)
   
    Dim FONTNAME_RECORD As FONT_RECORD
   
    l% = Len(FontName)
   
    With FONTNAME_RECORD
      .opcode = 49
      .length = 5 + l%
      .FontHeight = FontHeight * 20
      .FontAttributes1 = CByte(FontFormat)  'bold/underline etc...
      .FontAttributes2 = CByte(0) 'reserved-always zero!!
      .FontNameLength = CByte(Len(FontName))
    End With
    Put #FileNumber, , FONTNAME_RECORD

    'Then the actual font name data
    Dim b As Byte
    For a = 1 To l%
       b = Asc(Mid$(FontName, a, 1))
       Put #FileNumber, , b
    Next

    SetFont = 0
   
Exit Function

Write_Error:
    SetFont = Err.Number
    Exit Function


End Function


Public Function SetHeader(HeaderText As String) As Integer

On Error GoTo Write_Error

    Dim HEADER_RECORD As HEADER_FOOTER_RECORD
   
    l% = Len(HeaderText)
   
    With HEADER_RECORD
      .opcode = 20
      .length = 1 + l%
      .TextLength = CByte(Len(HeaderText))
    End With
    Put #FileNumber, , HEADER_RECORD

    'Then the actual Header text
    Dim b As Byte
    For a = 1 To l%
       b = Asc(Mid$(HeaderText, a, 1))
       Put #FileNumber, , b
    Next

    SetHeader = 0
   
Exit Function

Write_Error:
    SetHeader = Err.Number
    Exit Function

End Function



Public Function SetFooter(FooterText As String) As Integer

On Error GoTo Write_Error

    Dim FOOTER_RECORD As HEADER_FOOTER_RECORD
   
    l% = Len(FooterText)
   
    With FOOTER_RECORD
      .opcode = 21
      .length = 1 + l%
      .TextLength = CByte(Len(FooterText))
    End With
    Put #FileNumber, , FOOTER_RECORD

    'Then the actual Header text
    Dim b As Byte
    For a = 1 To l%
       b = Asc(Mid$(FooterText, a, 1))
       Put #FileNumber, , b
    Next

    SetFooter = 0
   
Exit Function

Write_Error:
    SetFooter = Err.Number
    Exit Function

End Function



Public Function SetFilePassword(PasswordText As String) As Integer

On Error GoTo Write_Error

    Dim FILE_PASSWORD_RECORD As PASSWORD_RECORD
   
    l% = Len(PasswordText)
   
    With FILE_PASSWORD_RECORD
      .opcode = 47
      .length = l%
    End With
    Put #FileNumber, , FILE_PASSWORD_RECORD

    'Then the actual Password text
    Dim b As Byte
    For a = 1 To l%
       b = Asc(Mid$(PasswordText, a, 1))
       Put #FileNumber, , b
    Next

    SetFilePassword = 0
   
Exit Function

Write_Error:
    SetFilePassword = Err.Number
    Exit Function

End Function




Public Property Let PrintGridLines(ByVal newvalue As Boolean)

On Error GoTo Write_Error

    Dim GRIDLINES_RECORD As PRINT_GRIDLINES_RECORD
   
    With GRIDLINES_RECORD
      .opcode = 43
      .length = 2
      If newvalue = True Then
        .PrintFlag = 1
      Else
        .PrintFlag = 0
      End If
     
    End With
    Put #FileNumber, , GRIDLINES_RECORD

Exit Property

Write_Error:
    Exit Property

 
End Property




Public Property Let ProtectSpreadsheet(ByVal newvalue As Boolean)

On Error GoTo Write_Error

    Dim PROTECT_RECORD As PROTECT_SPREADSHEET_RECORD
   
    With PROTECT_RECORD
      .opcode = 18
      .length = 2
      If newvalue = True Then
        .Protect = 1
      Else
        .Protect = 0
      End If
     
    End With
    Put #FileNumber, , PROTECT_RECORD

Exit Property

Write_Error:
    Exit Property

 
End Property


Private Sub Class_Terminate()

'if the file is still open when we terminate the class then make sure
'we close it. This is for safety reasons.
If FileNumber > 0 Then
  Call Me.CloseFile
End If

End Sub

----------------------->end code

and this is the function i used to write the file with the class above

'------------------------------------
Private Sub Report2Excel(vExcelFile As ExcelFile, _
    vIndex As Index, _
    vState As Byte, _
    vRowNumber As Integer)
'------------------------------------
'Dim m_clsExcelReport As New ExcelFile
Dim i As Integer

With vExcelFile
  Select Case vState
  Case Is = 0
    'Create the new spreadsheet
    Dim FileName$
    FileName$ = App.Path & "\output\" & vIndex.IndexId & "_wijzigingsReport.xls"  'create spreadsheet in the output directory
    .CreateFile FileName$
   
    'specify whether to print the gridlines or not
    'this should come before the setting of fonts and margins
    .PrintGridLines = False
       
    'it is a good idea to set margins, fonts and column widths
    'prior to writing any text/numerics to the spreadsheet. These
    'should come before setting the fonts.
   
    .SetMargin xlsTopMargin, 1.5   'set to 1.5 inches
    .SetMargin xlsLeftMargin, 1.5
    .SetMargin xlsRightMargin, 1.5
    .SetMargin xlsBottomMargin, 1.5
       
    'Up to 4 fonts can be specified for the spreadsheet. This is a
    'limitation of the Excel 2.1 format. For each value written to the
    'spreadsheet you can specify which font to use.
   
    .SetFont "Arial", 10, xlsNoFormat              'font0
    .SetFont "Arial", 10, xlsBold                  'font1
    .SetFont "Arial", 10, xlsBold + xlsUnderline   'font2
    .SetFont "Courier", 12, xlsItalic              'font3
       
    'Column widths are specified in Excel as 1/256th of a character.
    .SetColumnWidth 1, 5, 18
   
    'set any header or footer that you want to print on
    'every page. This text will be centered at the top and/or
    'bottom of each page. The font will always be the font that
    'is specified as font0, therefore you should only set the
    'header/footer after specifying the fonts through SetFont.
    .SetHeader "This is the header"
    .SetFooter "This is the footer"
       
    ' write sheet header
    .WriteValue xlsText, xlsFont2, xlsLeftAlign, xlsNormal, 1, 1, "Fondsid"
    .WriteValue xlsText, xlsFont2, xlsLeftAlign, xlsNormal, 1, 2, "Fondsnaam"
    .WriteValue xlsText, xlsFont2, xlsLeftAlign, xlsNormal, 1, 3, "FreeFloat"
    .WriteValue xlsText, xlsFont2, xlsLeftAlign, xlsNormal, 1, 4, "AantalAandelen"
    .WriteValue xlsText, xlsFont2, xlsLeftAlign, xlsNormal, 1, 5, "EuroCap"
    .WriteValue xlsText, xlsFont2, xlsLeftAlign, xlsNormal, 1, 6, "IndexWeging"
   
    'write some data to the spreadsheet
    For i = 1 To vIndex.Fondsen.Count
      .WriteValue xlsinteger, xlsFont0, xlsLeftAlign, xlsNormal, vRowNumber + i, 1, vIndex.Fondsen(i).Fondsid
      .WriteValue xlsText, xlsFont0, xlsLeftAlign, xlsNormal, vRowNumber + i, 2, vIndex.Fondsen(i).Fondsnaam
      .WriteValue xlsNumber, xlsFont0, xlsLeftAlign, xlsNormal, vRowNumber + i, 3, vIndex.Fondsen(i).FreeFloat
      .WriteValue xlsNumber, xlsFont0, xlsLeftAlign, xlsNormal, vRowNumber + i, 4, vIndex.Fondsen(i).TotalStock
      .WriteValue xlsNumber, xlsFont0, xlsLeftAlign, xlsNormal, vRowNumber + i, 5, vIndex.Fondsen(i).EuroCap
      .WriteValue xlsNumber, xlsFont0, xlsLeftAlign, xlsNormal, vRowNumber + i, 6, vIndex.Fondsen(i).IndexValue
    Next i
   
'    MsgBox "Excel report sreadsheet created." & vbCrLf & "Filename: " & FileName$, vbInformation + vbOKOnly, "Excel Class"
 
  Case Is = 1
 
    'write some data to the spreadsheet
    For i = 1 To vIndex.Fondsen.Count
      .WriteValue xlsinteger, xlsFont0, xlsLeftAlign, xlsNormal, vRowNumber + i, 1, vIndex.Fondsen(i).Fondsid
      .WriteValue xlsText, xlsFont0, xlsLeftAlign, xlsNormal, vRowNumber + i, 2, vIndex.Fondsen(i).Fondsnaam
      .WriteValue xlsNumber, xlsFont0, xlsLeftAlign, xlsNormal, vRowNumber + i, 3, vIndex.Fondsen(i).FreeFloat
      .WriteValue xlsNumber, xlsFont0, xlsLeftAlign, xlsNormal, vRowNumber + i, 4, vIndex.Fondsen(i).TotalStock
      .WriteValue xlsNumber, xlsFont0, xlsLeftAlign, xlsNormal, vRowNumber + i, 5, vIndex.Fondsen(i).EuroCap
      .WriteValue xlsNumber, xlsFont0, xlsLeftAlign, xlsNormal, vRowNumber + i, 6, vIndex.Fondsen(i).IndexValue
    Next i
 
    'Finally, close the spreadsheet
    .CloseFile
 
  End Select
   
   
    MsgBox "Excel report spreadsheet created." & vbCrLf & "Filename: " & FileName$, vbInformation + vbOKOnly, "Excel Class"
   
End With

End Sub
'------------------------------------

i hope the code isn't too mangled here or else you can mail a reply address so i forward the class to you

HTH:O)Bruintje
0
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 6867948
WOW !!!!
0
 
LVL 18

Expert Comment

by:bobbit31
ID: 6868114
i have a feeling, that piece of code is gonna come in real handy someday (for me that is ;))
0
 

Author Comment

by:garygreen
ID: 6868153
Cheers bruintje, I will start looking at that code this weekend.  I will hopefully give you an answer on Monday.

Thanks again :)
0
 
LVL 44

Expert Comment

by:bruintje
ID: 6868375
that's ok if there are any questions regarding that code just ask.....
0
 
LVL 16

Expert Comment

by:Richie_Simonetti
ID: 6868624
bruintje, that code sounds nice...
0
 
LVL 44

Expert Comment

by:bruintje
ID: 6868678
just adapted the code from the originator, didn't come up with the bulk myself ;)
0
 
LVL 3

Expert Comment

by:GeneM
ID: 6871187
Hi bruintje

I am having a little trouble figuring out how to call the ReporttoExcel function.  Where do I find the ExcelFile and Index data types?   Can you (or anyone else) post an example and explaination of how you have successfully built an Excel sample file?

I'm sorry if these questions are elementary, but I'm still trying to learn how to use VB.  This looks like something I may want to be able to do.

GeneM
0
 
LVL 44

Expert Comment

by:bruintje
ID: 6871223
i'll put together a small sample app tomorrow at work(yeah lucky me is working on sunday) since all the working code is stalled there and post it here :O)Bruintje
0
 
LVL 16

Expert Comment

by:Richie_Simonetti
ID: 6872187
bruintje, vIndex is missing!, another class maybe?
0
Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

 
LVL 44

Accepted Solution

by:
bruintje earned 50 total points
ID: 6873735
yeah Richie that's true......ok i try to do to many things at once all the time getting over-enthousiastic about EE ;)

but OK looked for the original poster of this code and found it here including a sample

http://www.planet-source-code.com/xq/ASP/txtCodeId.11898/lngWId.1/qx/vb/scripts/ShowCode.htm

HTH:O)Bruintje
0
 

Author Comment

by:garygreen
ID: 6876528
Cheers bruintje!

This really has helped a lot...I'm sure many others will be able to use that code also.

Thanks again

Gary
0
 
LVL 16

Expert Comment

by:Richie_Simonetti
ID: 6876678
If there are situations where points are well derserved, this is the max one.
0
 
LVL 44

Expert Comment

by:bruintje
ID: 6876792
wow thanks for the support
0
 

Author Comment

by:garygreen
ID: 6876833
Hi again bruintje,

Any idea on how to write to row 32768?  I have a 50,000 line text file and this is causing a slight headache I am not sure how to resolve.

Also, does BIFF 2.1 support the Excel "AutoFilter" command and if so, any ideas on how to activate it?

I will award more points if you (or anyone) can help.

Cheers

Gary
0
 
LVL 44

Expert Comment

by:bruintje
ID: 6877115
hmmmm... Biff 2 is the old excel format, and i remember that old excel files < 97 had the 32K limit

for that autofilter part, i've to look into this a bit more
0
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 6877142
excel doesn't support more than 32767 rows, even not in Excel 2000 and xp. bruintje will have difficult to find a solution for that...

CHeers
0
 

Author Comment

by:garygreen
ID: 6877220
Thanks bruintje.  The file imports all 50,000 lines but skips line 32768 which is the tricky bit.

angellll - my Excel 2000 supports 65536 rows....how many does yours support??

Gary
0
 
LVL 44

Expert Comment

by:bruintje
ID: 6877284
when i looked at that code i could create a 65000+ row file in the test, which is the current maximum for excel 64K

so what is the problem with the file you trying to write

i changed the testcode to something like

  Dim i As Long
     For i = 19 To 65000
       d = "21/10/1998"
      .WriteValue xlsnumber, xlsFont0, xlsCentreAlign, xlsNormal, i, 1, d, 12
     Next i

which wrote niceley away and could be opened in Excel showing that date till row 65001

about that autofilter feature it seems not be supported in the file format as the class is written now, and that would take some time to get doen because i'm not sure if i can get the specs to implement this......

:O)Bruintje
0
 

Author Comment

by:garygreen
ID: 6877359
When I ran your code, it again skipped line 32768...

About the autofilter, this was a "nice to have" but not essential - thanks anyway.

Cheers
0
 
LVL 44

Expert Comment

by:bruintje
ID: 6910318
sorry didn't receive that last comment it's that i'm looking at the BIFF specs again and wandered into this one again

if you want i can send you code and sheet that is creates?
0
 

Author Comment

by:garygreen
ID: 6911224
That would be great, thanks

No worries about missing the post, just grateful you have taken the time to respond :)
0

Featured Post

What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

I’ve seen a number of people looking for examples of how to access web services from VB6.  I’ve been using a test harness I built in VB6 (using many resources I found online) that I use for small projects to work out how to communicate with web serv…
Since upgrading to Office 2013 or higher installing the Smart Indenter addin will fail. This article will explain how to install it so it will work regardless of the Office version installed.
Get people started with the process of using Access VBA to control Outlook using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Microsoft Outlook. Using automation, an Access applic…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…

760 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now