Exporting to Excel problem

[TheDocterd]

Hi there

I have a problem. My code works perfect, but now I want to, instead of displaying multiple sheets, display all data in one sheet. Below is my code I used:

Private Sub Picture2_Click()

Screen.MousePointer = vbHourglass

Dim tempAss As String
Dim xlApp As Excel.Application
Dim wb As Workbook
Dim ws As Worksheet
Dim hold() As String

Dim g As Integer

Set xlApp = New Excel.Application
Set wb = xlApp.Workbooks.Open(folder & "Templates\Expense Report.xls")

Dim oucnt As Integer
Dim expense_amount As Single
expense_amount = 0

outcnt = 0

Dim E_temp1() As String
Dim E_temp2() As String

For j = 0 To assistants.ListCount - 1
                    
    If assistants.Selected(j) Then
        
        For i = 0 To nu - 1

            If assistants.List(j) = userlog(i).UName Then
                
                g = 0
                Call Read_TMdata(userlog(i).UID + ".txt")
                
                Set ws = wb.Worksheets(assistants.List(j)) 'Specify your worksheet name
                
                For k = 0 To TMcnt - 1
                If TMdata(k).Expenses <> "" Then
                
                If DateValue(TMdata(k).EDate) >= DateValue(Label4.Caption) And DateValue(TMdata(k).EDate) <= Label6.Caption Then

                    E_temp1 = Split(TMdata(k).Expenses, "$$")

                    For ep = 0 To UBound(E_temp1)

                    If E_temp1(ep) <> "" Then
                    E_temp2 = Split(E_temp1(ep), vbTab)

                    If E_temp2(0) <> "" Then
                        ws.Cells(g + 4, 1).Value = TMdata(k).EDate
                        
                        If InStr(1, TMdata(k).JobCode, "%d%", 1) <> 0 Then
                            hold() = Split(TMdata(k).JobCode, "%d%")
                            ws.Cells(g + 4, 2).Value = hold(0)
                        Else
                            ws.Cells(g + 4, 2).Value = TMdata(k).JobCode
                        End If

                       For n = 0 To nu - 1
                            If userlog(n).UID = TMdata(k).UID Then
                                ws.Cells(g + 4, 3).Value = userlog(n).UName
                            End If
                       Next n

                       ws.Cells(g + 4, 3).Value = E_temp2(0)

                       ws.Cells(g + 4, 4).Value = E_temp2(6)
                       ws.Cells(g + 4, 5).Value = E_temp2(7)
                       ws.Cells(g + 4, 6).Value = CSng(E_temp2(6)) * CSng(E_temp2(7))

                       expense_amount = expense_amount + (CSng(E_temp2(6)) * CSng(E_temp2(7)))
                   End If
                   
                   g = g + 1
                   End If
                   
                   Next ep
                   End If
                   End If
                Next k
            End If
        Next i
    End If
Next j

Call Read_TMdata(user.UID + ".txt")

xlApp.Visible = True

Set ws = Nothing
Set wb = Nothing
Set xlApp = Nothing

Screen.MousePointer = vbDefault

End Sub

Can anyone help me please?

[AndreRet]

Remove

Set ws = wb.Worksheets(assistants.List(j)) 'Specify your worksheet name

this part from your code and all that is referencing it. It opens a specific sheet for you. By removing it, you keep to only one sheet.

[TheDocterd]

Ahh okay I see, but if I remove it it tells me "object variable or with block variable not set and when I debug it, it points to this piece of code:

ws.Cells(g + 4, 1).Value = TMdata(k).EDate

Now this I'm figuring has to do with the cells inside the sheet?

[AndreRet]

I'll test the code a bit later and will post a possible solution.:)

[TheDocterd]

Thanks :)

[TheDocterd]

I have added a sheet in the document called "Expenses". But now when I make all the data display there, I only get some of the expenses...??

Set ws = wb.Worksheets("Expenses") 'Specify your worksheet name

[AndreRet]

"With block or variable not set" refers to a name function in your code that has not been referenced or dimensioned.

In this case it can be any of these 3 "TMdata(k).EDate", TMDate, k, EDate.

Did you manage to get sorted on the one sheet only problem?

[AndreRet]

Seems that I am behind here, sorry for that.

Where does the "xpenses" data coming from. It might be that you are running a loop through all recordsets, but it "cuts" off at a certain part...

[TheDocterd]

Well it's sounds real simple...I read the expenses data from txt files of users. In my code I use "call read_tmdata". This is where the txt file is taken up in segments.

But all that is correct. I'm guessing my problem lies at the code I gave. I tried disabling the list box where I select what user's expense data to display but nothing

[AndreRet]

Have a look at the following code, it imports 100%. Paste it after your code to open the excel workbook -

Public Sub ImportTextFile(FName As String, Sep As String)

Dim RowNdx As Long
Dim ColNdx As Integer
Dim TempVal As Variant
Dim WholeLine As String
Dim Pos As Integer
Dim NextPos As Integer
Dim SaveColNdx As Integer

Application.ScreenUpdating = False
'On Error GoTo EndMacro:

SaveColNdx = ActiveCell.Column
RowNdx = ActiveCell.row

Open FName For Input Access Read As #1

While Not EOF(1)
    Line Input #1, WholeLine
    If Right(WholeLine, 1) <> Sep Then
        WholeLine = WholeLine & Sep
    End If
    ColNdx = SaveColNdx
    Pos = 1
    NextPos = InStr(Pos, WholeLine, Sep)
    While NextPos >= 1
        TempVal = Mid(WholeLine, Pos, NextPos - Pos)
        Cells(RowNdx, ColNdx).Value = TempVal
        Pos = NextPos + 1
        ColNdx = ColNdx + 1
        NextPos = InStr(Pos, WholeLine, Sep)
    Wend
    RowNdx = RowNdx + 1
Wend

EndMacro:
On Error GoTo 0
Application.ScreenUpdating = True
Close #1
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
' END ImportTextFile
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
End Sub

Sub DoTheImport()
ImportTextFile FName:="C:\Test.txt", Sep:="|"
End Sub

I got this from HERE. You might find the other code samples helpful as well.

[TheDocterd]

Thanks for the link. Very useful :)

I don't know why it keeps doing this, but like i mentioned earlier I've created a new template with one sheet called "Expenses". Now when I export the data to the sheet, it only displays one sheets data. How I've noticed this is I've changed it back to the old sheets method and saw the sheets data it displays in the new template I've created.

Isn't there perhaps a way in excel himself where I can create a function to get all the other sheets data and display them all in the "Expenses" sheet??

[AndreRet]

Try something like -

Dim iSheets As Integer

For iSheets = 0 To MyWorkbookName.Worksheets
   'Code to copy the data into here
Next iSheets

This will start at the first sheets and loop through all sheets to the end.