Visual Basic :: New Excel 2007

How To Open/Read An Access 2007 (accdb) From Excel 2007
Hi All,

My company is moving to Office 2007. I am going through several hundred macros written in Excel 2003 to make sure they work in 2007.
I am trying to open and read an Access 2007 database (extension .accdb) from within Excel VBA.
What used to work was this:

Code:
Dim dbLocal as Database
Set dbLocal = Workspaces(0).OpenDatabase("C:MyDatabase.mdb")
When I run this under 2007 and change the Access database extension to .accdb, I get this error:

Quote:

Run TIme Error 3343
Unrecognized Database Format

I've tried adding extra References in, but no luck. Any help would be greatly appreciated.

Cheers,

Paul

Importing A Date Function From Excel 2007 To Access 2007
hi, I have a major project ahead of me, I am creating a Dbase full of maintenance records in Access 2007. This Dbase is supposed to be able to calendarise routine maintenance and also on-the-spot maintenance so that all our vehicles don't miss out on it. Is it necessary to link Access with Excel so that each time maintenance is performed, Excel tells Access when it needs to be done next time, or is there another way that's much simpler only using Access 2007? I'm new to Access and Visual Basic so simplest explanations are best...thanks in advance.

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.

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.

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.

Excel 2007
I am fairly new to VB and the person who first created the program used it through the same database but used Excel 2003. I am using the same database but except have Excel 2007. Are there any major codeing issues that arise from using 2007 rather than 2003?

Any help will be great!!

Excel 2007
Hi,

I encountered a problem and I was hoping I can get some help. I am using a complicated program that was
build using VB.NET 2002 version and Excel 2003. Now I installed Excel 2007 and I get
"Can not create ActiveX component" - related to the following line

XlApp = CType(CreateObject("Excel._ExcelApplication"), Excel._ExcelApplication)

I have no idea what to do, I am not even sure if the syntax is correct (although is not complaining)

I changed some of the references but still is not working. One thing caught my attention:
In the original code, before I made the modifications, I had a reference called, simply, "Office" (among others).
Now I have all the others (the new versions I hope) but no "Office". I am not a master in VB anyway, so I am lost.
Can anyone please help?

Thanks,
Andrei

Edited by - andrei_popa17 on 11/26/2007 5:29:26 PM

Problem With 2007 Excel
My company is transitioning from 2003 to 2007 Excel and I am now having a problem with a line of code that I have not had with the older version.

I have program "A" (as I will refer to) opening a CSV file full of data and copying any number of lines chosen by the operator to a master file which I will refer to as program "B" (WSmaster).

TotalParts(I) = Cells.CurrentRegion.Rows.Count
Rows(TotalParts(I) & ":" & TotalParts(I) - NumPartsPick + 1).Copy
**As u can see I highlight the WHOLE row or number of rows in program "A" above after pulling the data into program "A" from the csv file

WSmaster.Activate
Sheets(Blockhead & "op" & OPCODE + I - 1 & PRGLNTH).Select
Rows("8:8").Select
With Selection
Selection.Insert Shift:=xlDown 'This line no longer runs!!
End With
**I then activate program "B", select row 8:8 and when it gets to the insert line it errors out saying that the copy area and paste areas are not the same shape (runtime 1004). BUT they ARE!? I'm trying to paste the whole row after highlighting and copying the whole row. Even trying this manually yields the same error. It seems to me that this may be a glitch.

Please advise
Pnut

Removing An Excel 2007 Add-in
I imported an Excel 2003 .xls and saved it in Excel 2007 as an .xlsm and .xlma

Since the .xls had a toolbar attached, the tools from the toolbar show up under the add-ins tab in Excel 2007. Now I would like to remove the .xlma file and get rid of this add-in. I can't seem to do it though.

If I goto Options>Add-Ins, I can see the path is:
c:usersjoeAppDataRoaming ..... oolbar.xlam

using exlporer, I cannot see the AppData beyond c:usersjoe

So what do I do now. Why can't I see AppData with explorer.

Is there an easier way to do this?

Excel 2007 VBA Question
For use in Excel 2007 VBA, is there a similar statement I could use to mirror what #region - #end region statements do in Visual Studio?

Thanks for your help!

Problems With Excel/VBA 2007
I have a spreadsheet with macros that worked fine with Excel 2003. Now when I open the spreadsheet I get the following:

"Excel found undreadable content in ... Do you wish to recover contents ..."

To which I reply Yes. Then I get:

Removed Records: Sorting from /xl/worksheets/sheet2.xml part

Then after I save the sheet, exit, and try to reopen it, I get the same thing all over again.

Any ideas what is going on and how I can get rid of this annoyance?

Thanks,
JoeK

Excel 2007 PageSetup
Apparently the .PageSetup options have dramatically changed in Office 2007...

Im using a program i wrote which exports data to Excel and worked fine in non 2007... but these options

With oExcel.Worksheets(1).PageSetup
.HeaderMargin = 0
.FooterMargin = 0
.TopMargin = 0
.BottomMargin = 0

etc... no longer work... i don't know if they need to be set a different way, if the worksheet is somehow locked or what... the auto list also does not list any of the PageSetup properties.... can anyone help me here....

im editing this but leaving what ive previously wrote... apparently when exporting the excel object from visual basic into office 2007 most of the page layout optoins are locked... therefore causing routine failure... gay

CommandBars In Excel 2007
Hello,

I'm wondering if somebody could help me with a problem on CommandBars in Excel 2007. I have a code that add a RMC control item to a shape:

If Not Application.CommandBars("Shapes").Controls.Item(1).Caption = "Show Bus Connections..." Then
With Application.CommandBars("Shapes")
With .Controls.Add(Type:=msoControlButton, before:=1, Temporary:=True)
.Caption = "Show Bus Connections..."
.OnAction = "GrowBusDiagram"
.BeginGroup = False
.Visible = True
End With
End With
End If

This code works fine with Excel 2003, but doesn't in 2007. Is there any easy way to make it work for 2007?

Thanks a lot,
Julia

VB Errors In EXCEL 2007
A spreadsheet application written in EXCEL 2003 using the visual basic editor in EXCEL runs fine. When taking the same application to EXCEL 2007, errors relating to Range and Select occur. The same errors occur whether EXCEL 2007 is running under XP or Vista. When stepping through the program using the F8 key, the errors don't occur. Running in a normal fashion, the errors occur. The security level is set LOW and Windows Classic is the theme. Any ideas what may be happening?

Excel 2007 - Button Name
I have a chart on a sheet that has some buttons to which macros are assigned. I need to reposition the buttons occasionally. In Excel 2003, the buttons were treated as 'shapes' and I could simply run through all the shapes, find the button in question by its name, and move it. Excel 2007 apparently does not treat the button as a shape. How can I find the particular button (e.g. name is "Do Something") in question? Here's the code I have been using. When I execute it in Excel 2007, I get a 'not found' error.

HTML Code:

ActiveChart.Shapes("Do Something").Left = 450

Thanks.

VB 6 With Excel 2007 Error
Installed VB6 application on user's PC which runs MS Office 2007.  Application tries to produce an excel file.  I get "Run-time error '1004', Method '~' of object '~' failed"
when trying to save excel file in format "xlExcel9795".
(See ** for code that causes the failure.)
fyi - this code works with previous versions of Excel.

  If OpenExcel = True Then
    With xlApp
      .ActiveWindow.ScrollColumn = 1
      .Cells.Select
      .Selection.Columns.AutoFit
      .Range("A1").Select
       strMsg = "(5) After Last Column/Row! "
**     .ActiveWorkbook.SaveAs FileName:=strYEApath,
**        FileFormat:=xlExcel9795, CreateBackup:=False
    End With

Is there another FileFormat I should be using?  Is there something I need to set/change in Excel 2007?  Is there some  kind of download that would fix this?

TIA,

Excel 2007 VBA Not Seeing Some Form Controls
Hi All,

My VBA code from Excel 2003 does not work entirely when run in 2007. I need to manipulate some control objects, like assigning the range to a drop down combobox control. The programme errors when it says it cannot find that control on my chart sheet.

When I run this little bit of code to list all of the controls on my sheet, it is missing 4 ComboBoxes and 2 CommandButtons.

Code:
Dim SH As Shape
Dim i As Long
i = 1
For Each SH In Sheets("PlanBase&Incremental").Shapes
Sheets("Sheet1").Range("A" & i) = SH.Name
i = i + 1
Next
If I add textboxes to the sheet and run this code again, it sees them. IF I add another combobox to the sheet and run this code, it does not see it.

What gives?

I should mention that this is a chart, not a worksheet. However, it works correctly in Excel 2003 and "sees" all the objects on the chart.

Why are these objects not visible to VBA?

Cheers,

Paul

VB6 Dll Giving Error In Excel 2007
Hi All,

I have one DLL developed in VB6 which is working fine with excel 2003 sp2. i am converting my application to windows vista/office 2007. When i used same excel workbook to call dll i am getting error window " error dispalying dialogs.unable to proceed." This DLL is used to open different forms and do the data transfer inside the appliation and excel.

Can somebody tell me how to solve this.

Regards,
Urbunti

VBA In Excel 2003 And 2007 Problem
I have a few VBA routines which are written into an Excel spreadsheet. My application has us working with both Excel 2007 and 2003 depending on the machine, and everyone needs to be able to make these routines work. I am having some problems with my routines and I would appreciate any help anyone could give to resolving them!

first of all the routine runs from a button click on one Excel spreadsheet. it then opens another sheet which is a template at a defined location, performs a saveAs to move that template to the output location, then fills the template with information from the first sheet. all was fine in Excel 2003. when I went to Excel 2007 I found that it does not save in the correct format! We have set the default save format in Excel 2007 to the 2003 format for compatibility with everyone else. when I do the saveas command I give it a .xls extension, however it appears that XL2007 insists on saving in the xml based xlsx format even though there is an .xls extension on it. others on the outside cannot open these files as their excel says that it is not formatted correctly. I looked into this problem and found that there is a FileFormat parameter to the SaveAs command. I specified format 56 which is the excel 97-03 format. everything works great in 2007, however when a 2003 machine attempts to run the code, it blows up on this line giving a generic I can't do that kind of error message. removing the specified format fixes the problem on 2003, but returns the previous problem in 2007. what can be done? is there a way I can identify the version of excel and run a specific piece of code depending on which version I'm running?

Addin Ribbon UI+Excel 2007
I've created a ribbon UI for my Addin and Its appearing as a Seperate tab in my Excel 2007 application(Say tab1).I created 2nd addin and ribbon UI.My requirement is, whenever i select the 2nd addin it should sit under the first addin(i.e under tab 1).can i do this via coding??

Thanks in Advance...

Excel 2007 Table Arrays
Sorry for the long question; I've been doing some searching but I am having trouble finding or understanding info for tables in the newer Excel.

As an example, I have a table that has columns for Company, Salesperson and then a column for each date, say 1/1/2007, 1/2/2007...etc. It's possible that I could specify the number of dates in advance but ideally they would be able to add the next day to the end of the table.

For each entry in the table I am going to want to toggle between the data showing number of orders and the cost so the table is only showing one set of data at a time. The easiest idea I had in terms of programming would be to have the data columns side by side and just hide/unhide one of them depending on the view. However the developers that have the template set up to load into another system have turned this idea down. The next idea I had was to have two hidden sheets that have the same table but with the different sets of data. Then when the user hits the toggle button it would just copy the other table to the template sheet. This works fine except that as users add more rows, or possibly columns to the table I am not sure how to mirror that on the other sheets.

The idea I was thinking about now would be to load the table into two arrays and when you hit the toggle button it would load the other array into the table. That way table could easily grow and the data would also be stored in a good way to be able to load it into another system. I know how I could do this with a set number of rows and columns, but I am hoping that the table features of 2007 and/or just the normal named range features might make this easier and more robust. Any help at all is appreciated, but if this is even possible I guess some of my main questions are:

1. Is there any property that would let me get the dimensions of the table (# rows & columns and maybe the address of the starting point) just from the table name?
2. Since only the data under the date columns would be changing, is it easier to store just those columns in the array or is it better to store the entire table?
3. If a user adds a row is there any sort of "table change" event that could trigger the redim of the array? If not, is there a worksheet_change event that could tell if the target intersects the table?
4. I am assuming that the array should not be reloaded into memory every time the table changes, but only when you toggle or maybe save the file, correct? Otherwise it would slow it down considerably?

That would probably be enough to get me started. Of course all help is greatly appreciated.

Thanks!

Customizing The Excel 2007 Ribbon Using VB6?
I read Customizing the Excel 2007 Ribbon using VBA with great interest.

Does anyone know how to do this for a VB6 based Excel COM Add-In?

Is there a change one can make to the existing designer code to implement the extra interface required by the Ribbon?

Late Binding With Excel 2007
Hello, I need to make sure that the code you can find in the attached source files works with Excel 2007.

Unfortunately, I don't have Office 2007 installed on my PC, so I can't test it myself.

The attached code simply creates an Excel worksheet programmatically and works just fine with Office XP. (I've tested it under Windows XP)

So, if you are willing to help me and you are using Office 2007, you should just click on the Export button that you will find at the bottom of the form.

If you get an Excel sheet with some captions on the first row cells, then the code works.

I would also very much like to know whether that code works under Vista and Office 2007.

Any help will be greatly appreciated.

Vb Code Excel 2007 To Excel Xp
Hi all,

Hopefully there is somebody around who can help me out. I have a macro that functions as it should in excel 2007, but I need to port it to excel xp. I get an error code regarding the array in the filter. The code is listed just below. Anyone any suggestions? Thanks in advance!

Danny

Code:
Sheets("Temp").Select
Selection.AutoFilter
ActiveSheet.Range("A:U").AutoFilter Field:=1, Criteria1:= _
"Europe, Middle East & Africa"
ActiveSheet.Range("A:U").AutoFilter Field:=2, Criteria1:=Array( _
"Estonia", "Israel", "Netherlands", "Slovak Republic", "Ukraine"), Operator:= _
xlFilterValues
ActiveSheet.Range("A:U").AutoFilter Field:=6, Criteria1:="=Close-Out", Operator:=xlOr, Criteria2:="=Interim Monitoring"
ActiveSheet.Range("A:U").AutoFilter Field:=9, Criteria1:=Array("All Signatures Received", "MVR Internal Approval", _
"MVR Submitted for Review", "Visit Completed", "Visit Confirmed", "Visit Planned", "=", "Pre-Visit Letter Sent", "Follow-up Letter Sent", "MVR Submitted for Revision"), Operator:=xlFilterValues

With ActiveSheet
.UsedRange
lLastRow = .Cells.SpecialCells(xlCellTypeLastCell).Row
Set Rng = Range("A1", Cells(lLastRow, "A"))
Rng.SpecialCells(xlCellTypeVisible).EntireRow.Copy
.UsedRange
End With

'Set Data Sheet and fill with appropiate data
Sheets("Source").Select
Range("A1").Select
ActiveSheet.Paste

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

Thank you.

2007-2003 Excel ATP Compatibility (#N/A Problems)
there seems to be a bug with excel that screws up certain functions when the file is created in excel 2007 and subsequently saved in 2003-accessible format. it appears to be happening because some of the functions that used to be available only via analysis tool pack add-in are now standard in 2007 excel. the complete list of affected or possibly affected functions is here: http://support.microsoft.com/kb/912719. fortunately, there is a work around either via other functions in excel or via a user defined function in vba for some of them. the two that i had to deal with are below. hopefully this is helpful to others as well and will save you a little headache.

eomonth(A1,B1)

excel workaround: =DATE(YEAR(A1),MONTH(A1)+B1+1,1)-1

vba workaround:

Function EOM(startDate As Date, months As Integer)
EOM = DateSerial(Year(startDate), Month(startDate) + months + 1, 1) - 1
End Function

networkdays(A1,B1)

excel workaround: =SUMPRODUCT(--(WEEKDAY(ROW(INDIRECT(A1&":"&B1)),2)<6))

vba workaround:

Function NWD(startDate As Date, endDate As Date)
NWD = 0
Number_of_Days = DateValue(endDate) - DateValue(startDate)
For n = 0 To Number_of_Days
Weekday_Number = Application.WorksheetFunction.Weekday(DateValue(startDate) + n, 2)
If Weekday_Number < 6 Then
NWD = NWD + 1
End If
Next n
End Function

Application.FileSearch And Excel 2007 Problem
Hello everyone,

I was hoping someone could help me with I problem i have.. I am no expert on VBA, my knowledge is very basic, but I have managed to use some examples i found in the Excel help to make some macros that really help me at work. The thing is that when they changed our office package from 2003 to 2007 these macros stopped working. I've searched the web up and down with no luck to find a substitute for the code that's been giving me trouble. The problem lies with Application.FileSearch that, according to what i've read on many forums, is no longer supported on the 2007 version.

I had two macros, one of them opened excel files starting with the name "F1_*.xls" on a given directory, updated, saved and closed, then went to the next file and did the same thing untill there were no more, this was the code....

Sub ACTUALIZAR()

Set fs = Application.FileSearch
With fs
.LookIn = "F:shareControl FinancieroActividadesF12007 2008"
.Filename = "F1_*.xls"
If .Execute > 0 Then
For i = 1 To .FoundFiles.Count
Cells(i + 0, 1) = .FoundFiles(i)
Workbooks.Open Filename:=Cells(i + 0, 1), _
UpdateLinks:=3
ActiveWorkbook.Save
ActiveWindow.Close
Next i
Else
End If
End With
End Sub

The other macro had the same code as the first but (without the opening, updating and saving of files) just listed all excel files on a worksheet starting with the name "F1_*.xls" along with their path. This was the code...

Sub ACTUALIZAR()

Set fs = Application.FileSearch
With fs
.LookIn = "F:shareControl FinancieroActividadesF12007 2008"
.Filename = "F1_*.xls"
If .Execute > 0 Then
For i = 1 To .FoundFiles.Count
Cells(i + 0, 1) = .FoundFiles(i)
Next i
Else
End If
End With
End Sub

I would be very very grateful if someone could help me replicate this proceedure on Excel 2007..
Thank you all...

Export Data From Excel Into Access 2007 ??
What's up Crew?

I'm having trouble opening my access data base 2007 with my excel vba macro. I'm trying to open it and write into it. I get the Run-time error '3343'
"Unrecognized database format"

Greatly appricate the help!

Here is my code:

Sub ExcelToAccess()
' exports data from the active worksheet to a table in an Access database
'Microsoft DAO x.xx Object Library

Dim db As Database, rs As Recordset, r As Long
Dim tName, dbPath

dbPath = ThisWorkbook.Path & "Test2007.accdb"

' open the database

''''Error happens at this point''''''''''''''''''''''''''
Set db = OpenDatabase(dbPath)
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''' ''''''''''''''''''''

tName = Range("A1").Value
'Set rs = db.OpenRecordset("E01", dbOpenTable)
Set rs = db.OpenRecordset(tName, dbOpenTable)
' get all records in a table
r = 2 ' the start row in the worksheet
eRow = Range("C65536").End(xlUp).Row
For r = 2 To eRow
'Do While Len(Range("A" & r).Formula) > 0
' repeat until first empty cell in column A
With rs
.AddNew ' create a new record
' add values to each field in the record
.Fields("Test ID") = Range("B" & r).Value
.Fields("Module ID") = Range("C" & r).Value
.Fields("Module Config") = Range("D" & r).Value
.Fields("MNotes") = Range("E" & r).Value
.Fields("Date Received") = Range("F" & r).Value
.Fields("From") = Range("G" & r).Value

.Fields("Import Date") = Range("H" & r).Value
.Fields("Export Date") = Range("I" & r).Value
.Fields("CH") = Range("J" & r).Value
.Fields("Spire1 Date") = Range("K" & r).Value
.Fields("HiPot-D Date") = Range("L" & r).Value
.Fields("V1") = Range("M" & r).Value
.Fields("I1") = Range("N" & r).Value

.Fields("V2") = Range("O" & r).Value
.Fields("I2") = Range("P" & r).Value
.Fields("V3") = Range("Q" & r).Value
.Fields("I3") = Range("R" & r).Value
.Fields("Spire2 Date") = Range("S" & r).Value
.Fields("HiPot-W Date") = Range("T" & r).Value
.Fields("V4") = Range("U" & r).Value
.Fields("I4") = Range("V" & r).Value
.Fields("Spire3 Date") = Range("W" & r).Value
.Fields("PTNotes") = Range("X" & r).Value
.Fields("EnV Test") = Range("Y" & r).Value
.Fields("Location") = Range("Z" & r).Value
.Fields("Position") = Range("AA" & r).Value
.Fields("InOut") = Range("AB" & r).Value
.Fields("Date On") = Range("AC" & r).Value
.Fields("Time On") = Range("AD" & r).Value
.Fields("Date Off") = Range("AE" & r).Value
.Fields("Time Off") = Range("AF" & r).Value
.Fields("CyclHrs") = Range("AG" & r).Value
.Fields("Sched CyclHrs") = Range("AH" & r).Value
.Fields("Sched Date") = Range("AI" & r).Value
.Fields("ETNotes") = Range("AJ" & r).Value
.Fields("Excluded") = Range("AK" & r).Value

' add more fields if necessary...
.Update ' stores the new record
End With
r = r + 1 ' next row
If r = 65536 Then
'Exit Do
Exit For

End If
Next
'Loop
rs.Close
Set rs = Nothing
db.Close
Set db = Nothing
End Sub

Upgrading My .XLA Addin To Also Work With Excel 2007
I read in the forum on customizing the Ribbon.
I developped an add-in in Excel (.xla) that is triggered from my accounting program using COM. I use the built in VBA editor.
Since I'm a control-freak, I disabled the Excel menu's and toolbars, and replaced them with my own, but in Excel 2007 this has become a lot more difficult.
How can I add Ribbon functionality to an existing .xla source while maintaining compatibility for earlier versions of Excel.
Is there an article somewhere, where I can see how this works ? I hope you can help me with this, since my customers are pushing me for Excel 2007 compatibility.

Excel 2007 - What's Happened To 'Application.Screenupdating'?
Hi there

I've recently 'upgraded' to Excel 2007 and found that the trusty old code of 'Application.Screenupdating = False / True' no longer appears to be so trusty. What I mean is that, when executing various coding, it no longer appears to work.

Can anyone enlighten me as to what is going on?

Many thanks

Excel 2007 - Protected Sheet - Use AutoFilter
Checking the 'Use Autofilter' in the 'Protect Sheet' dialog box does not work. The data filter icon is greyed out. Is there a way to allow users to do data filtering on a protected sheet? Thanks.

Excel 2007 - Get Latest File In Folder
FileSearch does not seem to work in 2007. Can someone please provide some VBA code example where I can get the datetime of the latest file in a specific folder? Thanks.

OLAP Cube Wizard In Excel 2007
Hi all,

I have just discovered Excel Data Cubes (I save as a CSV File then create an OLAP Query File), this massively reduces the amount of memory used to store the data and I can easily access through Pivot Tables.

I was looking at creating some memory critical reports using Data Cubes until I happened upon the msdn site below. I am currently on Excel 2000 and don't have any other software I could use eg MS Access etc.

Question

Am I reading the link below correctly and in Excel 2007 I won't be able to create an OLAP Query File? (or was possibly going to learn how to save as .cub file).

http://blogs.msdn.com/excel/archive/2006/08/24/718786.aspx

Any help you can give is much appreciated.

How To Save A Excel 2007 Spreadsheet As A DBF File?
Hi Friends,

I am very much cofused to save a Excel 2007 file as a DBF (IV) file. Till Excel 2003 it was possible by Save As. But in Excel 2007 - in Save As - the DBF file type is not appearing.

Please guide / suggest me in this regard.

scsishere.

Excel 2007 Cannot Send Form Control To Back
Hi All,

In Excel 2003, I often place a rectangle shape over a ComboBox form control so that I can manipulate the font size, colour, font, etc.. I usually size it to fit exactly over the ComboBox, leaving only the drop-down arrow visible.
With the rectangle shape, in the formula bar, I reference a cell that contains the selection the user would make with the drop-down.

So far so good. I use the "Send to Back" on the ComboBox, or the "Bring to Front" on the rectangle shape to get the rectangle shape to sit atop the ComboBox.

In Excel 2007, I cannot seem to place anything on top of the ComboBox. The "Send to Back" or "Bring to Front" will work between shapes and text boxes, but once I try to put anything on top of the ComboBox, it won't work.

Is there a way to do this or is this one of the new "mysteries" of Excel 2007?

Any help will be appreciated.

Cheers,

Paul

Create Excel 2003 Workbook From Access 2007
Hi All,
Can't find help on this anywhere at all.
I'm using late binding 'Set oExcel = CreateObject("Excel.Application")' to create an excel workbook, but it needs to be in version 11 (2003) not 12 (2007) for the recipients to view.
Anyone have ideas on how to do this programmatically?
Cheers
Brian

To Display A Face Id In The Ribbon Item In Excel 2007.
Hi All,

We were using a menu in the previous versions of Office. To display the icon to a menu item I used face id property. Now for Office 2007 the menu's have been replaced with Ribbon.

Please let me know how I can load the icons using face id value to a ribbon item.

Thanks & Regards,
Abhi Ram.

Scroll Wheel In Listbox Hangs In Excel 2007
I have the following problem with Excel 2007 in Compatibility Mode:

In my sheet I have a standard ActiveX listbox (as found in the Developer tab of the Ribbon under the insert option). If I activate/give focus to the listbox and try to use my scrollwheel to scroll through the list, Excel 2007 hangs directly (i.e. ‘Microsoft Office Excel has stopped working’ with the option ‘Restart the program’).

Is there an easy workaround/fix for this? I tried looking around for and answer on the forum and elsewhere, but I have yet to find an answer.

Thanks for any help!

Saving An Excel File 2007 Vs Prev Versions
I was saving an Excel file in VB6 using the below code with Excel 2007 installed on my PC.

xl.ActiveWorkbook.SaveAs "C:GridEmail.xls"

Well there was a problem with previous versions of Office trying to open the file. It seems like my program saved it as a xls file with the properties of 2007 (xlsx). So to remedy this issue i changed the code to the following

xl.ActiveWorkbook.SaveAs "C:GridEmail.xls", FileFormat = xlExcel8

When i step through the code, it works perfectly. But it wil not compile because it doesn't recognize "FileFormat".

I create the object at run time:

Set xl = CreateObject("Excel.Application")

i think this is why i can step through the code fine. I do not want to create a reference to Excel which I'm sure would fix the problem. Any clue how to correct this without a reference?

Excel 2003 / 2007 Differences: OnAction Accessing Macro Sub In Another Workbook
In Excel 2003, I used the OnAction property to assign a public macro sub to a shape object that was in a different workbook than the macro sub:

Code:
Selection.OnAction = "'pm.ClickedShape " & looper & "'" ' See http://www.exceltip.com/st/Pass_arguments_to_macros_from_buttons_and_menus_using_VBA_in_Microsoft_Excel/424.html
This works fine in Excel 2003. In Excel 2007, when I click the shape object, nothing happens, and via the debugger, I see that the pm.ClickedShape sub that is in another workbook than the shape is not even called.

The problem isn't with the passed parameter. If I try to call a public macro sub without a parameter in a workbook other than the shape object, I get an Excel error, "Cannot run the macro 'NoParmMacro'. The macro may not be available in this workbook or all macros may be disabled."

If I change the OnAction reference to refer to the filename of the workbook that contains the macro that I want to call, it will work in Excel 2007:

Code:
Selection.OnAction = "'really big long filename.xls'!'ClickedShape " & looper & "'"
I don't know if I can trust my users to not rename the file that has the macro sub in it. That original file (with the macro in it) dynamically creates the workbook with the shape object in it, to which I want to call back to the original workbook. In Excel 2007, can I set the OnAction property of the shape object in the dynamically created workbook to refer back to something other than a hard-coded filename? Like I say, this worked fine in Excel 2003; I would simply use the module name ("pm" in the above code). Why did this change in Excel 2007?

Thank you for your help.

Routing In 2007
I know that the feature was removed from the menus but the MSDN says the method is still available.
http://msdn.microsoft.com/en-us/library/bb177975.aspx

So I route something and then I have a button for when the next person opens it that is just:

Code:
awname = ActiveWorkbook.Name
Workbooks(awnamee).Route
I have also tried ActiveWorkbook.Route and ThisWorkbook.Route

Now this works fine in 2003 but in 2007 the method fails with a 1004 error I believe.

Any suggestions?

VBA For Access 2007
Hope this is the right forum. I have a lot of VB experience, but am new to VBA, especialy Access 2007.

Have managed to create my tables, and an initial form, but I need a head start as to how to write some VB code. And to have access to a toolbox to add controls to the form. Once that is known to me, the next thing is how to perform SQL statements against the host DB.

Office XP Vs 2007
SCENARIO
Some years (2004) ago I developed some macros (forms, modules, etc) to automate some proceses using Word 2002, Excel 2002 & Access 2002. The programs used to run in a PC with Office XP, now the there is a new PC with OFFICE 2007 instead.

THE PROBLEM
Seconds after the macro starts running it stops with the msg
"Code excecution has been terminated" I click on debug and run the macro with F8 and no error is found

THE QUESTION
Why would the macro stop if no error is ecountered (and no one has press Ctrl+Break as the so useful help indicates is the cause of the problem)?

Outlook 2007
I have been sending emails daily, from my production system, using a VB6 app with a package called HTML Mailer Plus. Suddenly, mu customers are telling me that they are not receiving their daily email. The only change that I can see is that I upgraded from Outlook 2003 to Outlook 2007. I am assuming that HTML Mailer does not work with Outlook 2007, even though I get no error mesages.

My project does reference MS Office 12, where before it was version 11.

So, I guess now is the time to break away from 3rd party code. I am going to try this code:

Dim olapp As Object
Dim oitem As Object

Set olapp = CreateObject("Outlook.Application")
Set oitem = olapp.CreateItem(0)
oitem.Subject = "Daily Activity Report"
oitem.To = "fred@flintstone.com; "
oitem.Body = "Attached is the Daily Activity Report"
oitem.AttachFile ("Z:DADFaxesFax49.txt")
oitem.Send
olapp.Close
Set olapp = Nothing
'==========================

Do you see any reason why this will not work with Outlook 2007?

===edit

This is the old code

Dim objMailer As New FlexiMailer

objMailer.ConnectToServer ("smtp.hbci.com")
objMailer.From = "brendaanderson@technigraph.net"
objMailer.Subject = "Daily Activity Report"
objMailer.Body = "Attached is the Daily Activity Report"
objMailer.AttachFile ("Z:DADFaxesFax2.txt")
objMailer.AddMultipleRecipients fmTo, "fred@flintstone.com"
objMailer.SendMessage
objMailer.Disconnect
Set objMailer = Nothing

== more edit

it does not oitem.From and oitem.Attachfile. I get 'object doesn't support this method' errors. Argh...

Access 2007 VBA
Does anyone know of a link to where I can get started with Access 2007

Wha-a-a-a-t Is The Office 2007...
...object procedure--using VBA--for grabbing a chart that Excel has dumped onto the sheet after creating it and, after activating that ChartObject:

1. expanding the base chart object in the horizontal and vertical dimensions?

2. moving the chart object to another part of the same sheet?

(The whole object model has changed, and MS doesn't seem to have at the least even documented how the resizing and same-sheet moving is done. The capacity for that may not even be preserved from pre-2007.)

(By the way, the Office 2007 UI is a catastrophe.)

Thanks in advance.

New Excel 2007

I have a VB app that reads MS Excel files and imports selected fields into an access database.Some of my clients have upgraded to the new Excel 2007. What do I need to do within VB to ensure that the 2007 files can be read and processed?TIA for your assistance.jDadWilson

I have a VB app that reads MS Excel files and imports selected fields into an access database.

Some of my clients have upgraded to the new Excel 2007. What do I need to do within VB to ensure that the 2007 files can be read and processed?

TIA for your assistance.

jDadWilson