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.)
View Complete Forum Thread with Replies
See Related Forum Messages: Follow the Links Below to View Complete Thread
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
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
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
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
Run An Excel Macro From Access
G'day,
I know this may be a simple request, but I can't work out how to run an Excel Macro from Access code. Please help.
Run MS Excel Macro From MS Access
Hi,
I am using MS Access 97. While in the MS Access application, I would like to call a Macro I have created in MS Excel. Can someone provide me with the vba code that will perform this task?
Thanks a lot for your time and help,
Run Macro In Excel From Access
Can someone help me with this, please:
I'm using Access 2000 and from there I try to build code with VB6 to run my Excel macro within Excel.
I get MsgBox saying:
"Object doesn't support this property or method"
I have marked references in Access to use Excel library.
Here is my code for it:
Private Sub test_Click()
On Error GoTo Err_test_Click
Dim XlApp As Excel.Application
Set XlApp = New Excel.Application
XlApp.Visible = True
XlApp.Documents.Open Filename:="C:myExcel.xls"
XlApp.Run "myproject.myModule3.mymacro"
XlApp.Quit
Set ExApp = Nothing
Exit_test_Click:
Exit Sub
Err_test_Click:
MsgBox Err.Description
Resume Exit_test_Click
End Sub
Is there something wrong in code or what should I do?
Run An Excel Macro From Access With VBA...
Using Office XP.
i have recorded a macro in excel which just does some simple formatting to a worksheet.
I have saved this in my personal.xls it is called: FormatOVL
From within Access using VBA i generate a worksheet.
I then want to run my macro to format it. I can't seem to work out how to run the macro. I have currently been trying this code below but it does not seem able to find the macro.
Am i missing something ? Is it possible ?
XLapp.Run ("Personal.xls!FormatOVL")
The help file seemed to point me in this direction ?
I have saved the macro in my personal.xls as i thought it would be available to all worksheets i open on my machine which is great as i will want to format several worksheets from Access using VBA and this macro (if i can get it working).
Any help much appreciated
Excel Macro With Access Data
This is what I am trying to do. I have an Excel macro that is pulling data from another excel workbook. That workbook contains data that is imported from an Access query. What I want to do is skip over the other workbook and get the data directly from Access. Can this be done?
Running An Access Macro From Excel
I have a macro set up in Access which merges various excel spreadsheets . What I want to do is include the right VB code in my excel macro so that once it has finished with the spreadsheet it automatically opens access and runs the macro in there....can this be done ??
Running An Access Macro In Excel
I'm trying to run an Access Macro (Macro1) using the Visual Basic Editor in Excel. The code i'm using is:
Code:
Sub RunAccMacro()
Dim oAccess As Object, oDB As Object
Set oAccess = CreateObject("Access.Application")
oAccess.Visible = True
oAccess.OpenCurrentDatabase "J:UPSUPS.mdb"
oAccess.Run "Macro1"
End Sub
This opens the Access Database ok but then I get the runtime error:
Run-time error '2517':
Microsoft Access can't find the procedure 'Macro1.'
Any ideas why this is happening?
Matt
Help Me :( {Access To Excel Export Macro}
I am FAR from pro at visual basic.
Here's the problem.. When I export files from Microsoft Excel to Access (2003), the first two rows of the exported file get split up in a table. It'll look something like this:
Buyer Field 2 Buyer Cost Field 4 PO
Code Buyer Pricer Date Date
I need to create a macro that will somehow put all of the headers on the second line and completely delete the first.
Can someone help me?
Calling Macro In Excel From Access With Vba
HI
Im new here, this is my first question and I hope someone out there can lend me hand or point me in the right direction. Thanks.
I have developed an Excel template with a macro in VBA. I have ten different templates which I want to fire off. At the moment, all ten is fired off from Access using a shell command, but Im finding that it uses too much virtual memory. The ten templates are all the same except using different parameters, so I am thinking of just having one template and using the Access to call up a template and passing in the parameter so the macro can act on it using that parameter. Is there a way of calling a macro function in Excel using vba?
Anyone got any advice, will be greatly appreciated.
Cheers
Calling Access Macro From Excel
I have a model, running from a macro in Excel. The model cycles through hundreds of thousands of iterations for forecasting purposes. I am taking the output from the model after each iteration and dumping it into Access.
I am experiencing two problems calling the Access macro (that imports the data and places it in the appropriate table) from Excel:
1.) The database connection times out. The speed of the Excel macro is drastically reduced if I open Access after each iteration to dump data in. Therefore I have connected to Access in the beginning of my Excel macro and am only calling the Access macro after each iteration. I tried to open and close the database with each iteration, but it is time consuming and sometimes the database doesn't close which causes the program to halt.
2.) I call the Access macro in the following manner:
In the beginning of the macro the following is established:
Dim cn As New Access.Application
cn.OpenCurrentDatabase ("C:
ame.mdb")
cn.Visible = False
Then after each iteration:
SendKeys "MacroName{ENTER}"
cn.RunCommand acCmdRunMacro
Then at the very end:
cn.CloseCurrentDatabase
cn.Quit acQuitSaveNone
Set cn = Nothing
The problem is with the "SENDKEYS" statement. Sometimes, about 1% of the time, the macro seems to skip over the line. The macro window will open in Access prompting me to enter the macro name and "ENTER".
Any wisdom that could be imparted regarding these two issues would be greatly appreciated! Thanks for your help.
Help With Macro Designed To Go From Access To Excel
I developed a simple macro to take two queries from access and spit them into excel, HOWEVER i can't seem to figure out how to make the macro save it as a different file name each time.
Basically i want the macro to ASK me what the name of the file should be, THEN save.
Currently the macro is set up to save each query as a single name, one is "Client MSDS.xls" and the other is "Client Inventoryxls"
Here is what i have now:
Option Compare Database
Option Explicit
'------------------------------------------------------------
' Send_To_Excel
'
'------------------------------------------------------------
Function Send_To_Excel()
On Error GoTo Send_To_Excel_Err
DoCmd.OpenQuery "Client MSDS", acNormal, acEdit
DoCmd.RunCommand acCmdOutputToExcel
DoCmd.Close acQuery, "Client MSDS"
DoCmd.OpenQuery "Client Inventory", acNormal, acEdit
DoCmd.RunCommand acCmdOutputToExcel
DoCmd.Close acQuery, "Client Inventory"
Send_To_Excel_Exit:
Exit Function
Send_To_Excel_Err:
MsgBox Error$
Resume Send_To_Excel_Exit
End Function
Access Excel Using Word Macro
Hey guys, I'm new here and need some help.
I'm using VB in MS Word and I need to access data from Excel. Does anyone know how to open an Excel file and copy or assign data from a cell to a variable in MS Word?
Envoke Excel Macro Fr Access
What code would be appropriate to open an excel file, run a macro in excel and close excel, all
from access?
Please help.
Thanks.
Connect Excel To Access With Macro (VBA)
I am trying to connect Excel to Access and I learned that I would have to write a Macro script (VBA code) that will allow user to upload their Excel data to Access. I am new to Macro so can someone guide me on how I should approach this situation.
In Excel, there are 7 columns however in Access there are 5 columns. Some columns in Excel are combined in Access into 1. So, I need a way to combine some columns before uploading to Access. Also, the user would have the copy of it for their reference.
Open Access Database With Excel Macro
Hi all!
I managed to open Access from Excel, but I'm not able to open a database.
Code:
Set Projekt = CreateObject("Access.Application")
Projekt.Application.Visible = True
Projekt.Application.Database.Open Filename:="C:oce...FTF_LA81_.mdb"
So does anyone know how should I alter the last line?
Thank you in advance!
[REZZOLVED]Getting A Excel Macro To Work In Access
Hi all, I have this macro
Code:
Sub Test()
Dim I As Integer
For I = 2 To Worksheets.Count
'copy from worksheet
Worksheets(I).Range("A1:A" & Range("A" & Rows.Count).End(xlUp).Row) _
.EntireRow.Copy
'paste to new worksheet
Worksheets(1).Range("A" & Range("A" & Rows.Count).End(xlUp).Offset(1, 0).Row) _
.PasteSpecial
Next I
For I = Worksheets.Count To 2 Step -1
Application.DisplayAlerts = False
'delete all worksheets bar the first
Worksheets(I).Delete
Application.DisplayAlerts = True
Next I
End Sub
In Excel works just fine. But now i need to call this from access, and to be honest im not good at opening excel as an object from a access macro and having excel do its thing. ive done it before but im having particular trouble with this one... can anyone help me out?? I know we need to append objects to their respective ".methods" but with the "Worksheets" its acting funny. Oh this macro MUST be executed from access, ie the code must be in access, i cant call the macro from the excel workbook.
Any help would be much appreciated.
Thanks!
Doc
Edited by - doctor on 7/20/2004 12:36:48 PM
Excel && Access Macro/Vba/Batch File Question
Hello,
I have a question and want to know if its possible since I am rather new to programming side of things. Here is my situation:
There is a database here at my work that I have to query via excel to get infomation. I do this via Excel. I then am using this information in Infopath which needs the data to be in Access.
I would need a macro/code for running the SQL in Excel which queries the data from the database.I would need Access macro/code to automatically import the data from Excel.Then I would like to automate this on a daily basis so then I would a batch file correct?Basically I want the Access Database to be updated automatically everyday from the SQL that I do in Excel. So that the Infopath form has the most up-to-date information everyday.
Is this something that can be accomplished? Or...does anyone have any ideas of how to do this differently (maybe easier) but accomplish the same ends?
Thank you for any help and insight.
Is It Possible To Run A Macro In An Excel Workbook From An Access Database - Resolved
I am in the process of making a database in Access 97 that has links to a couple of Excel 97 Worksheets (in same Workbook). There is a macro in the workbook that I'd like to run, preferably without getting the user to have to open the workbook, run the macro, then return to Access. I'm almost sure that I read somewhere that this was possible, but I've no idea how it would be done.
Any suggestions would be appreciated.
Thanks in advance.
JackiD
Resolved
with thanks to Suat
See Below
Create a button on your form, then put a command button on this form (click Cancel when it goes to wizard to create button) then right click on the button and point to Built Event. Select Code Builder in the list. It will goto VBA and show you the Click event of the Command button, just like below:
Private Sub Command1_Click()
End Sub
Now put your code into this event sub, it will seem like below when you place the code in it:
code:
Private Sub Command1_Click()
Dim xls As Object
Dim wrk As Object
Set xls = CreateObject("Excel.Application")
Set wrk = xls.Workbooks.Open("D:Excel97 FilesTrial.xls")
xls.Run ("Module1.ReSetTime")
wrk.Close False
xls.Quit
Set xls = Nothing
End Sub
(Module1.ReSetTime: means you are calling a macro in workbook, named as "ReSetTime" and which is placed in a module named as "Module1")
I hope this helps.
Suat
Edited by - JackiD on 7/27/2003 9:52:51 AM
Access 2003 Opening Excel File And Not Running Workbook_open Macro How?
Using code below I am opening various spreadsheets and running multiple routines.
Code:
Dim objFileSystem As Object
Dim objFolder As Object
Dim objFilesCol As Object
Dim objFile As Object
Set objFileSystem = CreateObject("Scripting.FileSystemObject")
Set objFolder = objFileSystem.GetFolder(DtR)
Set objFilesCol = objFolder.Files
For Each objFile In objFilesCol
xLs.Workbooks.Open DtR & objFile.Name
xLs.Run "'" & objFile.Name & "'!all"
xLs.ActiveWorkbook.Close savechanges:=False
Next
Set objFileSystem = Nothing
Set objFolder = Nothing
Set objFilesCol = Nothing
Set objFile = Nothing
One of the spreadsheets is doing:
Code:
Workbooks.Open Filename:=EXTERNALSPREADSHEET, ReadOnly:=True, Origin:=xlWindows
This EXTERNALSPREADSHEET sometimes contains workbook_open macro.
How can I prevent this macro from running?
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!
Calling An Excel Macro From An Access Database That Refers To That Database
hi
i have a macro in excel that inserts some values into a table in a database in access, but i want to make it so i dont have to touch the excel file, instead i have a module in the access file that calls the sub in the excel file, but then i get an error saying that the database is already open....is there a way of getting around this?
thanks
jimmyp
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
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
|
TRACKER
