Output to Excel

[maththetraveller]

Hi! I created a macro that closes customers accounts. At first, the user needs to choose the reason for closing the account, then the macro performs the action and leaves a detailed note in the account. At the end, the macro sends the reason in a .txt file. I tried to send it to Excel, but my problem is that each word is being sent to a different cell. In the example, the reason would be "Prefers to use bank card / Other Card". In Excel, the date is in one cell, the time in another one, "am" "pm" in a third one, "Prefers" in a fourth one, "to" in a fifth one, "use" in a sixth one etc...

What I want is to have the date in one cell, the time in a second cell and the full reason in a third cell.

This part of the code is like this:

Reason = "Prefers to use bank card / Other card"
Open "C:\tempdata\Tracking.xls" For Append As #1
Print #1, Date, Time, Reason
Close


Thanks for your help!

 

[SkipVought]

"I tried to send it to Excel, but my problem is that each word is being sent to a different cell."

Where is the code that sends the data, "to a different cell?"

BTW, that does not have to happen. Your code can control EXACTLY where the data goes in Excel.

Skip,

_{Have you heard that the roundest knight at King Arthur's round table was...
Sir Cumference!}

 

[maththetraveller]

That's the part of the code that sends data to different cells in Excel:

Reason = "Prefers to use bank card / Other card"
Open "C:\tempdata\Tracking.xls" For Append As #1
Print #1, Date, Time, Reason
Close

 

[SkipVought]

Check out the CreateObject and GetObject methods.

dim xl as Excel.Application, wb as excel.Workbook
set xl = CreateObject("Excel.Application")
set wb = xl.workbooks.add
wb.Sheets(1).cells(1,1).value = Date
wb.Sheets(1).cells(1,2).value = Time
wb.Sheets(1).cells(1,3).value = Reason
'...
wb.saveas "somename.xls"
wb.close
set be = nothing
xl.quit
set xl = nothing

Skip,

_{Have you heard that the roundest knight at King Arthur's round table was...
Sir Cumference!}

 

[maththetraveller]

Thanks a lot! I'll try it at work tomorrow!

 

[maththetraveller]

Oh... Will that "append" to what's already in the file or will it overwrite on it?

 

[SkipVought]

Depends the row you designate.

dim lRow as long
lRow = wb.Sheets(1).cells(1,1).currentregion.rows.count + 1
wb.Sheets(1).cells(lRow ,1).value = Date
wb.Sheets(1).cells(lRow ,2).value = Time
wb.Sheets(1).cells(lRow ,3).value = Reason

Please describe the workbook you are writing to.

Skip,

_{Have you heard that the roundest knight at King Arthur's round table was...
Sir Cumference!}

 

[maththetraveller]

I finally got this, which works great! I'll add it to my code tomorrow.

Sub Main()
Dim Sessions As Object, System As Object, Sess0 As Object
Dim xl As Object, xl_workbook As Object, xl_sheet As Object
Dim iRows As Long, iCols As Long

Set System = CreateObject("EXTRA.System")
Set Sessions = System.Sessions
Set Sess0 = System.ActiveSession

Set xl = CreateObject("Excel.Application")
Set xl_workbook = xl.Workbooks.Open("C:\tracking.xls")
Set xl_sheet = xl_workbook.Worksheets("Sheet1")

'iRows = Sess0.Screen.Rows
iCols = Sess0.Screen.Cols

Reason = ""& XREASON &""

lRow = xl_sheet.cells(1,1).currentregion.rows.count + 1
xl_sheet.cells(lRow ,1).value = Date
xl_sheet.cells(lRow ,2).value = Time
xl_sheet.cells(lRow ,3).value = Reason

xl_workbook.Save
xl_workbook.Close
xl.quit

Set xl_sheet = Nothing
Set xl_workbook = Nothing
Set xl = Nothing
Set Sess0 = Nothing
Set Sessions = Nothing
Set System = Nothing
End Sub