-
November 7th, 2013, 06:09 AM
#1
Excel and VB 6
Hello code guru It is a program to merge all excel sheets to one sheet but I wrote this code on the base of VBA right now giving Activex error plzzzz check and conform me back
Code:
Private Sub Merge_Click()
Dim fso As New FileSystemObject
Dim fol As Folder
Dim i, j As Integer
Dim name1 As String
Fpath = Text1.Text
If Fpath = "" Then
MsgBox "Please select a path", vbExclamation
Else
Dim bookList As Workbook
Dim mergeObj As Object, dirObj As Object, filesObj As Object, everyObj As Object
Application.ScreenUpdating = False
Set mergeObj = CreateObject("Scripting.FileSystemObject")
Set dirObj = mergeObj.GetFolder(Text1.Text)
Set filesObj = dirObj.Files
Dim ThisWorkbook As New Excel.Workbook
For Each everyObj In filesObj
Set bookList = Workbooks.Open(everyObj)
Range("A2:IV" & Range("A65536").End(xlUp).Row).Copy
'Hear at the time of save the merged files it gives ActiveX error
'Please rectify the error
'I wrote this code with the ref.of VAB code
ThisWorkbook.Worksheets(1).Activate
Range("A65536").End(xlUp).Offset(1, 0).PasteSpecial
Application.CutCopyMode = False
bookList.Close
Next
End If
End Sub
Last edited by HanneSThEGreaT; November 7th, 2013 at 06:19 AM.
-
November 7th, 2013, 06:18 AM
#2
Re: Excel and VB 6
[ Moved to separate Thread ]
-
November 7th, 2013, 06:20 AM
#3
Multiple Excel files merged in to one sheet
I biginner to VB Devoloper and I got first project only confusion so please help me friends
This is a Excel merging program but it gives some Activex error plz anybody help me fast
this is my email :
Thank you friends
Merge.zip
Last edited by HanneSThEGreaT; November 7th, 2013 at 08:28 AM.
Reason: removed email
-
November 7th, 2013, 12:50 PM
#4
Re: Excel and VB 6
It would help if you told us what the error message is that you are getting. Some activeX error could mean almost anything.
It would also help if you clarify what you are coding in is it VBA [Excel] or is it in VB6?
Always use [code][/code] tags when posting code.
-
November 8th, 2013, 12:11 AM
#5
Re: Excel and VB 6
The Error message is : ActiveX component cannot be create object errorActualy it is a VBA code I modified to use in VB 6.0
is it able to modify like after merging it ask to user to save plz sir
That code is :
Code:
Private Sub Merge_Click()
Dim fso As New FileSystemObject
Dim fol As Folder
Dim i, j As Integer
Dim name1 As String
Fpath = Text1.Text
If Fpath = "" Then
MsgBox "Please select a path", vbExclamation
Else
Dim bookList As Workbook
Dim mergeObj As Object, dirObj As Object, filesObj As Object, everyObj As Object
Application.ScreenUpdating = False
Set mergeObj = CreateObject("Scripting.FileSystemObject")
Set dirObj = mergeObj.GetFolder(Text1.Text) 'this is the path of Excelfiles folder
Set filesObj = dirObj.Files
Dim ThisWorkbook As New Excel.Workbook
For Each everyObj In filesObj
Set bookList = Workbooks.Open(everyObj)
Range("A2:IV" & Range("A65536").End(xlUp).Row).Copy
'Hear at the time of save the merged files it gives ActiveX component cannot be create object error
'Please rectify the error
'I wrote this code with the ref.of VAB code
ThisWorkbook.Worksheets(1).Activate
Range("A65536").End(xlUp).Offset(1, 0).PasteSpecial
Application.CutCopyMode = False
bookList.Close
Next
End If
End Sub
Last edited by GremlinSA; November 8th, 2013 at 01:02 AM.
Reason: added code tags
-
November 8th, 2013, 12:55 AM
#6
Re: Excel and VB 6
I see that you dimmed a Var named ThisWorkbook but you did not set it to anything
I do not do coding with Excel so can't be of much help
Always use [code][/code] tags when posting code.
-
November 8th, 2013, 01:09 AM
#7
Re: Excel and VB 6
Code:
Dim bookList As Workbook
---------
Dim ThisWorkbook As New Excel.Workbook
---------
Set bookList = Workbooks.Open(everyObj)
---------
ThisWorkbook.Worksheets(1).Activate
You've initiated two different variables, set instance to one and trying to use the other to work with the workbook.. merge these two variables, and the error should be resolved...
Articles VB6 : Break the 2G limit - Animation 1, 2 VB.NET : 2005/8 : Moving Images , Animation 1 , 2 , 3 , User Controls
WPF Articles : 3D Animation 1 , 2 , 3
Code snips: VB6 Hex Edit, IP Chat, Copy Prot., Crop, Zoom : .NET IP Chat (V4), Adv. ContextMenus, click Hotspot, Scroll Controls
Find me in ASP.NET., VB6., VB.NET , Writing Articles, My Genealogy, Forum
All VS.NET: posts refer to VS.NET 2008 (Pro) unless otherwise stated.
-
November 8th, 2013, 01:45 AM
#8
Re: Excel and VB 6
Sir I changed but same error was showing. and also it is not asking user to save this please check sir
Code:
Private Sub Merge_Click()
Dim fso As New FileSystemObject
Dim fol As Folder
Dim i, j As Integer
Dim name1 As String
Fpath = Text1.Text
If Fpath = "" Then
MsgBox "Please select a path", vbExclamation
Else
Dim bookList As Workbook
Dim mergeObj As Object, dirObj As Object, filesObj As Object, everyObj As Object
Application.ScreenUpdating = False
Set mergeObj = CreateObject("Scripting.FileSystemObject")
Set dirObj = mergeObj.GetFolder(Text1.Text) 'this is the path of Excelfiles folder
Set filesObj = dirObj.Files
Dim ThisWorkbook As New Excel.Workbook
For Each everyObj In filesObj
Set bookList = Workbooks.Open(everyObj)
Range("A2:IV" & Range("A65536").End(xlUp).Row).Copy
'Hear at the time of save the merged files it gives ActiveX component cannot be create object error
'Please rectify the error
'I wrote this code with the ref.of VAB code
ThisWorkbook.Worksheets(1).Activate
Range("A65536").End(xlUp).Offset(1, 0).PasteSpecial
Application.CutCopyMode = False
bookList.Close
Next
ThisWorkbook.Worksheets(1).Close
ThisWorkbook.Save
End If
End Sub
Last edited by GremlinSA; November 8th, 2013 at 04:28 AM.
Reason: added code tags
-
November 8th, 2013, 04:32 AM
#9
Re: Excel and VB 6
Code:
Dim bookList As Workbook
--------
Dim ThisWorkbook As New Excel.Workbook
--------
Set bookList = Workbooks.Open(everyObj)
--------
ThisWorkbook.Worksheets(1).Activate
--------
ThisWorkbook.Worksheets(1).Close
ThisWorkbook.Save
you have not changed anything.... Use Either booklist or ThisWorkbook but NOT BOTH...
Also .... Save Before Close .. not close then save....
Articles VB6 : Break the 2G limit - Animation 1, 2 VB.NET : 2005/8 : Moving Images , Animation 1 , 2 , 3 , User Controls
WPF Articles : 3D Animation 1 , 2 , 3
Code snips: VB6 Hex Edit, IP Chat, Copy Prot., Crop, Zoom : .NET IP Chat (V4), Adv. ContextMenus, click Hotspot, Scroll Controls
Find me in ASP.NET., VB6., VB.NET , Writing Articles, My Genealogy, Forum
All VS.NET: posts refer to VS.NET 2008 (Pro) unless otherwise stated.
-
November 8th, 2013, 05:31 AM
#10
Re: Excel and VB 6
Hello sir,
In this program all the excel files are merging in one sheet but after merging all the source files are in red only form and also after merging all the source files are not closing at the same time excel gets struck.
Please rectify the errors
I am new to programming field so please ignore my mistakes
Code:
Private Sub Merge_Click()
Fpath = Text1.Text
If Fpath = "" Then
MsgBox "Please select a path", vbExclamation
Else
Dim bookList As Workbook
Dim mergeObj As Object, dirObj As Object, filesObj As Object, everyObj As Object
Application.ScreenUpdating = False
Set mergeObj = CreateObject("Scripting.FileSystemObject")
Set dirObj = mergeObj.GetFolder(Text1.Text) 'this is the path of Excelfiles folder
Set filesObj = dirObj.Files
Dim ThisWorkbook As New Excel.Workbook
For Each everyObj In filesObj
Set ThisWorkbook = Workbooks.Open(everyObj)
Range("A2:IV" & Range("A65536").End(xlUp).Row).Copy
'Hear at the time of save the merged files it gives ActiveX component cannot be create object error
'Please rectify the error 'I wrote this code with the ref.of VAB code
ThisWorkbook.Worksheets(1).Activate
Range("A65536").End(xlUp).Offset(1, 0).PasteSpecial
Application.CutCopyMode = False
Next
'ThisWorkbook.Worksheets(1).Close
cdc1.Filter = "Status File (*.xls, *.xlsx)|*.xls;*.xlsx"
cdc1.ShowSave
If cdc1.FileName <> "" Then
ThisWorkbook.SaveAs (cdc1.FileName)
MsgBox "Saved Successfully.", vbInformation, "Save Output"
ThisWorkbook.Close
Unload Me
End If
End If
End Sub
Last edited by HanneSThEGreaT; November 8th, 2013 at 06:18 AM.
Reason: code tags.
-
November 8th, 2013, 06:16 AM
#11
Re: Excel and VB 6
nil.kul007, please use [ CODE] tags when posting code. It makes the code nice to read.
-
November 8th, 2013, 06:33 AM
#12
Re: Excel and VB 6
sorry next time I am using
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
|