Number format to show both zeros AND blanks

03-31-2010, 09:11 AM
I have a VB6 program that's transfering data into Excel.. some of the data has zeros and blanks that need to be transfered over exactly as is, with both zeros AND blanks. I've tried many different number formats and it seems I can only show either zeros OR blanks, in some instances, Excel changes my blanks to zeros.. NOT what I want, I need both... anyone know what format will work??

Colin Legg
03-31-2010, 09:18 AM

How are you transferring the data into Excel?

03-31-2010, 09:23 AM
I'm a little embaressed to show my code because I know it's not the best and there are probably more errors... right now I'm just trying to fix one error at a time...

That being said, here it is...

Command1.Enabled = False
Command4.Enabled = True
Row = 0
'Col = 1
Start = Text1.Text
End1 = (Text2.Text - Text1.Text) + 1

'open Excel and create a new worksheet
Dim oXLApp As Excel.Application 'Declare the object variables
Dim oXLBook As Excel.Workbook
Dim oXLSheet As Excel.Worksheet

Set oXLApp = New Excel.Application 'Create a new instance of Excel

If FirstTime = True Then
Set oXLBook = oXLApp.Workbooks.Add 'Add a new workbook
Set oXLBook = oXLApp.Workbooks.Open(Text4.Text) 'Open an existing workbook
End If

Set oXLSheet = oXLBook.Worksheets(1) 'Work with the first worksheet

'This file is the input
INfile = FreeFile()
Open Text3.Text For Input As #INfile

Do While Not EOF(INfile) 'Continue to read and process MAINFILE until EOF is reached

Line Input #INfile, Rec 'Read a record
Row = Row + 1

'read data into worksheet
Xfer = (Mid(Rec, Start, End1))
oXLSheet.Cells(Row, Col).Value = Xfer
oXLSheet.Cells(Row, Col).NumberFormat = "space"


MsgBox "column created"

'Save the workbook to a new file:
Set oXLSheet = Nothing 'disconnect from the Worksheet
oXLBook.SaveAs Text4.Text 'Save (and disconnect from) the Workbook
oXLBook.Close SaveChanges:=False
Set oXLBook = Nothing
oXLApp.Quit 'Close (and disconnect from) Excel
Set oXLApp = Nothing
FirstTime = False

End Sub

EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum