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
Re: hlp: creating, editing & saving multiple workbooks
Welcome to VBForums :wave:
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:
Code:
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:
Code:
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?
