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??
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
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
Set oXLBook = Nothing
oXLApp.Quit 'Close (and disconnect from) Excel
Set oXLApp = Nothing
FirstTime = False