hlp: creating, editing & saving multiple workbooks

[no_idea]

hi,
im not very used to vb6 so i have some troubles to understand the syntax.

right now im getting freaked because of a code i whrote.
it should:
- create a excel file with the name of a cell from another excel file (which worx)
- copy content from another file into the new created one
- save the edited file again.

my current problems (see comment-syntax on the left in the code):
- the secound save- instruction wont work
- the last if-instruction seems to make trouble (i get an error if i try it the way i whrote it)

Please help me!

Sub create_owner_list()
    
    Dim rows As Integer
    Dim xlspath As String
    Dim name As String
    Dim path As String
            
    Dim J As Integer
    J = 0
    Dim K As Integer
    K = 0        
    
'##########################################
'#                                                                      #
'#    'Set amount of rows in ADS-Excelsheet                             #
      rows = 100
'#                                                                      #
'#    'Set path to dir where you want to create the lists               #
      path = "U:\test\"
'#                                                                      #
'#    'Set path to ADS Excel file                                       #
      xlspath = "U:\test\bla.xls"
'#                                                                      #
'########################################

    
    'ADS-Export einlesen
    Set adslist = CreateObject("Excel.Application")
    adslist.Workbooks.Open (xlspath)
    
    Set ExcelProzess = CreateObject("Excel.application")    
    
    For I = 2 To rows
    
        If adslist.Range("B" & I).Value <> "" Then
           
            'Owner name is taken
            name = adslist.Range("B" & I) & ".xls"
            
            'Check if File already exists
            If Dir(path & name) = Empty Then
            
                'Create & open a new excel File for every owner with the owner name
                Set ExcelDatei = ExcelProzess.Workbooks.Add()
                ActiveWorkbook.SaveAs Filename:=path & name, FileFormat:=xlNormal, Password:="", WriteResPassword:="", ReadOnlyRecommended:=False, CreateBackup:=False

                'for every new file set K = 0 because of the line counter for the new Excel doc
                K = 0
                                                
            Else
                                 
            End If
            
'            ExcelProzess.Workbooks.Open (path & name)
            
            'Loop for copying the group- and usernames in the new excel file
            For L = I + 1 To rows
 
                'Exit if the group has no more Users
                If adslist.Range("E" & L) = "" Then
'                   ExcelProzess.Workbooks.Save
'                   ExcelDatei.SaveAs (path & name)
'                   ActiveWorkbook.Close savechanges:=True
                    Exit For
                        
                Else
'                    If ExcelDatei.Sheets("Tabelle1").Range("A" & K) <> "" Then
                        K = K + 1
                    
'                    Else
                        '>>>>>Copy process<<<<<
                        'copy the group name in the new excel file
                        ExcelDatei.Sheets("Tabelle1").Range("A" & K).Value = adslist.Range("A" & I).Value
                        'copy the user name in the new excel file
                        ExcelDatei.Sheets("Tabelle1").Range("E" & K).Value = adslist.Range("E" & L).Value
                        
'                   End If
                  
                End If
                                                    
            Next L
                         
        Else
                         
        End If
                
    Next I
    

'ExcelProzess.Quit
'adslist.Quit
    
End Sub

[si_the_geek]

Welcome to VBForums

Are you actually using VB6 (a completely separate program), or VBA (the "VB Editor" inside Excel)?

If you are using VB6, ActiveWorkbook is guaranteed to cause issues - and it isn't entirely safe inside VBA either. It is a much better idea to use the object variables you have instead, eg:

ExcelDatei.SaveAs Filename:=path & name, ...

I don't understand why you have created two instances of Excel (adslist and ExcelProzess), as one is enough - you can have as many workbooks as you like in each instance.

You aren't closing either instance or the workbooks inside them, which can lead to several problems (like wasting memory, strange messages when the computer is shut down, etc). See my Excel Tutorial (link in my signature) for ways to do it.


Note that you do not have to have an "Else" with each If, so when you aren't actually using it, you shouldn't really have it in the code.

The keyword Empty is not what you should be using, as it is not the same thing as an empty string.. it might work for you, but it isn't reliable (it might fail if run on a different computer). Instead specify an empty string, eg:

            If Dir(path & name) = "" Then

Unfortunately is isn't clear where you are having problems (as there is so much code commented out), or what the actual problems are. Can you show us exactly which lines you mean, and tell us what the issues/errors are?