Visual Basic :: Excel In VB

Copy-(special) Pasting Excel Table Into Word By Clicking Button In Excel
I have a table in excel which I want to be able to to copy paste into an empty letterhead word document which is already saved in 'my documents'. I want to add a button onto the excel spreadsheet which when clicked will call up the empty (read-only) letter headed document and paste itself in there. However, I want to special paste it (unformatted text) to remove it from the table, then change the font on the whole document. In the same macro I want to have it underline some designated phrases which appear in the freshly pasted text as well.

Is this at all possible?

Playing Sound With VBA In Excel 2003 Using SndPlaySound32 Doesn't Work In Excel 2007
The following worked in Excel 2003:
Private Declare Function sndPlaySound32 Lib "winmm.dll" Alias "sndPlaySoundA" _
(ByVal lpszSoundName As String, ByVal uFlags As Long) As Long

Call sndPlaySound32(WelcomeFilenames(filenumber), 1)

Why doesn't it work in Excel 2007? Is there a way of using a single play .wav sounds command that will work in both Excel 2003 and Excel 2007?

I tried googling around and searching in these forums, but could not find anything related. If this has already been discussed, please redirect me to the appropriate location.

Thanks for your help.

Invalid Data In Excel Spreadsheet/count Group Of Items In Excel Sprdsh
Please help:
Problem#1:
I populated an excel spreasheet with data from access table, using CopyFromRecordset function. The process was successful and all the fields displayed correctly in the spreadsheet, except the date field which displayed:"########". What did I do wrong?

Problem#2:
What vb code can I used to count group of items in the spreadsheet.
Any insight will be very helpful.
Thanks,
C.

Exporting An Access Table To A New Excel SS And Open Excel File Afetr Export
Hi,

Can some help me with writing the code to Export a table from Acces into a new excel spreadsheet, then open an instance of excel to view the newly exported table? I can get the file to export but I am having trouble with the code to open Excel, thanks for you help. See code below..

Private Sub Command26_Click()

DoCmd.TransferSpreadsheet transfertype:=acExport, _
        tablename:="newreqtable", _
        FileName:="F:UserdataSelectionData.xls", hasfieldnames:=True, _
        Spreadsheettype:=8

MsgBox "Export is Complete", vbOKOnly, "Selection Data Export"

MsgBox "Your new file is located on F:UserdataSelectionData.xls", vbOKOnly, "Data Export Location"

'This is where I need to have excel open the file for the client.

End Sub

Excel Application.workbooks.open(filename) Doesn't Return Excel Workbook
I use the following code to open work book. Then would like to reference the excel work book using excelwrk.

I can't reference excelwrk as excelapp.workbooks.open("c: est.xls") doesn't return any work book and Excelwrk is nothing.
I have no choice, have to reference worbook using excelapp.activework

Is there anything wrong with my code. Please advise. When I run application, there is no error. I am using Excel 8.0 object library.

Dim excelapp as Excel.Application
Dim excelwrk as Excel.Workbook
on error resume next

Set excelapp = GetObject(, "Excel.Application")
if err=429 then
Set excelapp = CreateObject("Excel.Application")
end if

Set excelwrk = excelapp.Workbooks.Open("c: est.xls")

Thanks

Yee

Can I Query Data From Multi Excel Tables And Insert Them Into Another New Excel Table
Hi everyone,

Can I query data from multi excel tables and insert them into another new excel table via VC?

i.e. I have excel table A, and excel table B, they have one same column "num"

I hope I can join these two tables into one new excel table via this public coulmn, all the steps should be finished via VC (please tell me if you have other suggestion about the program tool ^_^)

Seems that the function of "Microsoft query" in the Excel is OK, But I hope I can do a good UI and user don't need to write sql by himself, so it is better if you can tell some reference about how to query from muti-tables (I think it should be muti-datasource) and insert this result to another excel table,Thanks a lot :-)

AND how to implement it? please give me more doc if it is possible, Thanks!

I'm not familar with sql syntax about excel, could you please tell more about this? thanks!

Thanks
Lindsay

Closing Excel Process After Creating Excel.Application From Access 2003
After such code:
Code:
Dim xLs As Excel.Application
Set xLs = New Excel.Application
xLs.Visible = True
xLs.Workbooks.Open DtR
‘do stuff
xLs.ActiveWorkbook.Close savechanges:=False
Set xLs = Nothing
There is always Excel process left running. When I do this 5 times I will have 5 instances of Excel running.
Is there a way to kill this process at the end of the code?

Excel-VBA Q: Mill's Posting Excel 101: Filtering Data Using Multiple Combobox
Sub: Excel-VBA Q Mill's posting Excel 101: filtering data using multiple combobox - how to indirectly reference the

I joined this group yesterday. Thanks to all who are actively participating. I read Mill's posting 'Excel FAQ - Excel 101 ' ; thanks for the sample code.

I love Excel, and has been using it for 7 years - good at formula, macros etc, not programming with VBA. Now I want to venture into that area. The last time I used VB was in 1998 - VB5, worked just on one project. I am good at OOA, OOD, and have done C++ programming.

I have a question with reference to the code found in the Lesson 3 - Having items in one ComboBox filter items in another ComboBox.

In the FilterList() subroutine, could I indirectly specify the argument for the Case statement through a cell reference instead of directly specifying it in the subroutine.
e.g.
Select Case strCompany
Case $D$1

Select Case strCompany
Case "Apple"

Actual code from Mill's posting:

Sub FilterList()

Dim strList As String, strCompany As String, strEmployee As String

On Error GoTo FilterListError

strCompany = Range("B1").Text

Select Case strCompany
Case "Apple"
strList = "=$F$2:$F$6"
Case "IBM"
strList = "=$G$2:$G$4"
Case "Microsoft"
strList = "=$H$2:$H$4"
End Select

Regards & Thanks

Create Excel File And Execute Excel Menu Command To Email It
I am using this code in VB 6 to create an Excel file and execute the
keystrokes to open the Send to message window in Excel.

Dim objExcel As Excel.Application
Dim objWorkbook As Excel.Workbook
Dim objSheet As Excel.Worksheet

On Error Resume Next 'ignore errors
Set objExcel = GetObject(, "Excel.Application")
If Err.Number <> 0 Then 'If Excel is not running then
Err.Clear ' Clear Err object in case error occurred.
' On Error GoTo ExcelProb
Set objExcel = CreateObject("Excel.Application") 'run it
ExcelWasNotRunning = True 'so i can shut it down
End If

Set objWorkbook = objExcel.Workbooks.Add
Set objSheet = objWorkbook.Worksheets(1)
objSheet.Cells(1, 2).Value = "write some stuff"

objExcel.SendKeys "%FDA", True

I want to be able to write to the new workbook from my VB app and
then allow the user to email it. The SendKeys doesnt seem to be doing
anything. How do I activate the workbook and then have the
"Send to..." message dialog box appear??

any help is Greatly appreaciated!!!

Lee

Exporting ListView To Excel And Utilizing Excel Functionality (Yield Formula)
I am actually trying to do two things. The first of which I already know how to do.

1) Export the contents of a ListView to Excel.
2) Add a column which utilizes Excels Yield formula.

For example:

The yield formula in Excel requires the following inputs: YIELD(settlement, maturity, rate, pr, redemption, frequency, basis). I already am able to export these data elements from my ListView to Excel in columns A-G. What I need to know is how to create a column H when I do my export which will utilize Excels YIELD formula and the data in columns A-G to calculate yield.

Hope that wasn't too confounded of a question.

Thanks for taking time to read.

How To Import Excel Data From A Closed Excel File To Access Table?
Hi friends,
i have the following Excel sheet

A B
1 BILL PAID
2 CM20 650.00
3 CM32 1750.12

I want to import the same data into an Access table with the same field name.

Any help?

Thankx and regards

Haris

_____________________________________________________________________

Edited by - harisraz on 6/18/2006 1:33:00 AM

Microsoft Office Object Library Excel 2003 And Excel 2000
Ciao Gurus,

I have developed an application with excel 2003 and, of course, it takes as reference the library of excel 2003, but most of the users have excel 2000 and the application goes in error when this reference is called. The library is Microsoft Outlook that in excel 2003 is 11.0 version while in excel 2000 is 9.0.
How can I fix the problem?
Best Regards
KAIALA

Edited by - kaiala on 7/2/2007 3:21:56 AM

Excel 2000: Bring Excel To Front Or MinMax Locked Taskbar
Hi All,

Here I am with another weird query. I have a spreadsheet I want to put on Full Screen (this bit is not a problem and I will be toggling between views). When in Fullscreen mode the Taskbar (the one with the Start Button on) covers over the excel sheet tabs, stopping people using them.

I want a way in code you can view Fullscreen and still see all of Excel including sheet tabs.

If you unlock the Windows? Taskbar at the bottom of the screen and drag it so that it can't be seen then drag it back up Excel then sits on top of the Taskbar as you would expect. How can I do this in code or any other way that achieves the same aim.

As always any help you can give is greatly appreciated.

Rob.

Excel VB Macro Invoked By Customized Button Not Moved When Excel File Is Copied && Pasted
hello....the thing is I wrote a macro in an excel file which get's invoked when a customized button is clicked......now when I copy and past this excel file, the button in the new excel file still keeps pointing to the macro in the old file and only the macro in the old file is invoked again.....can anybody tell me how to resolve this problem?

Find Location Of A Cell In Excel-import From Excel Into Access
Hi
I want to import some columns from an excel spreadsheet into an access table using vb6.

I am going abt this by finding the location of the cell but am having problems this way

Code:
Private Sub ImportExcelAttend()
Dim imA As New ADODB.Recordset 'Recordest for temp table
Dim ex As New Excel.Application
Dim I, J, a As Integer
Dim TheFile As String

imA.Open "select * from tbl_tempAttend", MyConn, , adLockOptimistic

ex.Workbooks.Open txtLocation.Text
ex.Cells(I, J) = "%att"

however i cannot get it to work. any help this way would be appreciated.

OR if you know another way to import these few colums with unknown location of cells into access i would be very grateful. Location will always change every time.

Regards
JohnnyH

Excel VBA Loop Execution Stops When Excel Events Occur
Hi,

I have a perpetual loop which is simply incrementing the value in the top left cell in an Excel worksheet. The loop is called on a button press.

In the loop is 'DoEvents' since I eventually want to allow the user to input data so I can use it as it is entered into the worksheet.

Code:
Private Sub CommandButton1_Click()
Do
DoEvents
Worksheets("Sheet1").Cells(1, 1).Value = Worksheets("Sheet1").Cells(1, 1).Value + 1
Loop Until False
End Sub
My problem is that as soon as any data is entered into any worksheet, the loop just terminates. I can't see where execution goes using the debugger, it just seems to stop.

Oddly, if I remove the line which increments the cell value and use the debugger to pause execution periodically, it seems as if the loop does not terminate when entering values into the worksheet.

I'm truly confused. I had assumed that events in VBA were similar to interrupts in an embedded system and would 'pop the stack' when they ended and continue execution where they left off. This code is so simple it's hard to see where it could be going wrong.

Any help appreciated.

Phil

Edit by Moderator:
Please post Excel questions, in the Excel forum.

Please use the [vb][/vb] tags when you post your code. Edit or reply to this post to see how.

Thank you.

Excel - When Userform Visible, Excel Menu Unable To Be Accessed
I have an excel template that has a pop-up userform to enter different types of data. The issue is, when the form is open, I can no longer access the excel menu-bar. I would like to have this ability so that the user may copy and paste and infomation that maybe on another workbook open in the same window. How do I get this access back when the form is open?
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!

Excel Sheet Question {excel Has Encountered A Problem And Must Shut Down}
I have a question on a form that transfers information to an excel sheet. What I have is a button that when you click it, it sends 4 pieces of information to an spread sheet. My problem is that it works just fine on my machine, but when I install it on a user system if I click it is said that "excel has encountered a problem and must shut down". It shuts down the excel program and my app. Can anyone give me a clue as to why this is happening?
Code use for this is:

Private Sub Excel_Transfer()
Dim sValue As String
Dim sValue2 As String

sValue = txtReqForm(2).Text ' assignes value in text box to string value
sValue2 = txtReqForm(5).Text

Dim oExcel As Excel.Application ' Sets up the excel transfer
Dim oWB As Excel.Workbook
Dim oWS As Excel.Worksheet

Set oExcel = New Excel.Application
oExcel.Visible = True ' Sets up excel sheet to be seen

' Dim oRng1 As Excel.Range

Set oWB = oExcel.Workbooks.Open(modMain.ExcelPath) ' Sets path for the excel document to open
Set oWS = oWB.Worksheets("Sheet1")

Set oRng1 = oWS.Range("A28") ' Sets range in excel cells to modify
Set oRng2 = oWS.Range("B28")
Set oRng3 = oWS.Range("F28")
Set oRng4 = oWS.Range("M28")

oRng1.Value = "1" ' Sets values for the cells to be passed.
oRng2.Value = "ea."
oRng3.Value = sValue
oRng4.Value = sValue2
End Sub

Running MS Excel 2000 Visual Basic In MS Excel 2002
Hi there this is my first post to the forum. I've looked all over Microsoft's website and Google but cannot find an answer to the following problem:

I have been working on a MS Excel spreadsheet that contains a number of macros and User Forms and two custom toolbars. I am working with MS Excel 2000 on a MS Windows 2000 desktop and am based in the United Kingdom. I emailed the finished spreadsheet to my colleague. She is using MS Excel 2002 on a MS Windows 2000 desktop and she is based in Montreal.

When she tried to run the spreadsheet it generated errors every time it tried to run any of the macros. The error was as follows: "Microsoft Visual Basic: Unexpected error (336) in Visual basic." Selecting help brought up further information: "Activex component not correctly registered: Error 336".

I have reported this to my company's IT support but as of yet they are not being that helpful (Visual Basic and Excel are not their strong points).

My colleague has checked to ensure that ActiveX is activated on her desktop but this has not resolved the problem.

Does anyone have any ideas what might be up here? Is it something to do with some kind of incompatibility between Excel 2000 and Excel 2002?

Any help would be greatly appreciated.

Thanks

Jack

VBA Excel 2007 Different Than Excel 2003 For Inserting 255+ Textbox Characters
My VBA application that works fine in Excel 2003 does not work in Excel 2007.

For example, the below code works fine in Excel 2003 to display multiple lines within a textbox:
Set DiagramTextBox = ActiveSheet.Shapes.AddTextbox(msoTextOrientationHorizontal, ActiveSheet.Shapes(PM.ActivityCode(PM.TotalActivities)).Left + 55, ActiveSheet.Shapes(PM.ActivityCode(PM.TotalActivities)).Top + 25, 330.75, 130.75)
DiagramTextBox.TextFrame.Characters(1).Text = "The activities seen in red are on the current critical path(s) based on what crashing (if any) has been done." & vbCrLf & vbCrLf & "Choose a set of critical path activities to crash that TOGETHER cost the least amount to shorten ALL critical paths." & vbCrLf & vbCrLf & "Needless to"
count = DiagramTextBox.TextFrame.Characters.Count
DiagramTextBox.TextFrame.Characters(count + 1).Insert String:=" say, you need to look at the crash cost per period (in the 'CC Per Period' column in the other window), and you need to verify that each activity can still be shortened (compare the 'Activity Time' to the 'Crash Time (CT)')."

In the VBA debugger in Excel 2003, the count variable says that there are 244 characters in the textbox after the first assignment of text but before I use the Insert method to get around the limitation that Excel seems to have inserting more than 255 characters at a time to a textbox.

When I try to run the same code in Excel 2007, I get error 1004 when I execute the line for "DiagramTextBox.TextFrame.Characters(1).Text". Even though I used "Option Base 1" at the start of my module, it seems to want to use 0-based indexing. Why is this?

It seems as though I can change the line to "DiagramTextBox.TextFrame.Characters.Text" and it will work fine in both Excel 2003 and Excel 2007. But then as I step through the VBA debugger in Excel 2007, I get to the line that counts the characters in the textbox, and it says there are only 240 characters (not 244, as with Excel 2003)! The textbox displays the text correctly, though. When I try to enter the line "DiagramTextBox.TextFrame.Characters(count + 1).Insert String:=", I get another error 1004. If I change from "count + 1" to just "count" and use:
DiagramTextBox.TextFrame.Characters(count).Insert String:=" say, you need to look at the crash cost per period (in the 'CC Per Period' column in the other window), and you need to verify that each activity can still be shortened (compare the 'Activity Time' to the 'Crash Time (CT)')."
it will execute, but then the text in the textbox will incorrectly say, "Needless t say,... ". (Notice that it dropped off the letter o).

How can I write VBA code that will work correctly in Excel 2003 and Excel 2007 for inserting more than 255 characters into a textbox?

Thanks for your help.

Creating Master Excel File From Many One Page Excel Files
I am using VB6 and am able to create a master excel file of Teaching schedules by getting all files in a specific directory that start with S2004. Starting with line 26 (MaxTimes = 11 represents time slots from 8a to 10p at night, increments of 1 hr 15 min classes) is a synopsis of the classes that an instructor will be teaching. So far I can create a single sheet in the master file from all the other schedules. What I would like to do is add a copy of the individual schedules into the master file. So, Master Sheet 1 (named 'MASTER') will still contain the synopsis. Master Sheet 2 will be a copy of Person#1's schedule (named for that person), Master Sheet 3 will be a copy of Person#2's schedule (named for that person), etc. My attempt below is not working. Please help. Thanks.

Private Sub CreateMasterSchedule_Click()
Dim Filename As String, Path As String
Dim Workbook, WorkbookAll As Workbook
Dim Worksheet, WorksheetAll As Worksheet
Dim Row As Integer, RowAll As Integer, i As Integer, j As Integer
Dim SheetNumber As Integer

'create MASTER file composed of individual schedule data
Set ExcelAll = New Excel.Application
Set WorkbookAll = ExcelAll.Workbooks.Add
Set WorksheetAll = WorkbookAll.ActiveSheet
WorksheetAll.Name = "MASTER"
RowAll = 1
SheetNumber = 2

Dialog1.Show
Dialog1.Label1 = "Loading..."

' Workbook.Worksheets.Add
' Set Worksheet = Workbook.ActiveSheet

Path = "c:"
Filename = Dir(Path)
Do While Filename <> ""
If Mid(Filename, 1, 6) = "S2004_" Then
'get data from files
Set Excel = New Excel.Application
Set Workbook = Excel.Workbooks.Open(Path + Filename)
Set Worksheet = Workbook.ActiveSheet
' WorkbookAll.Sheets(SheetNumber).Select
Dialog1.Label1 = Dialog1.Label1 + Chr(10) + "Processing " + Path + Filename + "..."
Row = 1
'offset of 3 accounts for two rows at top (Name, Days) and space row between last time slot and row data
Do While Worksheet.Rows.Cells(2 * MaxTimes + 3 + Row, 1) <> ""
For i = 1 To 12
'preserve date format as 03/14/98 instead of 34768
If i = 9 Or i = 10 Then
WorksheetAll.Rows.Cells(RowAll, i) = CStr(Worksheet.Rows.Cells(2 * MaxTimes + 3 + Row, i))
Else
WorksheetAll.Rows.Cells(RowAll, i) = Worksheet.Rows.Cells(2 * MaxTimes + 3 + Row, i)
End If
Next i
Row = Row + 1
RowAll = RowAll + 1
Loop
'copy schedule onto another sheet in the MASTER file
' WorkbookAll.Worksheets.Add
'I haven't figured out how to select and copy the entire sheet yet
' For i = 1 To Row
' For j = 1 To 12
' WorksheetAll.Rows.Cells(i, j) = Worksheet.Rows.Cells(i, j)
' Next j
' Next i
' WorksheetAll.Name = Mid(Filename, 6)
'change back to MASTER list sheet to add next faculty's info
' SheetNumber = SheetNumber + 1
' WorkbookAll.Sheets(1).Select
'close faculty file
Call Workbook.Close(False)
Excel.Quit
Set Excel = Nothing
End If
Filename = Dir() ' Get next entry.
Loop
WorksheetAll.Cells.Sort Key1:=Range("A1:L" + CStr(RowAll))
' WorksheetAll.Cells

WorkbookAll.SaveAs ("c:Documents and SettingscegDesktopMASTER")
Call WorkbookAll.Close(False)
ExcelAll.Quit
Set ExcelAll = Nothing

Dialog1.Hide
End Sub

How To Shade Cell In Excel Based On The Date Value In The Excel Column
Hai ,

I export the content of the MSHFlexgrid to ExcelSheet , Here with i have attached my Excel sheet. The below is the Code i use to export the Excel Sheet. If you see Excel Sheet attached you can find the cells shaded. The code is working fine,

I need a alteration in it. I need to shade the Cell based on the Value in the Column DOffStrm,

if the value in that column is below 30 and the T & I Ets/Ospas date begins with 1 (ex: 3/01/2007)
then the shade should be inside one cell,

suppose the Column DOffStrm is 30 and the Date value of T & I Ets/Ospas is 3/15/2007 then shade should start from half of the current cell and end in the second half of the next cell.

Kindly check this and tell me.

Code:

VB Code:
Private Sub cmdExport_Click() Dim MDur As Integer Dim obj1 As New Excel.Application Dim wsheet As Worksheet Dim wbook As Workbook Screen.MousePointer = vbHourglass Set wbook = obj1.Workbooks.Add Set wsheet = obj1.Sheets(1) Dim i% Dim j% Dim Ce% For i = 0 To MSHFlexGrid1.Rows - 1 For j = 0 To MSHFlexGrid1.Cols - 1 If j > 7 And j < 55 And i > 1 And Len(MSHFlexGrid1.TextMatrix(i, j)) > 1 Then MDur = MSHFlexGrid1.TextMatrix(i, 55) MDur = MDur / 30 wsheet.Cells(i + 2, j + 1).Value = Format(MSHFlexGrid1.TextMatrix(i, j), "dd") wsheet.Cells(i + 2, j + 1).Font.Bold = True obj1.ActiveSheet.Cells(i, j).Font.Color = vbRed obj1.ActiveSheet.Cells(i + 2, j + 1).Interior.Color = vbYellow For Ce = 0 To MDur Next ElseIf j > 7 And j < 55 Then wsheet.Cells(i + 2, j + 1).Value = MSHFlexGrid1.TextMatrix(i, j) wsheet.Cells(i + 2, j + 1).Font.Bold = True Else wsheet.Cells(i + 2, j + 1).Value = MSHFlexGrid1.TextMatrix(i, j) End If Next Next For i = 0 To 1 For j = 0 To MSHFlexGrid1.Cols - 1 wsheet.Cells(i + 2, j + 1).Font.Bold = True wsheet.Cells(i + 2, j + 1).Font.Color = &H800000 Next Next Application.DisplayAlerts = False With obj1.Sheets(1).Range("H2:S2") .Select .Merge End With With obj1.Sheets(1).Range("T2:AE2") .Select .Merge End With With obj1.Sheets(1).Range("AF2:AQ2") .Select .Merge End With With obj1.Sheets(1).Range("AR2:BC2") .Select .Merge End With obj1.Rows(2).HorizontalAlignment = Excel.xlCenter obj1.Columns.AutoFit Screen.MousePointer = vbNormal obj1.Application.Visible = True End Sub

also attached another excel file call Sheet, this type of shading is required here as in the Book1 which is attached here,

so i have can have the copy of the same picture in 4 different Width,

1st picture width will be of 24 and
2nd will be 50,
3rd will be 75
4th will be 100

so with this 4 different images i can cover the area in the cell,

the only code now needed is

1. how to place the Picture in the ExcelSheet through VB.6

2. how to specify the starting part (or X axis or Cell Range in Excel Sheet) of the picture to be placed

i hope, if i have the code for the above two, then it will be solved. Kindly check this and reply me.

Thankyou,
Chock.

Import Excel Into Flex Grid And Export It Back To Excel.
Hi, i want to make a program that can import the excel files into vb's flex grid or maybe a few textboxs.

then do some modifying and then export it back to the excel format.

the reason i want to have these functions is because i have an existing excel spreadsheet with a large number of infos. and i dont really want to reinput is again into teh new system (if i build it with vb+access db). and was thinkning why not straight away make the vb program import and export the file.

and why want to edit it in excel? wouldnt it be better ot do it in MSExcel itself?
well, the user is not me. the user wants to do something pretty much impossible for excel to do, and the user want a few things fixed. so it would be good to have a new simple coded program for the user to use.

What Is The Best Way To Save Data As Excel File If I Don't Know User's Excel Version?
do i want to query their version of excel then load
corresponding libraries...

thank you

How To Create Report In Word Format Through Excel Or Excel Macro
Hi...
I want to create one Report in Word document Format through Excel.
I have some Data in excel.Now How can I create report in word format using Macro or any other feature, if yes then How ??
Can anyone help me in solving this.
Thanks a ton !!!

Excel Or Outlook -- Automatic Execution Of Excel File Each Month
I'm trying to setup an automatic process at a defined interval (week, month,qtr) that alerts my users to take some action on results generated from an excel file. For example, a list of employees in their facility that needs follow-up on license renewals, or other document deadlines, or compliance issues like fire extinguisher inspections,etc. There are several 'reminder' files in use, one for employee items, one for financial deadlines, and another for safety program compliance. I have an excel file with the deadlines and another with the people, or whatever that needs follow-up.
Getting all of the managers to scan the data in the files on time is a real challenge. So, I wanted to automate the process if I could. Sort of a 'nag' feature that would periodically pull up the appropriate file scan it and produce a list of items needing attention. I would probably email myself a copy of the pending items, so I could follow-up with the manager and see what progress was being made. Right now I have to make several calls and there are many times people tell me they'll check and get back to me. Consequently, these delays make my status reports late and I have to update repeatedly.I can setup a macro that executes upon opening of the workbook to list the follow-up items, and I can email the file ok, but I'm not sure how to initiate (trigger) that process. I thought of using outlooks task or appointment reminders and either grab the file and email it to the user or myself.
I'm grasping here... I just don't know how something like this could be done. Any ideas would be greatly appreciated....

Overwriting Existing Excel File -- How To Disable Excel Dialog Box
Overwriting Existing Excel File -- How to disable Excel Dialog box ...?

How To Shade Cell In Excel Based On The Date Value In The Excel Column
Hai ,

I export the content of the MSHFlexgrid to ExcelSheet , Here with i have attached my Excel sheet. The below is the Code i use to export the Excel Sheet. If you see Excel Sheet attached you can find the cells shaded. The code is working fine,

I need a alteration in it. I need to shade the Cell based on the Value in the Column DOffStrm,

if the value in that column is below 30 and the T & I Ets/Ospas date begins with 1 (ex: 3/01/2007)
then the shade should be inside one cell,

suppose the Column DOffStrm is 30 and the Date value of T & I Ets/Ospas is 3/15/2007 then shade should start from half of the current cell and end in the second half of the next cell.

Kindly check this and tell me.

Code:Private Sub cmdExport_Click()
Dim MDur As Integer
Dim obj1 As New Excel.Application
Dim wsheet As Worksheet
Dim wbook As Workbook

Screen.MousePointer = vbHourglass

Set wbook = obj1.Workbooks.Add
Set wsheet = obj1.Sheets(1)
Dim i%
Dim j%
Dim Ce%

For i = 0 To MSHFlexGrid1.Rows - 1
    For j = 0 To MSHFlexGrid1.Cols - 1

        If j > 7 And j < 55 And i > 1 And Len(MSHFlexGrid1.TextMatrix(i, j)) > 1 Then
            MDur = MSHFlexGrid1.TextMatrix(i, 55)
            MDur = MDur / 30
            wsheet.Cells(i + 2, j + 1).Value = Format(MSHFlexGrid1.TextMatrix(i, j), "dd")
            wsheet.Cells(i + 2, j + 1).Font.Bold = True
            obj1.ActiveSheet.Cells(i, j).Font.Color = vbRed
            obj1.ActiveSheet.Cells(i + 2, j + 1).Interior.Color = vbYellow
            For Ce = 0 To MDur

            Next
        ElseIf j > 7 And j < 55 Then
            wsheet.Cells(i + 2, j + 1).Value = MSHFlexGrid1.TextMatrix(i, j)
            wsheet.Cells(i + 2, j + 1).Font.Bold = True
        Else
            wsheet.Cells(i + 2, j + 1).Value = MSHFlexGrid1.TextMatrix(i, j)
        End If
    Next
Next

For i = 0 To 1
    For j = 0 To MSHFlexGrid1.Cols - 1
        wsheet.Cells(i + 2, j + 1).Font.Bold = True
        wsheet.Cells(i + 2, j + 1).Font.Color = &H800000
    Next
Next
Application.DisplayAlerts = False
With obj1.Sheets(1).Range("H2:S2")
    .Select
    .Merge
End With

With obj1.Sheets(1).Range("T2:AE2")
    .Select
    .Merge
End With

With obj1.Sheets(1).Range("AF2:AQ2")
    .Select
    .Merge
End With

With obj1.Sheets(1).Range("AR2:BC2")
    .Select
    .Merge
End With
obj1.Rows(2).HorizontalAlignment = Excel.xlCenter
obj1.Columns.AutoFit
Screen.MousePointer = vbNormal
obj1.Application.Visible = True
End Sub

Thankyou,
Chock.

Want To Write An Excel Macro That Automaticaly Closes Excel When Finished.
HELP!!! I am trying to get excel to close itself down after a macro has run but have no idea how to do it. I can get it to save&close the current spreadsheet but thats it. Can anybody please help?

Reading Values From A Closed Excel Workbook (Excel 97) **RESOLVED**
Morning all,
This is further to an old post.

I am looking to read data values from a closed file and write them to an open workbook. I am hoping to do this not simply value by value but using large ranges.

e.g. On sheet1 of the closed file I need to read values for the range("A1:F100"). e.g. if the closed file were open:

Code: Workbooks(OpenFile).Sheets(Sheet1).Range(A1:F100).Value=Workbooks(ClosedFile).Sheets(Sheet1).Range(A1:F100).Value

a) Does anyone know the code to read values from a closed file (even if value by value)?
b) Is it possible to make this method read large data ranges?

Any help would be great.
Cheers,
M_B

Edited by - M_B on 5/28/2004 4:09:44 AM

Opening An Excel File Along With The Excel Menubar / Toolbar Within A Browser
How do we open an Excel file within a browser window and show the Excel toolbar with it? Any help will be appreciated!

How To Call Excel From Vb And Save The Contents Of Excel Sheet In To Database
Hi folks

I want to retrive the details from Database to excel sheets.After displaying the data in excel sheets from Dataabse, let the user update the record the record and now the updated record should be saved in database

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

How To Hide Group Of Excel Rows From Vb 6.0 While Generating Excel Report ???
hello every one,

i want to hide particular number of excel rows while i am generating excel report from vb 6.0

it is just like what we do in excel --> data(menu)-->group and outline

here in this sub menu we can group particular number of rows/ columns
by selecting rows/columns and then clicking group sub menu
please help me in this

Tricky Situation: Excel With Add-in Crash When Excel Is Losing Focus
I have an add-in for Excel. This add-in is connecting to a server (using CSocket and MSocketSupport) and retrive data for realtime display.

The cells from Excel are getting data by calling a parameterized volatile method from add-in. Each time the add-in socket is notify of data arrival, is calling Application.Calculate to refresh the sheet.

When Excel has focus, everything is fine. But when is losing focus, it crashes.

Any ideas what is going on? Or a hint for places to further digging?

I'm using Office 2000, Windows 2000 Professional SP3 and VB6.

How To Append Rows In Excel Without Opening The Excel Application....?
Hello everybody,

How to append rows in excel without opening the excel application? I've done it in notepad, is this possible in excel?

here's the code for text...

Dim Text As String
text = text1.text
Open "C: est.txt" For Append As #1
Print #1, text
Close #1

I want to do this in excel...... please help......

lexthor

Display Excel In IE And Edit And Save Excel At Server
I need to display an Excel sheet in IE . And Then i want to edit it in IE and save the changed excel sheet back to ther server.

I can view excel in IE by setting
Response.Contenttype="application/vnd.ms-excel"
.I can then edit excel but i don't know how to save changes to the server .
Please help

Closing Access From Excel And Returning Focus To Excel...
I'm using a combination of excel and access functions and in the process of automating this.

I have a button in Excel that runs the following:
__________________________________________
Worksheets.Application.ActiveWorkbook.FollowHyperlink ("C:FILE.mdb")
__________________________________________

FILE.mdb contains an auto exec macro that performs the required functions, including closing access (quit) when finished.

However, it only closes if FILE.mdb is opened manually, when FILE.mdb is called by the excel VBA the process problems occur.

How can I get Access to close once finished and then get the next line in the excel VBA to run?

please help...

cheers.

Whats The Vb Code To Close Excel Within Excel Using A Macro
i need the code to close excel within excel using a macro and saving all of the changes.

please help//

Jas

Loading An Existing Excel To An Excel Sheet On A Form
How can I load Sheet1 of a current Excel program to a sheet object on my VB form? I don't want to load the entire app, just one sheet.

thanx.

Excel 2007 (AKA Excel 12) Good Info Webpage
Hi All

Got an e-mail today on Excel 2007, thought I'd pass this useful link to all of you.

http://www.exceluser.com/solutions/office12srcs.htm

Things I like about Excel 2007 are as follows:

- Over 1 million rows and 16,000 columns in a spreadsheet
- Sort a range by 64 levels (currently of 3)
- The "Defer Layout Update" on Pivot Tables

My apologies if this thread has been created already, couldn't find anything so thought I'd let you all have this link.

Newbie In VBA Excel Needs Help In Copying && Updating Excel Columns
Hi,

I'm a newbie in using VBA Excel and have never use VBA excel before and am totally lost in my problem. Thus, i would GREATLY appreciate a direction or two.

I have 2 worksheets ONE and TWO. Both have the same columns type and column number (approximately 15) with worksheet TWO being the more updated worksheet.

I need to find the rows in worksheet TWO which are not present in worksheet ONE by comparing column A in both worksheets. After which i will copy the whole row from worksheet TWO to worksheet ONE. During the process of comparing column A in both worksheets, i also need to update column C and E of worksheet ONE.

I can do the above without using VBA but i need to create a macro for it to automate the whole process for future uses.

Would appreciate any help greatly! Thanks in advance!

Loading Files In Excel 2000 Vs Excel 2003
Is there something different when loading files using Excel 2000 vs Excel 2003? My code works under Excel 2003, but it doesn't work under Excel 2000. The program crashes.

Here's the code:

Code:
Private Sub Form_Load()

Set xlApp = New Excel.Application
If Dir$("C:PrePaid LogPrepaid-Log.xls") <> vbNullString Then
MsgBox ("Prepaid-Log.xls was successfully found.")
Set xlWB = xlApp.Workbooks.Open("C:PrePaid LogPrePaid-Log.xls")

Else
MsgBox ("Prepaid-Log.xls was not found. A new file will be created.")
Set xlWB = xlApp.Workbooks.Add
End If

End Sub

Please help.

Thanks

Excel Chart - Paste Special As Picture In Excel Itself
Hi All,

I am working on a report and it needs to be generated daily. After generating and creating charts, I need to paste special those charts as "Pictures". Is there any way to do this? And also i need to paste special every single chart in the work book. I already done a code but it not working properly.. and the important point is i need to paste special the chart at the exact position where i intended to cut. Is this possible???

Pls treat this as emergency and advice me on this.

Thanks in advance..

Regards
Mohan

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 Get Rid Of Save File Prompt In Excel With XP Version Of Excel
Hiyas..

At work, I am working on an application to write thousands of records from Access to Excel. Works fine, probably because I'm using a different version of Excel, most likely 2000.. Can't recall offhand for sure.

At home, the same code generates a dialogue box to save the Excel spreadsheet. I have a different version of Excel, I'm assuming it's XP, I can't find out any version info on it.

The code snippet in question is:

Code:
MyXLApp.DisplayAlerts = False 'subdue any Excel specific msg boxes
MyXLApp.Workbooks(1).SaveAs FileName:=PathName 'Save the Excel file
MyXLApp.Quit 'Quit the Excel application
Set MyXLApp = Nothing 'Clear object
At work, this works great.

At home, I get the Save As dialogue box, which won't go away. If I hit cancel, the app crashes. It also says the file is already open, and do I want to Switch to it. That doesn't help, either.

Also, I tried the trick of inserting an if/then statement in the above code to check to see if dir(pathname) existed, and if so, delete it. It wouldn't delete, as it was still open.

So... If anyone knows how to make the above code work in ALL versions of Excel, that'd be great.

Thanks much!
Bill Boxall

Excel In VB

hi, In my vb application i want to send values from vb to excel and draw a graph in excel.This graph should be displayed in vb application. when i run the vb application i want the the graph to have look and feel of vb graph.I mean No where excel file should get open. regardsnandula

hi,

In my vb application i want to send values from vb to excel and draw a graph in excel.This graph should be displayed in vb application.

when i run the vb application i want the the graph to have look and feel of vb graph.I mean No where excel file should get open.

regards
nandula