Problem writing data to Excel

I think the problem is that you are creating new excel instance for every subfolder... you should create excel instance only once and then recurse through all subfolders in a separate function... also, is it necessary to save the workbook after each folder?

Darko

OK, so if I understand you correctly, in one Sub I should create an instance of excel and in another sub execute my subfolder part?  Is this correct?

Yes

Darko

do have any samples?

I've created the following but I'm getting errors.

Public Function OpenExcel()
    Dim xl As New Excel.Application
    Dim wb As Excel.Workbook
    Dim ws As Excel.Worksheet

    Set wb = xl.Workbooks.Open(strSavePath)
    Set ws = wb.Worksheets("Sheet1")
   
End Function

Public Sub WriteExcel()

    Debug.Print strRange
    Debug.Print strFolderPath2
   
    ws.Range(strRange).Value = strFolderPath2
    wb.Application.Workbooks(1).Save
   
End Sub

Private Sub CloseExcel()
    wb.Application.Workbooks(1).Save
 
    'cleanup
    wb.Close
    xl.Quit
   
    Set wb = Nothing
    Set xl = Nothing
   
End Sub

I'm getting the error "object required" within:

Private Sub CloseExcel()
    wb.Application.Workbooks(1).Save
End Sub

It should work... post your entire code...

Darko

Here is it.... BTW, thanks for the help.

'--------------------------------------------------------------------------------------------------------------------------------------------
Dim lFileName As String
Dim strFileNameCheck
Dim FolderDrive As String
Dim strFolderPath As String

Private Type BrowseInfo
    hWndOwner As Long
    pIDLRoot As Long
    pszDisplayName As Long
    lpszTitle As Long
    ulFlags As Long
    lpfnCallback As Long
    lParam As Long
    iImage As Long
End Type
Const BIF_RETURNONLYFSDIRS = 1
Const MAX_PATH = 260
Private Declare Sub CoTaskMemFree Lib "ole32.dll" (ByVal hMem As Long)
Private Declare Function lstrcat Lib "kernel32" Alias "lstrcatA" (ByVal lpString1 As String, ByVal lpString2 As String) As Long
Private Declare Function SHBrowseForFolder Lib "shell32" (lpbi As BrowseInfo) As Long
Private Declare Function SHGetPathFromIDList Lib "shell32" (ByVal pidList As Long, ByVal lpBuffer As String) As Long


Dim strSavePath As String
Dim strFolderPath2 As String
Dim strRange As String
Dim strRow As String


Private Sub About_Click()
    Me.Move (Screen.Width - Me.Width) / 2, (Screen.Height - Me.Height) / 2
    frmAbout.Show
End Sub

Private Sub cmdDone_Click()
    Unload Me
End Sub

Public Sub cmdSaveAs_Click()
    CommonDialog1.ShowSave
   
    strSavePath = CommonDialog1.FileName
   
    Dim sName As String
    Dim wbk As Workbook
    Dim owbk As Workbook
   
    Set owbk = ActiveWorkbook
 
    sName = strSavePath
    Set wbk = Workbooks.Add
    wbk.SaveAs sName
   
    wbk.Close msoTrue
   
    cmdSaveAs.Visible = False
    cmdSelectPath.Visible = True
   
End Sub

Private Sub cmdSelectPath_Click()
       
    Call GetFolderInfo
    txtPath.Text = strFolderPath & "\"
   
    cmdGenerateList.Visible = True
    cmdSelectPath.Visible = False
   
End Sub
Private Sub GetFolderInfo()

    Dim iNull As Integer, lpIDList As Long, lResult As Long
    Dim sPath As String, udtBI As BrowseInfo

    With udtBI
       'Set the owner window
        .hWndOwner = Me.hWnd
        'lstrcat appends the two strings and returns the memory address
        .lpszTitle = lstrcat("C:\", "")
        'Return only if the user selected a directory
        .ulFlags = BIF_RETURNONLYFSDIRS
    End With

    'Show the 'Browse for folder' dialog
    lpIDList = SHBrowseForFolder(udtBI)
    If lpIDList Then
        sPath = String$(MAX_PATH, 0)
        'Get the path from the IDList
        SHGetPathFromIDList lpIDList, sPath
        'free the block of memory
        CoTaskMemFree lpIDList
        iNull = InStr(sPath, vbNullChar)
        If iNull Then
            sPath = Left$(sPath, iNull - 1)
        End If
    End If
    strFolderPath = sPath
   
End Sub

Private Sub Form_Load()
On Error GoTo ErrorHandler

Me.Move (Screen.Width - Me.Width) / 2, (Screen.Height - Me.Height) / 2
    frmMain.Height = 3355
    frmMain.Width = 9270
   
    cmdSaveAs.Visible = True
    cmdDone.Visible = False
    cmdGenerateList.Visible = False
    cmdSelectPath.Visible = False
   
   strRow = 1
   strRange = "A" & strRow

Exit Sub

ErrorHandler:

End Sub

Private Sub cmdGenerateList_Click()
   
    Call OpenExcel
    Call ListFolders(strFolderPath)
    Call CloseExcel

End Sub

Public Function OpenExcel()
    Dim xl As New Excel.Application
    Dim wb As Excel.Workbook
    Dim ws As Excel.Worksheet

    Set wb = xl.Workbooks.Open(strSavePath)
    Set ws = wb.Worksheets("Sheet1")
   
    Dim fso As Object, fld As Object, f As Object
    Dim NewF As Object
   
End Function

Public Sub WriteExcel()

    Debug.Print strRange
    Debug.Print strFolderPath2
   
    ws.Range(strRange).Value = strFolderPath2

End Sub

Private Sub CloseExcel()

    wb.Application.Workbooks(1).Save
   
    'cleanup
    wb.Close
    xl.Quit
   
    Set wb = Nothing
    Set xl = Nothing
   
End Sub

Sub ListFolders(strPath As String)
On Error Resume Next
   
    cmdGenerateList.Visible = False
   
'    Dim fso, fld, f As Object
'    Dim NewF
   
    Set NewF = f
    Set fso = CreateObject("Scripting.FileSystemObject")
    Set fld = fso.GetFolder(strPath)
     
    For Each f In fld.SubFolders
        ListFolders f.Path
        FolderDrive = f.Path
        strFolderPath2 = f.Path
   
        Call WriteExcel
   
        strRow = strRow + 1
        strRange = "A" & strRow
       
    Next
   
    cmdMoveFiles.Visible = False
    cmdDone.Visible = True
   
End Sub

Private Sub Command1_Click()
    Screen.MousePointer = vbHourglass
    Command1.Enabled = False

    Command1.Enabled = True
    Screen.MousePointer = vbDefault
End Sub

'--------------------------------------------------------------------------------------------------------------------------------------------

Well you didn't move the variable declarations to the top as I said I my post above :)

Darko

works now.... thanks for all the help.

You're welcome

Darko

Please accept my answer if it's working as it should

Darko

Sorry about that.  Have a good one.