Results 1 to 2 of 2

Thread: hlp: creating, editing & saving multiple workbooks

Threaded View

  1. #1

    Thread Starter
    New Member
    Join Date
    Aug 2008
    Posts
    1

    Question hlp: creating, editing & saving multiple workbooks

    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!
    Code:
    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
    Last edited by no_idea; Aug 21st, 2008 at 10:16 AM.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width