Excel Automation
I have an EXE file that generates an Excel Workbook in Excel 2000
This EXE works well with one WorkBook
I use classic Reference
Set xlsRemittance = CreateObject("Excel.Application")
The problem is when the system Generates at same time more than 1 WorkBook, Excel 2000 close ALL workbooks when i invoke .Quit
Is there any configuration to close only the Excel Window i need because with CreateObject Excel.EXE is creating consuming memory, and if i only use
ActiveWorkBook.Close EXCEL.exe is still working, so i can not use .Quit because the others workbook are still working, or can you help me with a code that identifies if another workbook is still working
View Complete Forum Thread with Replies
See Related Forum Messages: Follow the Links Below to View Complete Thread
Vb.net And Excel Automation. Excel Prcess Still Running In Task Manager
I posted this in the VB.NET forum also. All though I thought some Excel expertise might be helpful. According to MS Knowledge base I must release every excel com object that I create. For example If I only used the line
Code:
WB = Worbooks.Open(OpenPath)
a reference to a Workbooks object was created with out me explicityly doing it in code. So I changed the code to create a Workbooks object. then Changed the above line to the following.
Code:
WBs = XL.Workbooks
WB = WBs.Open(OpenPath)
So my question is what other ojects are created when I use this line with out me doing so in code.
Code:
rng = WS.Range("A65536").End(XlDirection.xlUp)
See Full Code below
'TEXT FROM POST IN .NET FORUM
This is very Frustrating. I am re-writting some code to open an Excel file, do some stuff and close the file.
Excel will not shut down if I use this line
Code:
rng = WS.Range("A65536").End(XlDirection.xlUp)
it will how ever close down if I use this line.
Code:
rng = WS.Range("A1")
What is going on? My code is below.
Code:
Sub Verispan(OpenPath as string)
Dim XL As Excel.Application
Dim WBs As Excel.Workbooks
Dim WB As Excel.Workbook
Dim WS As Excel.Worksheet
Dim rng As Excel.Range
Dim lrow As Integer
If XL Is Nothing Then XL = New Excel.Application
Try
'START EXCEL
WBs = XL.Workbooks
WB = WBs.Open(OpenPath)
WS = WB.ActiveSheet
WB.Activate()
WS.Select()
XL.ScreenUpdating = False
XL.Visible = True
'Find the last row, and the start range for the data
'rng = WS.Range("A1")
rng = WS.Range("A65536").End(XlDirection.xlUp)
'lrow = rng.Row
'Close Down Excel. Must Remove all references to COM objects that are created,
'or Excel will remain as running process
Call ReleaseCom(rng)
Call ReleaseCom(WS)
WB.Close(False)
Call ReleaseCom(WB)
Call ReleaseCom(WBs)
XL.Quit()
Call ReleaseCom(XL)
GC.Collect()
Catch ex As Exception
MessageBox.Show("Error in ScrubDataFiles Module, VerispanProcedure" & Chr(10) & ex.Message)
End Try
Code:
Sub ReleaseCom(ByVal o As Object)
Try
If Not o Is Nothing Then System.Runtime.InteropServices.Marshal.ReleaseComObject(o)
Catch ex As Exception
MessageBox.Show(ex.Message)
Finally
If Not o Is Nothing Then o = Nothing
End Try
End Sub
Excel Automation Help...Involves Multiple Excel Files
Hi
First of all I am new to Excel programming..............But I can learn
Here is the description of the problem i have
There are about 10 excel sheets...
These sheets contains some daily updates(sheets updated manually)
Every week i create a new excel sheet which is based on the information contained in those 10 sheets..
What I have to do is to automate the process of generating the weekly excel sheet..This weekly sheet can be updated every time a daily sheet is updated and saved...NOT all the information from the daily sheet needs to be entered into the weekly sheet...Just some data based on certain conditions..
I beleive this is possible using Macros....I have explained the problem but i don't know how to proceed ...I beleive some one will help me with this...
Presently This is what I do:
I open one of the daily sheets...Enter the required data into the newly created (if doesn't already exist) weekly excel sheet(a new excel file...)
So this involves two diffrent excel sheets(files) and i beleive recording this as a macro is not possible...
I don't know the exact macro programming but i am good at VB so i beleive I can learn fast...
This is the idea I have..
Whenever a daily sheet is updated and saved...Update the weekly sheet(file)(Programatically)
This will involve the following
Whenever a daily sheet is saved
open the weekly update sheet using macros..
Then copy the required cells from the daily sheet to the weekly sheet..And save the weekly sheet..
I beleive this can be achived using macros...
This is the theory I have in mind...I have no idea how sound it is..I beleive I can use your expertise..
Thanx
Can't Close Excel Instance After Excel Automation
I hope I'm posting this in the right forum, I've never really used on of these forums before. I have the following code that I am using to move date from MS Access to MS Excel. The code itself work flawlessly except the after closing there is still an instance of Excel hanging around in my task manager. I believe the problem is related to the fact that the row I activated to use the .freezePanes is still activated when I close the application. If I remove the adding of the image and the freezePane there is no Excel instances left. If I put them back in then it hangs there. I've scoured the net for about a week looking for my error and I've yet to find it. Any suggestions would be appriciation.
Code Starts here:
Sub ecMAP()
On Error GoTo ErrHandler
' Excel object variables
Dim appExcel As Excel.Application
Dim wbk As Excel.Workbook
Dim wks As Excel.Worksheet
Dim sOutput As String
Dim dbs As DAO.Database
Dim rst As DAO.Recordset
Dim sSQL As String
Dim lRecords As Long
Dim iRow As Integer
Dim iCol As Integer
Dim iFld As Integer
Dim highLight As Boolean
Dim sheetsPerBook As Integer
'CONSTANTS
Const aTab As Byte = 1
Const aStartRow As Byte = 6
Const aStartColumn As Byte = 1
' set to break on all errors
Application.SetOption "Error Trapping", 0
' GENERATING OUTPUT FILE NAME
If formdate("S", 8) = formdate("E", 8) Then
sOutput = "S:HWYREPORTSCOLAccountsMMarianiMariani Accessorials " & Format(fdate(formdate("S", 8)), "mm-dd-yy") & ".xls"
Else
sOutput = "S:HWYREPORTSCOLAccountsMMarianiMariani Accessorials " & Format(fdate(formdate("S", 8)), "mm-dd-yy") & " through " & Format(fdate(formdate("E", 8)), "mm-dd-yy") & ".xls"
End If
If Dir(sOutput) <> "" Then Kill sOutput
' Create the Excel Applicaiton, Workbook and Worksheet and Database object
Set appExcel = New Excel.Application
sheetsPerBook = appExcel.SheetsInNewWorkbook
appExcel.SheetsInNewWorkbook = 1
Set wbk = appExcel.Workbooks.Add
appExcel.SheetsInNewWorkbook = sheetsPerBook
Set wks = wbk.Worksheets(aTab)
Set dbs = CurrentDb
sSQL = "select * from MAPqryExport"
Set rst = dbs.OpenRecordset(sSQL, dbOpenSnapshot)
'ADDING LOGO TO EXCEL FILE
wks.Pictures.Insert("S:HWYREPORTSLibrariesLogosmariani.GIF").Select
Selection.ShapeRange.Height = 49.5
Selection.ShapeRange.Width = 235.5
With Selection
.Placement = xlFreeFloating
.PrintObject = True
End With
wks.Rows("6").Activate
ActiveWindow.FreezePanes = True
' ADDING COLUMN HEADERS TO EXCEL FILE
With wks
iCol = aStartColumn
iRow = (aStartRow - 1)
If Not rst.BOF Then rst.MoveFirst
iFld = 0
lRecords = lRecords + 1
For iCol = aStartColumn To aStartColumn + (rst.Fields.Count - 1)
wks.Cells(iRow, iCol) = rst.Fields(iFld).Name
wks.Cells(iRow, iCol).Interior.ColorIndex = 1
wks.Cells(iRow, iCol).Font.ColorIndex = 2
wks.Cells(iRow, iCol).Font.Bold = True
iFld = iFld + 1
Next
iRow = iRow + 1
rst.MoveNext
End With
' ADDING INFO TO EXCEL FILE
iCol = aStartColumn
iRow = aStartRow
highLight = False
With wks
If Not rst.BOF Then rst.MoveFirst
Do Until rst.EOF
iFld = 0
lRecords = lRecords + 1
For iCol = aStartColumn To aStartColumn + (rst.Fields.Count - 1)
wks.Cells(iRow, iCol) = rst.Fields(iFld)
wks.Cells(iRow, iCol).NumberFormat = "$0.00"
'If highLight = True Then
'wks.Cells(iRow, iCol).Interior.ColorIndex = 35
'End If
iFld = iFld + 1
Next
iRow = iRow + 1
rst.MoveNext
'If highLight = False Then
'highLight = True
'Else
'highLight = False
'End If
Loop
End With
'ADDING TOTALS
Dim columnCount As Integer
columnCount = 3 'starting column for totals
With wks
wks.Cells(aStartRow + rst.RecordCount + 1, aStartColumn + 1) = "Totals:"
wks.Cells(aStartRow + rst.RecordCount + 1, aStartColumn + 1).Font.Bold = True
wks.Cells(aStartRow + rst.RecordCount + 1, aStartColumn + 1).Font.ColorIndex = 2
wks.Cells(aStartRow + rst.RecordCount + 1, aStartColumn + 1).Interior.ColorIndex = 1
Do While columnCount <= 10
wks.Cells(aStartRow + rst.RecordCount + 1, columnCount).Formula = "=SUM(R[-" & rst.RecordCount + 1 & "]C:R[-1]C)"
wks.Cells(aStartRow + rst.RecordCount + 1, columnCount).Font.Bold = True
wks.Cells(aStartRow + rst.RecordCount + 1, columnCount).Font.ColorIndex = 2
wks.Cells(aStartRow + rst.RecordCount + 1, columnCount).Interior.ColorIndex = 1
columnCount = columnCount + 1
Loop
End With
'AUTOFITTING COLUMNS
With wks
wks.Columns("A:A").EntireColumn.AutoFit
wks.Columns("B:B").EntireColumn.AutoFit
wks.Columns("C:C").EntireColumn.AutoFit
wks.Columns("D:D").EntireColumn.AutoFit
wks.Columns("E:E").EntireColumn.AutoFit
wks.Columns("F:F").EntireColumn.AutoFit
wks.Columns("G:G").EntireColumn.AutoFit
wks.Columns("H:H").EntireColumn.AutoFit
wks.Columns("I:I").EntireColumn.AutoFit
wks.Columns("J:J").EntireColumn.AutoFit
wks.Columns("K:K").EntireColumn.AutoFit
wks.Columns("L:L").EntireColumn.AutoFit
wks.Columns("M:M").EntireColumn.AutoFit
wks.Columns("N:N").EntireColumn.AutoFit
wks.Columns("O:O").EntireColumn.AutoFit
wks.Columns("P:P").EntireColumn.AutoFit
wks.Columns("Q:Q").EntireColumn.AutoFit
wks.Columns("R:R").EntireColumn.AutoFit
End With
With wbk
'NAMING TAB
wks.Select
wks.Name = "Mariani Accessorials"
End With
With wks
.PageSetup.Zoom = False
.PageSetup.CenterHeader = "Mariani Accessorial Report"
.PageSetup.CenterFooter = "Page &p"
End With
'CLOSING AND SAVING NEW FILES
Set wks = Nothing
wbk.SaveAs FileName:=sOutput, FileFormat:= _
xlNormal, Password:="", WriteResPassword:="", ReadOnlyRecommended:=False _
, CreateBackup:=False
wbk.Close SaveChanges:=False
Set wbk = Nothing
appExcel.Quit
Set appExcel = Nothing
'Call AutoEmailAll("SPNEM - tblDistList", "Attached is the SP News Exception Report. If the report is blank, there were no exceptions entered.", "SP News Exception Memo Report", sOutput)
ExitProcedure:
Exit Sub
ErrHandler:
Select Case Err.Number
Case Else
Call UnexpectedError(Err.Number, "ecSPNEM: " _
& Err.Description, Err.Source, _
Err.HelpFile, Err.HelpContext)
Resume ExitProcedure
Resume
End Select
End Sub
Excel Automation Keep Excel In Front
Hi,
When I start Excel by another application like Delphi, is it possible to keep excel (forms) in front of other windows so that the users can not loose the form focus?
Thanks
Excel Automation
can anyone tell me how to save an excel workbook so that I can open it with readwrite privilages, but only allow readonly access to others. I have tried the following code but get the error, file is write reserved, when trying to save the file for the second time. Thanks for the help.
'save the file
ExcelApp.Workbooks(1).SaveAs(strFileName, , , "sa", True, False, Excel.XlSaveAsAccessMode.xlShared, , , , )
'open the file
ExcelApp.Workbooks.Open(strFileName, , False, , , "sa", True, , , True, , , )
VB In Excel Regarding Automation
hi all, what i have in my hands is that i have to automate the calculation of stuffs like the mean and range of a specific spreadsheet.
i have the formulas but i just dunno what are the codes that allows the automation
which means the scenario is to create a button "Calculate Mean". and just by selecting any column and click on the button the output will be displayed either on a cell or anywhere.
pls help me, my life is on the line. . pls try to help me try automate the calculation of the mean of any row. it must be automated.
Excel Automation From Within VB 6 HELP
I have an application which uses excel automation to create a pretty form in which to present the data that the application has acquired.
I can not really discern any real pattern, but approximately 50% of the time, it crashes on one line with the following error code/messsage:
1004
"Unable to set the linestyle property of the border class."
At this point in my code, I am drawing boxes around my data members.
The way in which I do this is by selecting the group of cells in question, then handling the borders.
I hate using the select method, but I am not sure there is another effective way of doing what I want to do.
I generally cannot deal with absolute cell names, because the number and dimensions of the data are determined during run-time.
I guess what I want to know is if anyone sees a glaring problem that I missed, or maybe knows a more elegant way of doing what I want to do.
Here is the code in question:
Code:
With xlsheet
'select the temp values area
.range(.Cells(lintGridStart, lintMainStartColumn + 2), (.Cells(lintGridStart, lintMainStartColumn + 1 + Caltempnum))).Select
With xlApp.Selection.Borders(xlInsideVertical)
.LineStyle = xlContinuous 'this is the line where it crashes!!!!
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
'lots more suff
end with
If necessary, of course, I can post more code. Any help availible would be greatly appreciated.
Excel Automation
Hi all
i just want VB 6.0 code for comparing two excel work sheet and create a new excel sheet which contains all the differences in the first 2 sheet.
Could you please help me??
Using Excel Automation In VB.net
I have produced a text box that when you put a number in transfers i to a cell on the spreadsheet i then have 2 columns as listed below :
Column A contains a stock level
Column B contains an equation that is the cell on the spreadsheet - the stock from column a what I need to do, is transfer the values from column B only to column a.
Have got as far as copying column B, but unlike in VBA VB.NET does not recognise the pastespecial values only command is there a special command in VB.net to do this ?
If anyone can help me out that would be great thanks
HELP!!! Excel Automation
Hi! I am working on an application that among other things needs to use an excel workbook to query another excel workbook. I have been trying to use QueryTables to do it.
I have been getting a error message that tells me "Run-time error '438' object doesn't support this property or method". But when I record a Macro directly in excel doing the query it works, I even have tried copying the code from excel to VB. The code I have is as follows:
Code:
With ActiveSheet.QueryTables.Add(Connection:=Array(Array( _
"ODBC;DSN=Excel Files;DBQ=c:WINDOWSDesktopDatabaseInquiry listing.xls;DefaultDir=c:WINDOWSDesktopDatabase;DriverId=22;MaxBuffe" _
), Array("rSize=2048;PageTimeout=5;")), Destination:=Range("A1"))
.CommandText = Array( _
"SELECT `newStudents$TABLE`.ID, `newStudents$TABLE`.`Created On`, `newStudents$TABLE`.Subject, `newStudents$TABLE`.Descr, `newStudents$TABLE`.`First Name`, `newStudents$TABLE`.`Last Name`, `newStudents" _
, _
"$TABLE`.`Address 1`, `newStudents$TABLE`.`Address 2`, `newStudents$TABLE`.`Address 3`, `newStudents$TABLE`.`Address 4`, `newStudents$TABLE`.City, `newStudents$TABLE`.St, `newStudents$TABLE`.Postal, `n" _
, _
"ewStudents$TABLE`.Cntry, `newStudents$TABLE`.Descr1, `newStudents$TABLE`.Email, `newStudents$TABLE`.`Refrl Srce`" & Chr(13) & "" & Chr(10) & "FROM `c:WINDOWSDesktopDatabaseInquiry listing`.`newStudents$TABLE` `newStudents$TA" _
, _
"BLE`" & Chr(13) & "" & Chr(10) & "WHERE (`newStudents$TABLE`.Descr='Accounting') OR (`newStudents$TABLE`.Descr='Business Administration')" & Chr(13) & "" & Chr(10) & "ORDER BY `newStudents$TABLE`.`Last Name`" _
)
.Name = "Query from Excel Files_3"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = True
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.PreserveColumnInfo = True
.Refresh BackgroundQuery:=False
End With
Sorry for the mess. Please help!!!!!!!
Excel Automation
Hello,
I am working on automating the workings of an Excel workbook.
In the Workbook Open event I call subs that pull data, plot charts, format the output and publish to the web. All of the above is done and it works.
Now, I want to have all of the above executed - say hourly - and have the file close upon completion of the above tasks without user intervention.
I can programatically close the workbook (ActiveWindow.Close).
My concern is regarding opening the VBA code that does it all. Since I will be doing all of the above (including the call to close the workbook) in the Workbook.Open event how will I be able to access the code later?
In other words, how do I access the VBA code for a workbook that's not open?
Any ideas?
Excel Automation Using Vb.Net
Hi ,
I am a newcomer sulf study to real VB.Net Programming and I try to creat an excel sheet using this code which is derived from Microsoft site
and I get this Error on compiling the line (owb=oxl.workbooks.add)
Error:(System.Runtime.InteropServices.COMException)
I added the Excel library to the referance , and I really ge confused as I coudn't complete the job and continue studying VB
Will be Geatful if you helped me. And Please try to simplify the matter as I am a new comer
Thanks
Aly
Code:
Imports office = Microsoft.office.core
Class Form1
Inherits System.Windows.Forms.Form
Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
Dim oXL As Excel.Application
Dim oWB As Excel.Workbook
Dim oSheet As Excel.Worksheet
Dim oRng As Excel.Range
' Start Excel and get Application object.
oXL = CreateObject("Excel.Application")
oXL.Visible = True
' Get a new workbook.
oWB = oXL.Workbooks.Add
oSheet = oWB.ActiveSheet
' Add table headers going cell by cell.
oSheet.Cells(1, 1).Value = "First Name"
oSheet.Cells(1, 2).Value = "Last Name"
oSheet.Cells(1, 3).Value = "Full Name"
oSheet.Cells(1, 4).Value = "Salary"
' Format A1:D1 as bold, vertical alignment = center.
With oSheet.Range("A1", "D1")
.Font.Bold = True
.VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
End With
' Create an array to set multiple values at once.
Dim saNames(5, 2) As String
saNames(0, 0) = "John"
saNames(0, 1) = "Smith"
saNames(1, 0) = "Tom"
saNames(1, 1) = "Brown"
saNames(2, 0) = "Sue"
saNames(2, 1) = "Thomas"
saNames(3, 0) = "Jane"
saNames(3, 1) = "Jones"
saNames(4, 0) = "Adam"
saNames(4, 1) = "Johnson"
End Sub
End Class
Edit by italkid: Please use the [vb]][ and [/vb] tags to display your code, thanks.
Excel Ole Automation In VB
I want to show Excel file through the OLE Automation in VB. this is excel file should show a Chart which is feeded by variable data . I mean i have to pass the input data to the excel file to show the chart any time i open that form or report. i haven't done it before and i don't know anything. can anyone tell me how i should start it.
Thanks
Help With Excel Automation
My code is so slowly, so I guess I need help with the hole code I record from excel. Can someone help me to speed up this code and correct all misstakes I have made. I run this code from my VB project.
VB Code:
Option Explicit Private Const xlColumns = 2Private Const xlColumnStacked = 52Dim objGraph As Graph.Chart Function IsNothing(Obj As Object) As BooleanOn Error Resume Next IsNothing = True Obj.Name = Obj.Name 'Works even if Object doesn't have a name property If Err.Number = 91 Then IsNothing = True End Function Function IsExcelRunning() As Boolean Dim xlApp As Excel.Application On Error Resume Next Set xlApp = GetObject(, "Excel.Application") IsExcelRunning = (Err.Number = 0) Set xlApp = Nothing Err.ClearEnd Function Private Function GetExcel() As Excel.Application On Error Resume Next Set GetExcel = GetObject(, "excel.application") If Err.Number <> 0 Then Set GetExcel = New Excel.ApplicationEnd Function Function OpenXLChange() Dim db As DAO.Database Dim RS As DAO.Recordset Dim SQL, PATH As String Dim xlApp As Excel.Application Dim xlBook As Excel.Workbook Dim ExcelRunning As Boolean Dim objGraph As Object Set objGraph = frmForm1_Excel.OLE1.object ' I included a chart ExcelRunning = IsExcelRunning()If ExcelRunning = False ThenSet xlApp = CreateObject("Excel.application")ElseSet xlApp = GetExcelEnd If If IsNothing(xlApp) Then Set xlApp = New Excel.Application PATH = "C:" Set xlBook = xlApp.Workbooks.Open(PATH & MyXl & ".xls") xlBook.Application.Visible = False xlApp.ScreenUpdating = False If frmForm1_Excel.ChkChart = vbChecked Then With xlApp.Sheets.Add(After:=Sheets(1)) xlApp.Sheets(2).Name = "Graf" End With End If xlApp.Sheets(1).Select With xlApp.Workbooks(1).BuiltinDocumentProperties.Item("Author").Value = "AuthorName".Item("Manager").Value = "ManagerName".Item("Title").Value = "TitleText".Item("Company").Value = "TheCompany".Item("Subject").Value = "SubjectText".Item("Comments").Value = "Comments"End With xlApp.ActiveSheet.PageSetup.PrintArea = "" With xlApp.ActiveSheet.PageSetup .PrintTitleRows = "$1:$1" .PrintTitleColumns = "" .LeftHeader = "" .CenterHeader = "TestText" .RightHeader = "&""Arial,Regular""&8 Sidan &P av &N" .LeftFooter = "&""Arial,Regular""&8 TestNr2/&F/TestNr4/&D" .CenterFooter = "" .RightFooter = "&""Times New Roman,Normal""&3Automatic Created" .LeftMargin = xlApp.Application.InchesToPoints(0) .RightMargin = xlApp.Application.InchesToPoints(0) .TopMargin = xlApp.Application.InchesToPoints(0.984251969) .BottomMargin = xlApp.Application.InchesToPoints(0.984251969) .HeaderMargin = xlApp.Application.InchesToPoints(0.5) .FooterMargin = xlApp.Application.InchesToPoints(0.5) .PrintHeadings = False .PrintGridlines = False .PrintComments = xlPrintNoComments .CenterHorizontally = False .CenterVertically = False .Orientation = xlLandscape .Draft = False .PaperSize = xlPaperA4 .FirstPageNumber = xlAutomatic .Order = xlDownThenOver .BlackAndWhite = False .Zoom = 100 End With xlApp.Range("A1:S1").Select With xlApp.Selection.Interior .ColorIndex = 15 .Pattern = xlSolid End With xlApp.Cells.Select With xlApp.Selection.Font .Name = "MS Sans Serif" .Size = 8.5 .Strikethrough = False .Superscript = False .Subscript = False .OutlineFont = False .Shadow = False .Underline = xlUnderlineStyleNone .ColorIndex = xlAutomatic End With xlApp.Selection.Borders(xlDiagonalDown).LineStyle = xlNone xlApp.Selection.Borders(xlDiagonalUp).LineStyle = xlNone With xlApp.Selection.Borders(xlEdgeLeft) .LineStyle = xlContinuous .Weight = xlThin .ColorIndex = 15 End With With xlApp.Selection.Borders(xlEdgeTop) .LineStyle = xlContinuous .Weight = xlThin .ColorIndex = 15 End With With xlApp.Selection.Borders(xlEdgeBottom) .LineStyle = xlDouble .Weight = xlThin .ColorIndex = 15 End With With xlApp.Selection.Borders(xlEdgeRight) .LineStyle = xlContinuous .Weight = xlThin .ColorIndex = 15 End With With xlApp.Selection.Borders(xlInsideVertical) .LineStyle = xlContinuous .Weight = xlThin .ColorIndex = 15 End With With xlApp.Selection.Borders(xlInsideHorizontal) .LineStyle = xlContinuous .Weight = xlThin .ColorIndex = 15 End With xlApp.Selection.Borders(xlDiagonalDown).LineStyle = xlNone xlApp.Selection.Borders(xlDiagonalUp).LineStyle = xlNone xlApp.Rows("1:1").Select With Selection .HorizontalAlignment = xlCenter .VerticalAlignment = xlBottom .WrapText = False .Orientation = 0 .AddIndent = False .ShrinkToFit = False .MergeCells = False End With xlApp.Selection.Borders(xlDiagonalDown).LineStyle = xlNone xlApp.Selection.Borders(xlDiagonalUp).LineStyle = xlNone With xlApp.Selection.Borders(xlEdgeLeft) .LineStyle = xlContinuous .Weight = xlThin .ColorIndex = xlAutomatic End With With xlApp.Selection.Borders(xlEdgeTop) .LineStyle = xlContinuous .Weight = xlThin .ColorIndex = xlAutomatic End With With xlApp.Selection.Borders(xlEdgeBottom) .LineStyle = xlDouble .Weight = xlThick .ColorIndex = xlAutomatic End With With xlApp.Selection.Borders(xlEdgeRight) .LineStyle = xlContinuous .Weight = xlThin .ColorIndex = xlAutomatic End With With xlApp.Selection.Borders(xlInsideVertical) .LineStyle = xlContinuous .Weight = xlThin .ColorIndex = xlAutomatic End With xlApp.Columns("D:E").Select xlApp.Selection.NumberFormat = "yyyy-mm-dd" xlApp.Columns("H:S").Select xlApp.Selection.AutoFilter xlApp.Cells.Select xlApp.Cells.EntireColumn.AutoFit xlApp.ActiveWindow.View = xlPageBreakPreview xlApp.ActiveSheet.VPageBreaks(1).DragOff Direction:=xlToRight, RegionIndex:=1 xlApp.ActiveWindow.View = xlNormalView xlApp.Range("A1").Select SQL = "Mys SQL statement" Set db = OpenDatabase(GetTmpPath & "TestMDB.MDB", False, True, ";pwd=******") Set RS = db.OpenRecordset(SQL, dbOpenDynaset) While Not RS.EOF With xlApp.Range("A" & RS.AbsolutePosition + 2).AddComment .Visible = False .Text Text:=RTrim(RS.FIELDS("Name").Value) & Chr(10) & _ "Faxnr: " & RTrim(RS.FIELDS("FaxNumber")) .Shape.ScaleWidth 1.63, False .Shape.ScaleHeight 1.38, False End With RS.MoveNext Wend RS.Close If frmForm1_Excel.ChkChart = vbChecked Then xlApp.Sheets("Graf").Select objGraph.ChartArea.Copy xlApp.Cells.Select xlApp.ActiveSheet.Paste xlApp.ActiveSheet.Shapes("Object 1").Select ' Here is the copied chart xlApp.Selection.ShapeRange.ScaleWidth 0.88, False With xlApp.ActiveSheet.PageSetup .PrintTitleRows = "$1:$1" .PrintTitleColumns = "" .LeftHeader = "" .CenterHeader = "TestText" .RightHeader = "&""Arial,Regular""&8 Sidan &P av &N" .LeftFooter = "&""Arial,Regular""&8 TestNr2/&F/TestNr4/&D" .CenterFooter = "" .RightFooter = "&""Times New Roman,Normal""&3Automatic Created" .LeftMargin = xlApp.Application.InchesToPoints(0) .RightMargin = xlApp.Application.InchesToPoints(0) .TopMargin = xlApp.Application.InchesToPoints(0.984251969) .BottomMargin = xlApp.Application.InchesToPoints(0.984251969) .HeaderMargin = xlApp.Application.InchesToPoints(0.5) .FooterMargin = xlApp.Application.InchesToPoints(0.5) .PrintHeadings = False .PrintGridlines = False .PrintComments = xlPrintNoComments .CenterHorizontally = False .CenterVertically = False .Orientation = xlLandscape .Draft = False .PaperSize = xlPaperA4 .FirstPageNumber = xlAutomatic .Order = xlDownThenOver .BlackAndWhite = False .Zoom = 100 End With Set objGraph = Nothing End If xlBook.SavexlBook.Close xlApp.Quit Set xlBook = Nothing Set xlApp = Nothing Set db = Nothing Set RS = Nothing End Function
Excel Automation
I'm facing a serious problem with Excel Automation..
COuld someone please take a look here:
http://www.galahtech.com/forums/show...&threadid=2868
Excel Automation
Could someone please take a look at this:]
http://www.galahtech.com/forums/show...&threadid=2868
Thank you!
Excel Automation
Hi all,
I got some problem with generating an Excel file thru my vb application.
I am using Microsoft Excel 5.0 Object Library in my project. The project is working fine on my PC where Excel 2000 is loaded. But it gives me error on cleint PC where Excel 5.0 is installed. Error message received is: Run time erro '1005'. Unable to set the Value property of the Range class. I don't know where I am going wrong. Following is my code.
VB Code:
Dim OutFile As excel.Workbook, PriceSheet As excel.Worksheet Dim xRow As Integer Set OutFile = Workbooks.Add Set PriceSheet = OutFile.ActiveSheet PriceSheet.Name = lst_comm.Text & "-" & lst_type.Text PriceSheet.Range("a1") = "Date" PriceSheet.Range("a1").Font.Bold = True PriceSheet.Range("b1") = "Price" PriceSheet.Range("b1").Font.Bold = True xRow = 2 With rsrep If .EOF And .BOF Then MsgBox "No record found for the given date range", vbInformation dtp_from.SetFocus Exit Sub End If .MoveLast .MoveFirst Do While Not .EOF PriceSheet.Cells(xRow, 1) = !Date PriceSheet.Cells(xRow, 1).NumberFormat = "dd/mm/yyyy" PriceSheet.Cells(xRow, 2).NumberFormat = "#0.00" PriceSheet.Cells(xRow, 2) = rsrep(lst_type) If Not .EOF Then .MoveNext xRow = xRow + 1 Loop End With OutFile.SaveAs txt_out OutFile.Close Set OutFile = NothingEnd Sub
pls guide.
regards,
prakash
Excel Automation
Does anyone out there know if one is able to use a variable to provide arguments for the excel '=sum(e313)' function. For example: for my application the sum will not always be from cell e3 to cell p3, so I want to define 2 string variables as in:
Dim range1 as string
dim range2 as string
range1 = "e3"
range2= "p3"
result = "r3"
objexcel.range(result).formula="=sum(" & range1 & ":" & range2 & ")"
All I get from running this code is a type mismatch error "13"
Any suggestions would be greatly appreciated, Thanks in advance!
Excel Automation
Hi
I want to generate a report in Excel based on data retrieved from a DB. I am using the excel object from within VB to setup the report in excel, but I seem to have trouble with generating charts in excel from within VB. Constantly throws errors and complains abut the method parameters.
Please help. All suggestions welcome
thanks
Excel Automation Via VB6
I have a vb application that runs Excel(macro) invisible to the user.
When I try and quit the excel app I get a excel dialogue box prompting whether I want to "Save Changes" at this stage I have already saved my work.
Can anybody tell me how to get rid of this Dialogue?
OLE Automation And Excel... Please
Hi,
I'm new to OLE Automation. Can anyone show me how to:
1. Open an existing Excel file
2. Insert an empty row
3. Enter data in that row from my VB app.
4. Save the modified excel file
As always, thanks
Excel Automation
I created a command button in VB6. In the click event I put the following code.
Dim objExcel As Object
Set objExcel = GetObject("C:Dave.xls")
objExcel.Application.Cells(1,1).Value = 7
((help))
objExcel.Application.Quit
Set objExcel = Nothing
On the line that I wrote ((help)) I have been trying to find some code to save
the file Dave.xls.
My goal is to find some code to put in the command button such that when pressed
it opens an Excel spreadsheet, writes 7 to cell 1,1, saves the file, the closes
Excel.
Thank you for taking the time to read this.
Any help you can offer would be very much appreciated.
Dave7.
Excel Automation Using VB6
Hi guys, I am very new to VB and this forum so be gentle :-)
How do I create a chart in Excel using VB6?
This is how far I have gotten (this is mostly what I have found on the net)
Sub createExcelGraphs()
Dim oXL As Object ' Excel application
Dim oBook As Object ' Excel workbook
Dim oSheet As Object ' Excel Worksheet
Dim oChart As Object ' Excel Chart
Dim iRow As Integer ' Index variable for the number of Rows
Dim iCol As Integer ' Index variable for the number of Cols
Set oXL = CreateObject("Excel.application")
Set oBook = oXL.Workbooks.Add
Set oSheet = oBook.Worksheets.Item(1)
Set oChart = oSheet.chartObjects.Add(50, 40, 300, 200).chart
iRow = 10
iCol = 2
' This line gives the error - "Object required"
oChart.ChartType = Excel.XlChartType.xlLine
oChart.SetSourceData Source:=oSheet.Range("b1").Resize(iRow, iCol)
oXL.UserControl = True
End Sub
I want to create an xlLine graph and be able to chaneg the font on the axis. How do I do this?
Thanks
OLE Automation Excel
I posted a previous thread which asked how to use the Solver add in included in excel in the VB6 environemnt in order to solve an equation with 3 unkowns. In short, I use solver to minimize the sum of the squares.
In thinking about the problem I think I can do this by openning an excel workbook in VB which has the solver included as a recorded macro. I would then dump my data onto the excel workbook and use a shortcut key to launch the solver macro. I am unsure if this will work or how to do this ==> Open an existing file and type the shortcut key in excel without the user being aware of it. Any suggestions would be appreciated.
If you know how to call the solver add in in Excel from a VB6 application that would do as well.
Thanks.
Excel Automation Via VB.NET
Hi,
I wanted to know how can i pass a variable number of parameters tovb.net function though excel.
I have sucessfully created functions, menus and other things but i canseem to pass a dynamic number of parameters of a function the thevb.net.
If anyone can explain how to achieve that it would be very apreciated ;)
Best Regards,
Luis Simoes
Excel Automation Using VB
ITtoolbox Portal for Visual Basic - http://visualbasic.ittoolbox.com/
Hi,
here's the website that could also help you...
For information and sample code for integrating Office with Visual Basic, Visual C++, Internet Scripts, and other programming languages,please see
http://support.microsoft.com/support/officedev/offd evout.asp.
This site contains the most up-to-date information for using developer tools for Office integration and extensibility.
Read article # Q219151: How to Automate Excel from VB
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ ~~~~~~~~~~~~~~~~~~~~
Excel Automation !!! Please Help !!
Hi Friends,
I am automating Excel in one of my Projects under VB.
When I use:
objExcel.Quit - then normally the Excel Application should quit/end/terminate from the memory, but it isn't....
After I end my VB Program, then only the Excel Application is getting terminated.
Please help me as what should I do, in order to terminate/end/quit Excel Application, while my VB Program is still running. (objExcel.Quit ?? or something else ??).
Regards.
Excel Ole Automation In VB
I want to show Excel file through the OLE Automation in VB. this is excel file should show a Chart which is feeded by variable data . I mean i have to pass the input data to the excel file to show the chart any time i open that form or report. i haven't done it before and i don't know anything. can anyone tell me how i should start it.
Thanks
Excel Automation Help
A user just requested something I don't think is possible. I was hoping that someone here would know whether or not I'm right. I've been googling this for almost an hour with no luck so far. Thanks in advance!
I am importing a csv file into a database (SQL Server) and have this set up in a DTS Package. Now, the user wants this to run for the first 10 days of the month. He also wants the info to be stored in a new Excel file each of these days. So, there'd be an Excel file for day 1, one for day 2, etc.
I'm not quite sure that I can set up an Excel file to be automatically created and populated with the csv file info. I can do this manually quite easily, but he is looking for 100% automation -- open a folder every day and a new file is there waiting for him.
Questions then:
1) Can an Excel file even be created automatically?
2) If so, how do I set up a naming structure so that old files aren't overwritten?
3) Is there a way to add this to my package?
Many thanks to anyone that can help!
Excel Automation !! Help
Hi Friends,
I am working on Excel File and VBA. Can anyone tell me what's wrong with following command:
Private Sub optMonday_Click()
Sheet1.Unprotect
'Now here I want to enter some values.....and in the next line want to re-protect sheet....
Sheet1.Protect
End Sub
Tha above always displays an error (Runtime Error 1004) as:
Method 'Unprotect' of object '_Worksheet' failed. Similarly, Method 'Protect' of object '_Worksheet' failed 'when I try to Protect it...
How I can Protect and Unprotect the Sheet...Please help.
Thanx in advance.
Excel Automation
Hello!
How can I get current cell's size and position relative
to the screen?
I have next situation.
There is some edit control. It is created by add-in. It
must be positioned and covered at current cell. I can get
cell's size through Range object. But i cann't get cell's
position relative to the screen.
Can anybody help me?
Best regards.
Oleg.
Excel Automation
I would like to know if it's possible to automate the bitmap insertion in an excel cell.
If so, I would like to see a sample.
Thanks in advance.
Excel Automation
Hello,
Im looking for a way to catch on an event in Visual Basic when a user drags text from another program onto my excel worksheet which I have creatd from with Visual Basic.
Basically what I have is this
Dim excelApp As Excel.Application
Dim WithEvents excelWorksheet As Worksheet
Dim excelworkbook As Workbook
Private Sub cmdCreateExcel_Click()
Set excelApp = CreateObject("Excel.Application")
Set excelworkbook = excelApp.Workbooks.Add
Set excelWorksheet = excelApp.Sheets(1)
excelApp.Visible = True
End Sub
Does anyone have any ideas?
Thanks,
Nicholas Laizer
Excel Automation Through VBA
I have an Excel workbook, which will be open. My objective is to have that workbook programmatically open another and copy a chart from it another one which will also need to opened by the first one. I'd like to keep them invisible. I could either do a copy or a move but I can't get either to work.
I have it opening the second one and the third one. It's jsut the copy that keeps failing. PLease help!
Thanks
Mike
Excel Automation
Hi,
I want to create a DLL that will be used in Excel to perform operations on the workbook it is referenced in. I DON'T want VB to open the workbook. A user will open the workbook and the object in the dll will be instantiated through VBA code.
I have the DLL compiled and I created a test VB project to see if it will execute in the VB IDE. To do that, I had to create an Excel object and instantiate it through the form code and open the sheet. Then my class was called and everything did what it was supposed to. The class doesn't instantiate anything. It just selects a bunch of cells.
Now if I try to call the same class through an open excel workbook, I get a 'method"~" of object"~"' failed' error.
Does anyone know what might be causeing this. The only difference is in the open Excel workbook, I don't use GetObject or create object to instantiate Excel since it's already opened. I even tried putting
Dim objXL as Excel.Application
set objXL = GetObject(, "Excel.Application")
into the class thinking maybe it needs to know somehow that Excel was already open. I get the same error.
For kicks, here's what the class module code looks like
option Explicit
public Sub CreateChart()
'I tried with and without the next 2 lines
Dim objXL as Excel.Application
set objXL = GetObject(, "Excel.Application")
'Counting rows
Dim lngRow as Long
lngRow = 2
Dim myTotalRow as Long
Do While Cells(lngRow, 1) <> ""
lngRow = lngRow + 1
Loop
myTotalRow = lngRow - 1
'counting columns
Dim lngCol as Long
lngCol = 2
Dim myTotalCol as Long
Do While Cells(1, lngCol) <> ""
lngCol = lngCol + 1
Loop
myTotalCol = lngCol - 1
ActiveSheet.Range(Cells(1, 1), Cells(myTotalRow, myTotalCol)).Select
'CREATING THE GRAPH
'Charts.Add
End Sub
I call the from a module in the workbook by doing this
Sub CreateGraph()
Dim Graph as ExcelFunctionLibrary.clsXCLChart
set Graph = new ExcelFunctionLibrary.clsXCLChart
'set Graph = nothing
Graph.CreateChart
End Sub
When it gets to graph.createchart, that's where the error occurs. Remember, that the workbook is not opened by VB. I want it to work outside of the VBIDE and without any interaction with
any other executable other than Excel.exe
Thanks in advance,
Mike
Excel Automation
I have an "Excel.Application" running, with my data in an "Excel.Worksheet".
How do I execute Excel commands (average, std. dev. etc.) ?
Thanks,
Gino
Excel And OLE Automation
I am trying to use Excel and OLE automation. I am using MSAcess to generate a spreadsheet, and then Excel to import it and format it, etc. What happens is that the forms rule applies, and for some reason, though I close the Excel application, there is a reference left and the File remains locked.
I want to know if there is anyway to know if an OLE invoked object has any references left, and if I can clean up the references (using IReleaseReference or some similar process). Anyone using Excel and OLE automation has probably run up against this so please HEEEEEEEELP......
Thanks,
Nikhil
Excel Automation
I am using the workbook method "SaveAs" which can accept 11 arguments. It works fine until I try and save a file that already exists and it prompts me with a dialog asking if I want to overwrite it. If I click the "Yes" button everything is fine, but if I click the "No" or the "Cancel" button I get a message box informing me of a "SaveAs" method failure. I cannot find any information about the 11 arguments does anyone know what I am doing wrong.
Thanks
Jim Bassett
Excel Automation
Hi!
How can I fill a range of cells on Excel worksheet without moving
from cell to cell and inserting the data into each cell?
Besides I can't use Clipboard to perform such an operation
Thanks for any reply.
Alex.
Merlin Automation With Excel Thru VBA
I am linking a pgm called Merlin to excel. Merlin controls test equipment. I read thru the samples and help files and I can get the status of the merlin pgm (such as test started, test ended, etc) thru a SUB called CONNECT. I call CONNECT through the MerlinAutomation library that is referenced in VBA.
The help file says that CONNECT's method's are to be put into a class that I put into my VBA pgm. An additional help file gave me the methods that I put in the class.
I created a class module called MerlinEvents that I put all the methods in. I then added the following line "Dim Started As New MerlinEvents".
Here's the problem: The Merlin help file states the CONNECT sub should look like this: MerlinAutomation.CONNECT("projectname.class")
When I type the projectname (VBAProject), the class name is not a member or method of the project. I tried just putting the class name in there (Started) and it says "object variable not set". I tried adding a method to Started (Started.TestStarted). The pgm does step into the class module, when TestStart finishes VBA says "activex component unable to create object".
Some additonal info:
Merlin Help file states that the project should be set up as a ActiveXEXE type and the class instancing to Multiuse.
I can't fing project type in VBA and the only class instancing for VBA are 1) Private and 2) PublicNotCreatable - I tried both.
Am I trying to do something where VBA is insufficient? Do I need an actual VB pgm (compilier)?
Excel Automation Too Slow
I'm using Excel automation from VB6. Based on everything I've read - my code is standard. I turn visibility off at the beginning and on at the end to save time i.e. eliminate screen updating.
I am creating an M by 7 spreadsheet. When M is about 120 - the whole thing takes 15 seconds. It gets worse as M increases.
How can I improve this?
What if I created a single comma delimmited string in VB6 and "dumped" it to Excel all at once? Does that make sense? I'm thinking if I could avoid calling .cells M*7 times things should get faster.
Any ideas?
Thanks in advance.
Automation Macro In Excel
Well, my first try at VBA and I might as well get better at it, since my job involves alot of statistics beeing pulled from a SQL server and then we use Excel to sort the data (well not sort in that manner but to have it look nice etc..)
My problem is now that I am working on a macro that allowes me or the user to have a main workbook with three sheets, one for the calculation, and two for stats which is where the problem lies.
The stats we are getting are comming out in single excel files, meaning that for January I would get 31 excel files, where filename is "name of statistics_yyyy_mm_dd" so I have tried to make a macro that opens the first file "name of statistics_2003-01-01", copys a set of cells from that, pastes it to the main workbook and then closes it and opens the next. Now I got that working by Cut&paste 31 times, but it's not flexible. For febuary then I would manually have to change it..
So, what I need help with, or at lest some tips if someone has, is to make it so that the user is asked to select the 1st file and then the macro increments the filename until all the files have been taken. I have a pice of code here that I will try and paste for you :
This is the Sub for cut & paste
Code:
Sub Bef_18()
'
' Bef_18 Macro
' Macro recorded 2003-02-20 by BEBO3426
'
'
Range("A2:L2").Select
Selection.Copy
Windows("Stats_Bef18_Aft18_Total_Jan.xls").Activate
ActiveSheet.Paste
Windows("Stats Bef18 2003-01-01.xls").Activate
ActiveWindow.Close
End Sub
And this is the declaration part.. might be I am doing something wrong here as well though...
Code:
Dim i As Integer
i = 1
Dim s As String
's = Filename
Do
Sheets(Före_18.Range("A" & i).Value).Select
Bef_18
i = i + 1
s = s + 1
Loop While Före_18.Range("A" & i).Value > ""
What I could need here as I allready have the increment for Cut&Paste (it increases so that each new copy&paste is on a new line) is a way to select first file (wheter it be january or any month/file) and then the macro /Vb increments the rest... and it loops..
I am new at this but I appriciate any help to get me moving.. and yes I have a error in the declaration part, hence I have commented out the s=Filename...
*Edited*
Actually seems like I have more than one error.. I can't the i=1 to work either.. says "Invalid outside procedure" so I'm stuck there too
Thank you!
Baxel "The n00b"
Anyone can mess up, to really Screw things up, you need a computer..
Help With Problem With Excel Automation Within VB6
I had to put this problem on the back burner, but now it is coming back to haunt me.
The original query was here:
http://www.xtremevbtalk.com/show...threadid=51253
Anyway, I still get the same error.
When running my Excel automation code, at this one point, I get the error "1004: Unable to set the linestyle property of the border class."
I had initially suspected erroneously that it had something to do with me using the select method (which I consider far less than elegant). So, at the advice of a forum poster, my code looks like this:
Code:
Dim rng As excel.range
With xlsheet
'select the temp values area
Set rng = .range(.Cells(lintGridStart, lintMainStartColumn + 2), (.Cells(lintGridStart, lintMainStartColumn + 1 + Caltempnum)))
If Not rng Is Nothing Then
rng.Borders(xlInsideVertical).LineStyle = xlContinuous 'problem line 1
rng.Borders(xlInsideVertical).Weight = xlThin 'problem line 2
rng.Borders(xlInsideVertical).ColorIndex = xlAutomatic ' problem line 3
'actual cell formatting
rng.HorizontalAlignment = xlCenter
rng.VerticalAlignment = xlCenter
With rng.Font
.Name = "Courier New"
.FontStyle = "Regular"
.size = 12
End If
End with
Set rng = Nothing
Now, the error occurs about 75 % of the time. and on any of the three lines marked "problem line." (whichever is first and not commented out)
Does anyone have any insight/experience in property changes simply failing in the excel object?
Any insight/suggestions would be greatly appreciated
(I am down to 82% original hair growth)
Automation Error Using Excel
I wrote a small program to open an excel spreadsheet and save some data. It works fine on my primary machine, running Windows XP. Installed on my Windows 98 machine however all kinds of trouble starts. It gives me some runtime -214.... error "Automation Error" when it encounters a reference to "xlwksheet". I cannot figure out what has happened. Below is some code that I use to open, close and sort the spreadsheet. I can open and close the spreadsheet without trouble, however I cannot run the sort utility. Any help would be appreciated as I am at ropes end!
Dim xlapp As Excel.Application
Dim xlbook As Excel.Workbook
Dim xlwksheet As Excel.Worksheet
Public rowck As Integer
Public Sub openxl()
Set xlapp = New Excel.Application
Set xlbook = xlapp.Workbooks.Open(App.Path & "employee.xls")
End Sub
Public Sub showxl()
xlapp.Visible = True
End Sub
Public Sub closexl()
xlbook.Save
xlbook.Close
xlapp.Quit
Set xlwksheet = Nothing
Set xlbook = Nothing
Set xlapp = Nothing
End Sub
Public Sub openxlsheet(sheetnum As String)
Set xlwksheet = xlbook.Worksheets(sheetnum)
xlwksheet.Activate
End Sub
Public Sub sorter()
For Each xlwksheet In xlapp.Worksheets '(Crashes at this point)
xlwksheet.Activate
xlapp.Columns("A:E").Select
xlapp.Selection.Sort Key1:=xlapp.Range("A2"), Order1:=xlAscending, Header:=xlYes, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
xlapp.Range("a1").Select
Next
End Sub
Help - VB6 && Excel Automation Error
Hi guys, u all are my last resort....
I stuck for 4 days to check this problem. I'm using XP but all users' OS are Win 98. In my system, the automation is running perfectly well. I'm using Excel as my reporting tools.
But when I install to users and run it, it always prompt 'Run-time Error -2147023067 (80070725) Automation Error !' when it come to the reporting parts.
I'd check this forum, Microsoft and find out that the problem was occured because of these 4 files : ASYCFLT.DLL, OLEAUT32.DLL, OLEPRO32.DLL and STDOLE2.TLB. It's because I'm using XP so PDW fill take XP version which is not compatible with pre-XP OS.
To know whether these files are XP version or not, we can check from SETUP.LST and look at these files. If it's begin with 350. it's mean XP but if 240. that's mean pre-XP.
The solution is to use back the pre-XP version. These I had solved it myself. That is by copying these files into VB98WizardPDWizardRedist folder.
I'm very happy when I check my new SETUP.LST contain with those files with 240. version. But when I install it again, the same problem occur.
Why Why Why ? Oh, please help me guys. I'm getting bald by plugging my hair for 4 days.
VB - Excel Automation Slow :o(
Hi!
I am exporting data from an excel spreadsheet to an access database using ADO.
I have created a macros to do so, and if I import the macro and run it (having excel running), it works quickly.
If I automate Excel from Vb and run the macro, it also works quickly, however I need to export data from a NUMBER of excel spreadsheets that currently do not have the macro in, so this would mean the user would have to import the module and run the macro for each file (slightly time consuming).
What I would like to do is automate excel from VB and instead of running a macro, let vb retrieve the data and add it to the Access Database, but this takes FOREVER! I have a pentium 4 pc and it is just way too slow. I know that this is due to the fact that I am reading each value from the workbook from vb, is there any other way to speed up the process, like extract the data to a recordset first? Any advice would be appreciated,
Nicole
Excel And Vb6.0 Automation Objects
Hi,
Im trying to create a vb app for my husbands company. i have created one so that he is able to create a excel app.sheet that saves certain data to it from the text control boxes on the form.
i can reopen the newly created excel sheet and add 1 line to the bottom as the top is of a definite length. my problem is i want to fill in the form with text and data and press the button again and be able to save to the last empty line of the excel sheet. ive tried everything. and im bout to give up.
please help.
Excel Chart Automation
I need to add a border to a chart in Excel from my VB 6.0 app
Here's what i got so far
'Make Chart in Excel
Set xlChart = xlWkb.Charts.Add
xlChart.SetSourceData xlSht.UsedRange
xlChart.ChartType = xlXYScatterLinesNoMarkers
xlChart.Legend.Clear
xlChart.ChartTitle.Text = xlSht.Range("B1").Value
Now i need to add a border to it like:
xlChart.border.colorindex =1
xlChart.border.weight = xlHairline
xlChart.border.linestyle = xlContinuous
But that's not it, does anyone know what it is? From macro I get the .Border but xlChart.border isn't recognizable?? P.S. The only lines that don't work are the last 3.
Excel To Outlook Automation
Okay, so I haven't completly started this project yet, but I wanted to know if anyone had done something similar and could help.
What I need to do is create an Excel file that contains contact information, i.e. Name, Phone #, Address, etc. I then need to be able to convert this into a .csv file so that it can be imported into Outlook.
Basically, I need to create an external file that all users can access and update contact information, and then allow the managers to import this file into their Outlook contacts.
Any ideas or suggestions would be greatly appreciated.
Thanks!
|
TRACKER
