|
-
Aug 21st, 2008, 08:16 AM
#1
Thread Starter
New Member
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
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|