Close Excel From Within Macro?
Hi
I have designed a customized login screen that is displayed in the Workbook_Open event. How do I cause the Excel spreadsheet to close if the user presses Cancel or the Close button (X button) on the login form?
I have another related question. How do I set the startup object in Excel VBA. I know there is an option while using VB, but what about VBA?
Thanks,
Sarah
View Complete Forum Thread with Replies
See Related Forum Messages: Follow the Links Below to View Complete Thread
How To Close Excel Using Macro In Access.
I've opened an excel file and manipulated it using macros from access. I don't want it to continue running and I'm having trouble finding the code to close it. (Good ol' record macro can't record its own demise.)
Excel VBA Run Macro On Workbook Close
How can I run some code when the workbook is being closed?
I would like to be able to automatically protect my sheets on workbook close, and thought something similar to Auto_Open would work, but I couldnt get Auto_Close to work?
Excel Macro's To Open, Print And Close Workbooks
I have three file type that I need to grab edit and print. These file types are .sum, .dat and .csv. The file names are like this 123.sum, 123.dat, 123.csv; 321.sum, 321.dat, 321.csv. Ok I already wrote to the point where I can open a range of files of certain type ex *.dat edit and print. But what I would like to do now is print files 123.sum then 123.dat, and 123.csv. Not all my Dat's first and csv and so on. I will attach my macro so you can have a better understanding on what is happen. It will be cut and pasted into notepad. If you can help Thanks
RedDog
Edited by - RedDog on 6/15/2002 12:55:54 PM
Excel Macro's To Open, Print And Close Workbooks
I have three file type that I need to grab edit and print. These file types are .sum, .dat and .csv. The file names are like this 123.sum, 123.dat, 123.csv; 321.sum, 321.dat, 321.csv. I have one hundred of each file type a day. Ok I already wrote to the point where I can open a range of files of certain type ex *.dat edit and print. But what I would like to do now is print files 123.sum then 123.dat, and 123.csv. Not all my Dat's first and csv and so on. I will attach my macro so you can have a better understanding on what is happen. It will be cut and pasted into notepad. If you can help Thanks
RedDog
I am sorry I uploaded the code that was wrong, not working.. Sorry!!
Thanks for your help...
Edited by - RedDog on 6/17/2002 4:15:58 PM
Excel Macro's To Open, Print And Close Workbooks
I have three file type that I need to grab edit and print. These file types are .sum, .dat and .csv. The file names are like this 123.sum, 123.dat, 123.csv; 321.sum, 321.dat, 321.csv. I have one hundred of each file type a day. Ok I already wrote to the point where I can open a range of files of certain type ex *.dat edit and print. But what I would like to do now is print files 123.sum then 123.dat, and 123.csv. Not all my Dat's first and csv and so on. I will attach my macro so you can have a better understanding on what is happen. It will be cut and pasted into notepad. If you can help Thanks
RedDog
Excel Macro Disabling Control Box "Close"
Hi everyone, one small question
I am adding a User Form to a workbook, my question is that how can i disable the "close" control box of the above user form because i want that the user to press "ok" or "cancel" command buttons on the user form to enter the worksheet. Otherwise if the user press "Cancel" the worksheet closes.
Can this be done. Oh by the way i am using VB6 with Excel 2000
Regards
Amarjit
Converting An Excel 2002 Macro To An Excel 95 Macro
Hi there, I’ve finally got my macro program to work (thanks everyone). Now I have to load the macro on a much older machine. I wrote the Macro on Excel 2002 and now I’m trying to run it on Excel for Windows 95 Version 7.0. I’ve loaded it up, but there are all sorts of errors. Does anyone know of a simple way to convert the macro code so it will run smoothly on Excel 7.0?
Openning Excel And Running Macro From Word - Macro
Ok - I have a DB which I export to Excel, I then run a macro to "Groom The Data". I then run word and run a Macro (MailMerge to print labels) which retrieves data from the excel spreadsheet groomed above. Everything is working fine, despite my novice knowledge of VB, but I would like to do this in a single click.
I use a command line shortcut to Launch word and automatically have it run the macro, but I still have to Launch Excel and manually run its macro first.
I did not find a command line switch to make excel launch and run a macro. Is there any way I can Lauch Word have its Macro link to excel, then launch and run the excel macros before performing the macro I have set up in word (mailmerge)
In Other Words I have working macros in Word and Excel, I want the Word Macro to force the Excel macro to run before completing the rest of its duties.
Thanks
WW
Macro: Close Word After Print
Is there a way I can close word automatically after a file prints?
here is the macro:
Code:
ActiveDocument.PrintOut
Application.Quit
with this it prompts and says if word exits the print will stop. is there an .isbusy command or something similar to that..so that i can have the application wait until printing completes and then exit?
thanks
-B
Simple Problem - Run Macro Just Before Close
How do I get a macro to run when the user hits the "x" in the top right corner of the work sheet... I have tried:
Private Sub Workbook_BeforeClose ()
...stuff
end sub
but i get the error message:
"Compile error: Procedeure declaration does not match description of event or procedeure having same name"
What does this mean? and What do I do to fix it?
Thx in advance!
Not Close Thead When I Close Excel
I have developed, a .net dll. I call this dll from excel VBA code. In dll code I throw a thread. I need the thread still is running when I close Excel. How can I do it?
Thanks
________________
Hip Hop Videos
Foro Heavy
Run Access Macro From Excel Macro
Hi
I am using Microsoft Office 2000 and trying to run an Access macro from an Excel macro, but my Excel macro does not seem to be working. When I run the Excel macro, nothing happens - I do not get an error and the code does not do what it is suppossed to. Below is my code:
Code:
Sub DisplayForm()
' Initialize string to database path.
Const strConPathToSamples = "C:Miscell est.mdb"
strDB = strConPathToSamples & "test.mdb"
' Create new instance of Microsoft Access.
Set appAccess = _
CreateObject("Access.Application")
' Open database in Microsoft Access window.
appAccess.OpenCurrentDatabase strConPathToSamples
' Run Access macro.
appAccess.DoCmd.RunMacro "Macro1", 1
End Sub
I have included the references to "Microsoft DAO 3.6 Object Library" and "Microsoft Access 9.0 Object Library."
Any help on this subject would be much appreciated.
Thanks
Possible To Launch A UserForm In Excel From Startup Macro Without Showing Excel?
Greetings to all VBA/Excel Excperts!
I have a project at work that is requiring me to utilize Excel/VBA (something I'm not super-conversant in) and I am wondering if it is possible to have a UserForm be the first thing that pops up when you launch (i.e. double click on) a spreadsheet (likely an .xlt but not necessarily)? I know I can have an Autostart Macro (or something of that ilk) that could launch the form, but I am hoping there is a way to do this so that it just looks like I launched the UserForm and am not firing up the entire Excel program.
As soon as the functionality encapsulated in the UserForm is completed it will shut down Excel, which is why I'd rather just have the form and nothing else.
My reason for doing this is to make deployment of this "application" simple - just drop the spreadsheet onto the target system (which of course must have Excel 2003 installed, which all of our company systems do) and double click. Saves having to create an install package for a plain ole VB app.
Thanks for any suggestions and/or taunts you may have!
Close All Excel Applications Before Start New Excel Application
Ciao Gurus,
I created an excel 2003 application which takes time to be opened.
I notice that it is more fast (50-60%) if no other excel applications are opened, so here the question: is it possible to close all excel application before start my excel application?
Of course not manually
Best Regards
kaiala
Emailing Excel Files Via Outlook Using An Excel Macro
Ok maybe this is an easy one for someone out there.
My goal is to write a macro within Excel to automatically grab a file, attach it to an email and send it from Outlook. I know this can be done. I currently have a copy of a macro that will do this exact sequence but through Lotus Notes instead of Outlook. My hang up has to be with the communication between the two programs.
This is what I have currently (Excel to Lotus Notes):
Const embed_attachment As Integer = 1454
Dim session As Object
Dim db As Object, doc As Object, ws As Object, rtitem As Object
Dim CurRow As String
Dim Recipient As Integer
Dim special As String, location As String, ext As String
Sheet2.Select
'Establishes Notes session using server and .nsf file
'Server and sender must be the person sending the email
server = Cells(2, 2)
Sender = Cells(3, 2)
Set session = CreateObject("Notes.NotesSession")
Set ws = CreateObject("Notes.NotesUIWorkspace")
Call ws.opendatabase(server, Sender)
Set db = session.GetDatabase(server, Sender)
'Begins the mail loop for the number of recipients
Recipient = 8
send_to = Cells(Recipient, 2)
Do
Set doc = db.CreateDocument()
doc.savemessageonsend = True
subject_out = Cells(4, 2)
Message = Cells(5, 2)
Message2 = Cells(6, 2)
special = Cells(Recipient, 3)
doc.sendto = send_to
doc.Subject = subject_out
doc.returnreceipt = "1"
Set rtitem = doc.CREATERICHTEXTITEM("Body")
Call rtitem.APPENDTEXT(Message)
Call rtitem.ADDNEWLINE(2)
Call rtitem.APPENDTEXT(Message2)
Call rtitem.ADDNEWLINE(3)
location = "h:Monthly BudgetsColumnarxls"
'Reads filenames and attaches files until it reaches the end of the file list
z = 4
file = Cells(Recipient, z)
Do
Call rtitem.EmbedObject(embed_attachment, "", location & file & ext & ".xls")
z = z + 1
file = Cells(Recipient, z)
Loop Until IsEmpty(file) = True
'Sends file, resets doc variable to empty, and advances to next recipient
Call doc.Send(False)
Call doc.Save(True, False)
Set doc = Nothing
Recipient = Recipient + 1
send_to = Cells(Recipient, 2)
Loop Until IsEmpty(send_to) = True
'Sets variable to empty to save on memory
Set session = Nothing
Set db = Nothing
Set ws = Nothing
Set rtitem = Nothing
End Sub
Help!!!!
Thanks,
Matt
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, To Close Or Not To Close
Hello All,
I have a VB6 app that can open different Excel sheets. At some point after the user selects these sheets, Excel starts acting strange. Meaning Excel opens, but only the toolbar can be seen.
I will post my code being I know everyone will think early binding problems, but i can't find anything out of whack.
Also, I didn't use .QUIT being i want the user to be able to print or modify the sheet if needed. At this point, if the user opens some other sheet on there pc, Excel doesn't cooperate. I guess I would like to get rid of EXCEL.EXE in the task mgr once the user closes Excel, but it seems to hold the lock until my VB app is closed.
Any ideas????
Code:
'Code
Dim xlApp As excel.Application 'excel
Dim xlBook As excel.Workbook 'excel
Dim xlSheet As excel.Worksheet 'excel
Dim spMnth, spDay, spYear, spDate As String
Set xlApp = Nothing
Set xlBook = Nothing
Set xlSheet = Nothing
Set xlApp = excel.Application
Set xlBook = GetObject("C:EstSysRptssp_req.xls") ' production
Set xlSheet = xlBook.Worksheets(1)
xlApp.ScreenUpdating = True
xlBook.Application.Visible = True
xlBook.Windows(1).Visible = True
xlBook.Application.Cursor = xlWait
xlBook.Application.StatusBar = "***Building Excel Sheet - Please Wait***"
Set rsSP = gDatabase.OpenRecordset("Select * from [Special Pricing Request] Where PROPNO = " & SelJobNo, dbReadOnly)
With rsSP
xlSheet.Cells(2, "I") = 1 'Page Number
xlSheet.Cells(6, "C") = !branch
xlSheet.Cells(7, "C") = !JobName
xlSheet.Cells(8, "C") = !city & ", " & !State
xlSheet.Cells(9, "C") = !PROPNO
xlSheet.Cells(6, "I") = !slsname
xlSheet.Cells(7, "I") = !req_dt
xlSheet.Cells(9, "I") = !BID_DT
xlSheet.Cells(10, "I") = spDate
'Product Check Boxes
xlSheet.Cells(14, "A") = IIf(!PRD1 = True, " ___X___ ", " _______ ")
xlSheet.Cells(15, "A") = IIf(!PRD2 = True, " ___X___ ", " _______ ")
xlSheet.Cells(16, "A") = IIf(!PRD3 = True, " ___X___ ", " _______ ")
xlSheet.Cells(17, "A") = IIf(!PRD4 = True, " ___X___ ", " _______ ")
xlSheet.Cells(18, "A") = IIf(!PRD5 = True, " ___X___ ", " _______ ")
'Competition Check Boxes
xlSheet.Cells(28, "A") = IIf(!COMP1 = True, " ___X___ ", " _______ ") 'Simplex
xlSheet.Cells(29, "A") = IIf(!COMP2 = True, " ___X___ ", " _______ ") 'EST
xlSheet.Cells(30, "A") = IIf(!COMP3 = True, " ___X___ ", " _______ ") 'HoneyWell
xlSheet.Cells(11, "I") = spDate
xlSheet.Cells(14, "I") = !TSell
xlSheet.Cells(15, "I") = !GM_PCT
xlSheet.Range("F" & 37).Formula = "= (100 - SUM(F28:F36))"
'Protect the sheet after all data is transferred.
Screen.MousePointer = vbDefault
xlBook.Application.Cursor = xlDefault
xlBook.Application.StatusBar = "Ready"
xlSheet.Protect ("Pass")
If Dir("C:Progra~1Estima~1SP_REQ" & !PROPNO & ".xls") <> "" Then Kill ("C:Progra~1Estima~1SP_REQ" & !PROPNO & ".xls")
xlBook.SaveAs ("C:Progra~1Estima~1SP_REQ" & !PROPNO & ".xls")
If EMailClick = True Then
holdPropNo4Mail = !PROPNO
holdJobName4Mail = !JobName
xlApp.Quit 'Quit Excel if report is sent through email.
End If
Set xlApp = Nothing
Set xlBook = Nothing
Set xlSheet = Nothing
End With
Delete An Excel File With An Excel Macro
Hi
Did any know if I can delete an excel file with an excel macro? (the file is not the ones that I am, instead is one in the same directory).
Any suggestions I'll appreciated it
Question From Excel Or Excel Macro Gurus
Hi To ALL
i really want to now that is there is any utility/way or wizard through which i can make my interface on Excel just like this
here i m trying to display my interface which wil be on sheet or user forum
TextBox1 Textbox3
TextBox2 TextBox4
Button--- Add ---.Display result in TextBox4
and Run time Change on TextBox3[that will automatically add textbox1 & textbox2 and display in [Textbox3]
but i want to do this without coding is their is way or wizard in Excel Macro to Do this Please Confirm me this thing is it possible without codind i just want to do this without coding
Somebody told me that through Excel we can do this as Front Page Make WebPage & made their code automatically
Please Reply me about this Question
Thanks in Anticipation
Bye......
Close Excel As Well?
Hello, Everyone:
I tried to get "Do you want to save" dialog like this by clicking X:
Private Sub Workbook_BeforeClose(Cancel As Boolean)
Dim thisWkBook As Workbook
Set thisWkBook = ThisWorkbook
thisWkBook.Close savechanges:=False
End Sub
Now,Dialog is gone. However, the excel is still there. I need to click X once more to get rid of the Excel. Can someone help me to get Excel as well when I click X once.
Thank you very much!
Charlie
'close' Help In Excel
Hi,
Is there anyway of stopping the user from clicking on the "X" close button in the top right-hand corner of the screen. I want the user to exit my form by pressing a command button, and I do not know how to disable the "X" button?
Excel Won't Close
I am trying to make a graph in Excel using vb 6. The problem is that when I run the code Excel stays open. If I don't use the line highlighted in red then there is no problem.
Private Sub Command2_Click()
Dim oWorkbook As Excel.Workbook
Dim oSheet As Excel.Worksheet
'
Set oExcel = New Excel.Application
oExcel.Workbooks.Open FileName:="C:Documents and Settings" & SpreadSheet & ".xls"
oExcel.Charts.Add
' Note the type
'.Columns("A:B").Select
oExcel.ActiveChart.ChartType = xlXYScatterSmoothNoMarkers
oExcel.ActiveChart.HasTitle = True
oExcel.ActiveChart.ChartTitle.Characters.Text = "Total Number of Operations per Team"
oExcel.ActiveChart.SetSourceData Source:=Sheets("Sheet1").Range("A1:B1"), PlotBy:=xlColumns
' Note the location
oExcel.ActiveChart.Location Where:=xlLocationAsNewSheet, Name:="MySheetName" & " Chart"
'End With
Set oSheet = Nothing
Set oWorkbook = Nothing
oExcel.Quit
Set oExcel = Nothing
end sub
How To Close Out Excel
Hi,
I'm trying to write code so that will direct Excel to close itself...
I have a piece of code that closes out a workbook but i'm not sure how to close out Excel?
My code looks like,
Application.DisplayAlerts = wdAlertsNone
Windows("Urine Macro.xls").Activate
ActiveWorkbook.Close
Application.DisplayAlerts = wdAlertsAll
Any ideas would be greatly appreciated.
Thanks
How To Close Excel Vb
can i put a close button in excel vb to close vb once it has run
and can i put in a cell in excel to run vb
cheers
Doug uk
Excel Will Not Close With VB6
I have a VB6 program that populates comboboxes from data with an Excel worksheet. But when I exit the VB program, in the Task Manager Excel is still running, sometimes many times. I have the Excel sheet opening at Load() and I want to have Excel exit when I unload the program. I need the Excel sheet to be opened until the program is ended. Everything works how it should except for the Excel sheet not closing. So how do I fix this?
Code:
Private Sub Form_Load()
Dim MyXLApp As New Excel.Application 'declare and start a new excel application
'Open an Excel workbook
MyXLApp.Workbooks.Open FileName:="M:material_strength_list_1.xls"
For y = 2 To 4 Step 1
cmb_mat.AddItem Sheets("Main").Cells(y, 1)
Next y
For v = 2 To 6 Step 1
cmb_temp.AddItem Sheets("Main").Cells(v, 2)
Next v
End Sub
Private Sub Form_Unload(Cancel As Integer)
Dim ans As Integer
Dim MyXLApp As New Excel.Application
ans = MsgBox("Do you really want to exit?", vbYesNo, "Exit Confirmation")
If ans = vbYes Then
MyXLApp.Quit
Set MyXLApp = Nothing
End
End If
End Sub
Excel Won't Close From VB6
From VB6 I open excel add a workbook, write to it, save the workbook, close the workbook, quit the application.
BUT: excel stays open in the task manager. If I run it again I get an error msg.
Public Sub OpenExcel()
On Error GoTo OpenError
Set xlApp = New Excel.Application
Set xlBook = xlApp.Workbooks.Add
Set xlSheet = xlBook.Worksheets.Add
xlSheet.Name = "Imported"
bExcelOpen = True
On Error GoTo 0
Exit Sub
OpenError:
MsgBox "Error opening Excel Spreadsheet" & vbCrLf & _
"Error: " & Error, vbCritical, "Open Error"
On Error GoTo 0
bExcelOpen = False
End Sub
Public Sub CloseExcel()
' Save the Sheet
On Error GoTo ExcelCloseError
xlSheet.Application.DisplayAlerts = False
xlSheet.SaveAs App.Path & "Scrap" & sName
xlSheet.Application.DisplayAlerts = True
xlBook.Close
' Close Microsoft Excel with the Quit method.
xlApp.Quit
' Release the objects.
Set xlSheet = Nothing
Set xlBook = Nothing
Set xlApp = Nothing
bExcelOpen = False
Exit Sub
ExcelCloseError:
MsgBox "Error closing Excel Spreadsheet" & vbCrLf & _
"Error: " & Error, vbCritical, "Shutdown Error"
On Error GoTo 0
bExcelOpen = True
End Sub
Thanks
Excel Won't Close
I am having a problem with Excel not closing even after I give the quit command. In the task manager the process is still running. This is causing my application to hang. Could anyone help me out? Here is my code:
Code:
Private Sub cmdCrtRpts_Click()
Dim nx As Integer
Dim nx2 As Integer 'checks to see that all fields are filled out.
nx = 0
nx2 = 0 '0=fields missing 1=all fields filled in correctly
Do Until nx = 13
If txtJobInfo(nx) <> "" Then
nx = nx + 1
Else
nx2 = 0
MsgBox "All fields must be filled out to create a report!", , "Empty field error"
Exit Sub
End If
Loop
'all fields filled out
'MsgBox "report will be created"
'************************Create Excel Report********************
Dim objExcel As Excel.Application
Dim objWB As Excel.Workbook
Dim objRC As Excel.Worksheet ' report cover
Dim objRng As Excel.Range
Dim excel_filename As String
Dim cover_picture As String
Dim report_cell_index As Double
Dim report_cell As String
'************************Create Dmis Report********************
CreateRpt
'**************************************************************************************
Dim CMMNUMBER As Double
Dim calcert As String
CMMNUMBER = Val(frmJobInfo.txtJobInfo(12))
If CMMNUMBER < 2 Or CMMNUMBER > 4 Then
Do
CMMNUMBER = InputBox("You have entered and incorrect CMM Machine Number!" & vbCrLf & "Please re-enter" _
, "INCORRECT CMM MACHINE ID")
Loop Until CMMNUMBER > 1 And CMMNUMBER < 5
Else
If CMMNUMBER = 2 Then calcert = "c:program filesjncmmcmm2.gif"
If CMMNUMBER = 3 Then calcert = "c:program filesjncmmcmm3.gif"
If CMMNUMBER = 4 Then calcert = "c:program filesjncmmcmm4.gif"
End If
If CMMNUMBER = 2 Then calcert = "c:program filesjncmmcmm2.gif"
If CMMNUMBER = 3 Then calcert = "c:program filesjncmmcmm3.gif"
If CMMNUMBER = 4 Then calcert = "c:program filesjncmmcmm4.gif"
frmJobInfo.txtJobInfo(12) = Str(CMMNUMBER)
'************************Create Excel Cover Sheet********************
Set objExcel = Excel.Application
Set objWB = objExcel.Workbooks.Open("c:program filesjncmm
eport_dmis.xlt")
Set objRC = objWB.Worksheets("REPORT COVER")
objRC.Range("b7").Value = frmJobInfo.txtJobInfo(0)
objRC.Range("b8").Value = frmJobInfo.txtJobInfo(1)
objRC.Range("b9").Value = frmJobInfo.txtJobInfo(2)
objRC.Range("b10").Value = frmJobInfo.txtJobInfo(3)
objRC.Range("b11").Value = frmJobInfo.txtJobInfo(4)
objRC.Range("b12").Value = frmJobInfo.txtJobInfo(5)
objRC.Range("b13").Value = frmJobInfo.txtJobInfo(6)
objRC.Range("b14").Value = frmJobInfo.txtJobInfo(7)
objRC.Range("b15").Value = frmJobInfo.txtJobInfo(8)
objRC.Range("b16").Value = frmJobInfo.txtJobInfo(9)
objRC.Range("f12").Value = frmJobInfo.txtJobInfo(10)
objRC.Range("f13").Value = frmJobInfo.txtJobInfo(11)
objRC.Range("f14").Value = frmJobInfo.txtJobInfo(12)
objRC.Range("A22", "h44").Cells.Select
cover_picture = frmJobInfo.txtJobInfo(13)
objRC.Pictures.Insert cover_picture
'**************************************************************************************
'************************Create Excel Report Sheet********************
Dim fso, r
Set fso = CreateObject("Scripting.FileSystemObject")
Set r = fso.OpenTextFile(txtRptOutput.Text, 1)
report_cell_index = 3
Set objRC = objWB.Worksheets("Report")
Do
strReadLine = r.readline
report_cell = "A" & report_cell_index
objRC.Range(report_cell).Value = strReadLine
report_cell_index = report_cell_index + 1
'r.skipline
Loop Until r.atendofstream = True
r.Close
'**************************************************************************************
'************************Create Excel Calibration Sheet********************
Set objRC = objWB.Worksheets("Calibration")
objRC.Range("A1").Value = ""
'objRC.Range("A1").Cells.Select
objRC.Pictures.Insert calcert
'**************************************************************************************
'************************Save Excel Report********************
excel_filename = Left(frmJobInfo.txtRptOutput, Len(frmJobInfo.txtRptOutput) - 4)
excel_filename = excel_filename + ".xls"
ActiveWorkbook.SaveAs FileName:=excel_filename, _
FileFormat:=xlNormal, Password:="", WriteResPassword:="", _
ReadOnlyRecommended:=False, CreateBackup:=False
objExcel.Quit
Set objExcel = Nothing
Set objWB = Nothing
Set objRC = Nothing
'**************************************************************************************
MsgBox "Reports Completed!" & vbCrLf & vbCrLf & "TextFile=" & frmJobInfo.txtRptOutput & _
vbCrLf & "Excel File=" & excel_filename, , "REPORTS COMPLETE"
frmJobInfo.txtRptInput = ""
frmJobInfo.txtRptOutput = ""
Dim x As Integer
x = 0
Do
If x = 9 Then
frmJobInfo.txtJobInfo(9) = "N/A"
x = x + 1
Else
frmJobInfo.txtJobInfo(x) = ""
x = x + 1
End If
Loop Until x = 13
End Sub
Thank you!
Rich
How To Close Excel
Hi
I like to know how to properly close excel properly after opening it as I can see excel.exe is still running behind (from task manager) and I cannot open it again unless excel.exe is ended from task manager. Pls help me.
How To Close The Excel With VB?
the following is the standard example program of operatong excel with VB.
but cann't close the excel , when we click the command5, the excel will found in the task manange.
Code:
xlsApp.Workbooks.Close
Set xlsApp = Nothing
shall close the excel , but cann't , why?
=============An entire excel example program===============
Code:
Option Explicit
Dim xlsApp As Excel.Application
Dim wrdApp As Word.Application
Private Sub Command1_Click()
Set xlsApp = Excel.Application
With xlsApp
'Show Excel
.Visible = True
'Create a new workbook
.Workbooks.Add
'Put text in to the cell that is selected
.ActiveCell.Value = "Hi"
'Put text into A3 regardless of the selected cell
.Range("A3").Value = "This is an example of connecting to Excel"
End With
End Sub
Private Sub Command2_Click()
'close the workbook
xlsApp.Workbooks.Close
'Close Excel
xlsApp.Quit
End Sub
Private Sub Command3_Click()
Set wrdApp = New Word.Application
With wrdApp
'Show Word
.Visible = True
'Create New Document
.Documents.Add
'Add text to the document
.ActiveDocument.Content.Text = "Hi"
.ActiveDocument.Content.Text = "This is a test example"
End With
End Sub
Private Sub Command4_Click()
'Close the current document
wrdApp.ActiveDocument.Close
'Close Word
wrdApp.Quit
End Sub
Private Sub Command5_click()
'Clear the memory
Set xlsApp = Nothing
Set wrdApp = Nothing
End Sub
Excel Will Not Close
Hello! I have the following code. I generate a report into an excel and the I make some modifications in the xls file. The thing is that excel will not close at the end of the process. I have attached the code. Thank you!
Close Excel From VB
When an instance of Excel is opened like this:
VB Code:
Set xlApp1 = New Excel.Application
How do you shut it down when you're done?
Norris
Close Excel Again
When I create an excel object, make my stuff, and close it remains in the taskbar.
This is to generate a report, so If I try to open it again it works fine, but just with simple values( xls.cells(1,1)=3), but I can´t use formulas or try to fit columns cause I get the error
"Object Variable or With block not set"
I've read posts asking this and of course the replies but I can't make it work
Im doing this
Dim xla As excel.Application
Dim xlb As excel.Workbook
Dim xls As excel.Worksheet
Set xla = CreateObject("Excel.application")
xla = True
Set xlb = xlb.Workbooks.Add
Set xls = xlb.Worksheets(1)
xla.Workbooks(1).Close SaveChanges:=true
Set xls = Nothing
Set xlb = Nothing
xla.Quit
Set xla = Nothing
I don't know if this can help but i have seen this:
after this lines
Set xla = CreateObject("Excel.application")
in the taskbar appears "Excel"
xla = True
then "Microsoft Excel"
Set xlb = xlb.Workbooks.Add
then "Microsoft Excel - Workbook1"
and when closing..
xla.Workbooks(1).Close SaveChanges:=true
again "Micrsoft Excel"
Set xls = Nothing
Set xlb = Nothing
the same "Microsoft Excel"
xla.Quit
in the taskbar just appears "Excel"
Set xla = Nothing
and this one don't destroy the object
and remains in the taskbar
Any ideas what to do???
I don't know what to do now.
Im using win 98 and Off 97
Close Excel
Hello, anyone know how to close all excel spreadsheets that are currently open?
Thanks
Chris
Cannot Close Excel
My code is
Code:
Public xApp As Excel.Application
Private Sub Command1_Click()
Dim wb As New Excel.Workbook, str As String
Set xApp = CreateObject("Excel.Application")
xApp.Visible = True
str = "Myfile.xls"
Set wb = xApp.Workbooks.Open(FileName:=str)
wb.Close False
End Sub
Private Sub Command2_Click()
xApp.Quit
Set xApp = Nothing
End Sub
When I click on Command2, Excel window is closed but an Excel process always appears in Window Task Manager,Process Tab. If I run above code again, two Excel processes are in Window Task Manager and so on.
Anyone can help me to terminate Excel processes?
Close Excel
IS there a way to close Excel without it giving a popup message to save information
this is what I use
xlApp.Quit
rest of code
Dim xlApp As excel.Application
Dim objWrkSht As Worksheet
Set xlApp = New excel.Application
xlApp.Workbooks.Add
Set objWrkSht = xlApp.ActiveWorkbook.Sheets(1)
How To Close Excel
Hi there,
I wrote an application that reads data from an excelsheet
(importfilter), using the following code to open the file:
Dim objExcelApp as new Excel.Application
Dim objExcelBook as Excel.Workbook
Call objExcelApp.Workbooks.Open(strFileName)
set objExcelBook = objExcelApp.ActiveWorkbook
When the application is terminated, I want to close the excel-
instance too, but it just doesn't work. Even if I use
Unload objExcelApp
Unload objExcelBook
there is still the excel-task in the task mangager.
How can I close it totally?
Thanks in advance,
Daniel
Excel Macro Help
I'm trying to write a macro so that when you start a new row a 'N' automatically goes into column E. A friend of mine asked me for help and for the life of me I can't figure it out.
Any help is appreciated, Thanks!
Amy
Is This Possible? Excel Macro
If I wanted a user to open a particular file (name known to user) located in a particular folder could I just have them type the filename in an excel cell eg - they would type "thisfile" and then have a macro button which will open the file.
would it perhaps look something like this?
Sub OpenCellValue()
Workbooks.Open Filename:="C:MyFolderSubFolder[Range("A1").Value].xls"
End Sub
Obviously the code above doesn't work at all. Am new and stuck - hope someone can help
GH
Help With Excel Macro
Hello,
I need to create a macro to peform the following tasks:
1) Open a file dialog pop up to ask user to select the
Excel file(s). Files will have different names, but same
format.
2) Copy certain cells in all files selected and paste into
an Excel master file called "Master".
Here are the example of the 2 Excel files data and format:
1) Opened files format, could be called Data1, Data06, etc.
A B C D
1 Date: 5/2/03
2 Requester: John
3 Buyer: Mary
4 Issue: Defective parts
5 Ship: 5/8/03
2) Master file:
A B C D E
1 Date Requester Buyer Issue Ship
2 B1 D2 C3 B4 C5
3 B1 D2 C3 B4 C5
4
5
NOTE: Row 2 is from file Data1, row 3 from Data06, etc.
SUMMARY: Dialog to open selected files, copy certain cells
and paste/transpose over to the Master file, where it will
put the data in the first file into row 2, 2nd file data
into row3, etc.. THANKS
VBA For Excel... Or A Macro
I have to do a spreadsheet at work, well its using Quattro Pro 10, which does use VBA, but also in Excel XP.
See, its going to be like a large... Index for files on the hard drive, as well as a spreadsheet to sort "done", "in progress", Creator, division, etc.
Its going to be a huge listing of files. Right now I'm seeing about 600+ and more in the future. We want to do html links to a relative path on the disk to them, and that works just fine. Except for having to open a dialog box, travel down the path and type in the 'shown text'.
I tried to just use html in the cell, and thats a no go.
Right now all of the files are <groan> in directories by dept and then by 'done', 'in progress', 'not started'. My first idea is to get rid of that crap. Just throw it all into dept directories. If its 'not started' and theres a html link to it and someone moves it, the link is broken.
I suppose the question is, does VBA have a way to just run through the path relative to the spreadsheet and grab all of the files throwing them into html links with the path+ file also as their visible name?
A quick way to begin this would be helpful, and a quick way to update things down the road would help too. It just nagged me as something that shouldn't be so hard, but was just made needlessly annoying, and there HAD to be a more simple solution.
Thank you for you help/advice
Need Help With Excel Macro
Hi, I'm new to this forum. Here's my problem:
I have an Excel macro that deletes the entire row if the cell in the selected column is empty. I need to modify it so that it deletes the entire row if the cell in the first column contains certain text or does not contain certain other text. It seems like this shouldn't be too difficult, but I'm having a hard time with it. Here's the macro as I currently have it - any help much appreciated:
Public Sub DeleteBlankRows()
Dim R As Long
Dim C As Range
Dim N As Long
Dim Rng As Range
On Error GoTo EndMacro
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
If Selection.Rows.Count > 1 Then
Set Rng = Selection
Else
Set Rng = ActiveSheet.UsedRange.Rows
End If
N = 0
For R = Rng.Rows.Count To 1 Step -1
If Application.WorksheetFunction.CountA(Rng.Rows(R).EntireRow) = 0 Then
Rng.Rows(R).EntireRow.Delete
N = N + 1
End If
Next R
EndMacro:
Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic
End Sub
Excel Macro
I would like to create a VB macro in Excel that will store the modification date of the worksheet in a Cell. This will allow someone who is only viewing to see how current the data is.
I tried using DateLastModified command but could not get it to work.
Any help would be appreciated
Thanks
Excel Macro
I am using an Excel macro to automate a query from Access but have a bit of a problem... everything works fine when I am using the hardcoded values as selected in the query wizard e.g.
`Milestones Stuff`.`Project Ref`=3
However, what I really want to do is change these values depending upon other conditions something like this:
Dim myvariable As Integer
myvariable = 6
`Milestones Stuff`.`Project Ref`= myvariable
This doesn't seem to work at all since the query does not return any data. Can you do this kind of thing in VBA? If not what other way can you change macros dynamically?
Thanks
Help With Excel Macro Please....!!
I am trying to return the file path of a specified file without the file name attached to the end of the path.
I am really struggling with interpreting the useless help documentation in VB.
I want the macro to prompt the user for a directory using this code..
pname = Application.GetOpenFilename(, , "Please Select A File In The Prefered Working Directory", , False)
This allows the user to browse the directories for the files they want but I really only want the directory, not the file itself. Unfortunately, you must select a file that gets attached to the variable, pname in this case.
This is one of the examples the help documentation shows for using the path property, one of the things that I tried...
Sub ShowFileAccessInfo(filespec)
Dim fs, d, f, s
Set fs = CreateObject("Scripting.FileSystemObject")
Set f = fs.GetFile(filespec)
s = UCase(f.Path) & vbCrLf
s = s & "Created: " & f.DateCreated & vbCrLf
s = s & "Last Accessed: " & f.DateLastAccessed & vbCrLf
s = s & "Last Modified: " & f.DateLastModified
MsgBox s, 0, "File Access Info"
End Sub
Can someone tell me what filespec stands for?
I don't care about the extra info, like date modified and such.
I am only concerned with getting the path to the directory that the user has chosen, without the filename.
I have tied countless different methods to extract the directory only without the file because my code goes on to use the
.LookIn command to search the designated directory for as many files of a certain type there are, then it opens each file one by one and does stuff to them.
I just don't want to have to hard code the path to the directory in the macro script. I want the user to be able to browse for it when they run the script.
Is there an easy way to do this that I am completely overlooking??
Thanks,
|
TRACKER
