anyone know how to ensure data is inserted into a new cell in .xls?

[DK8]

I am writing code for a payment file in vba. The data for first name, last name, ssn, employee paid premium and cancel date should be in seperate cells. This should create five cells of data in the .xls file. All of the data is currently being lumped into the first cell. If anyone knows what I need to do here, I would be greatly appreciative. Thanks in advance! Here is the code:

With rstRecs
.CursorLocation = adUseClient
.CursorType = adOpenForwardOnly
.LockType = adLockReadOnly
.ActiveConnection = g_conOLEDB
.Open strSQL

Set .ActiveConnection = Nothing

lngEmployerNo = 0
If (Not (.EOF)) Then
strFileName = CreateNightBatchDirectory() & "Payments\UNUMProvident" & lngCarrier & "Client" & .Fields("EMPLOYER NUMBER").Value & ".txt"
Open strFileName For Output As #1
Add_Email_Process_Record True, 1, lngCarrier, strFileName, False, CInt(Asc("P") & Asc("F"))
Add_Daily_Process_Record "UNUMProvident" & lngCarrier, strFileName

Do While (Not (.EOF))
lngEmployerNo = .Fields("EMPLOYER NUMBER").Value

If (Not (BlankField(.Fields("FIRST NAME").Value))) Then
Print #1, Format(.Fields("FIRST NAME").Value, "!@@@@@@@@@@@@@@@");
Else
Print #1, Space$(15);
End If
If (Not (BlankField(.Fields("LAST NAME").Value))) Then
Print #1, Format(.Fields("LAST NAME").Value, "!@@@@@@@@@@@@@@@");
Else
Print #1, Space$(15);
End If
Print #1, Format(.Fields("EMPLOYEE SSN").Value, "000000000");
If (Not (BlankField(.Fields("EMPLOYEE PAID PREMIUM").Value))) Then
Print #1, Format(.Fields("EMPLOYEE PAID PREMIUM").Value, "0000000.00");
Else
Print #1, Space$(10);
End If
If (Not (BlankField(.Fields("CANCEL DATE").Value))) Then
Print #1, Format(.Fields("CANCEL DATE").Value, "MMDDYYYY");
Print #1, Space$(4);
Else
Print #1, Space$(12),
End If
Print #1,
.MoveNext
Loop

Close #1
End If
End With

 

[George Too]

I don't see your cell reference unless "#1" is it(?) but you have to reference the cell you want your data to go to, something like

If (Not (BlankField(.Fields("FIRST NAME").Value))) Then
.Range("A1") = Format(.Fields("FIRST NAME").Value
Else
.Range("A1") = ""
End If

When you have to transfer a lot of data you can cycle thru your recordset with loops and with variables increase your cell ranges...

 

[DK8]

Yes, #1 references where the data should go; I tried your code but it doesn't work. I don't have .Range in my code anywhere; is that a reference to Me.Range? (as in the current form?) Do you have any other suggestions?

 

[George Too]

I'm no expert here, but by looking at your code, are you creating a txt file? If not, what do you call your excel object? I don't see it in your code. Give the following a try:

'dim your variables
Dim ExcelApp As Object
Dim rst As Recordset

'create your excel object
Set ExcelApp = CreateObject("Excel.Application")
With ExcelApp
.Workbooks.Add 'add a workbook to your object
.Visible = True 'true if u wanna see it else false

'Open your recordset you want to output
Set rst = CurrentDb.OpenRecordset("your_recordset")

'populate recordset
rst.MoveLast
rst.MoveFirst

'fire away
.Range("A1") = rst("field1")
.Range("B1") = rst("field2")
'you can add as many cells u need

'Save the resulting xl file
.Activeworkbook.SaveAs Filename:="put your path or file name"
'Close and quit
.ActiveWindow.Close
.Quit
End With
Set ExcelApp = Nothing

Regards,
George

 

[DK8]

sorry it took so long to reply back

I was off the last couple of days and just got a chance to try your code suggestions. I have one question: how and where is .range declared? because it won't let me declare it. Also when I compile, I get a message saying invalid or unqualified reference. Please help me out, thank you.