PDA

View Full Version : import outlook calender automaticly using vb



rotemblu
05-17-2007, 10:49 AM
I am trying to import outlook calender automaticly using vb
I have found this site
that has the macro

ImportCalendarData

I try to run it but I get syntax error at the row

With ActiveSheet.QueryTables.Add(Connection:=Array( _
"OLEDB;Provider=Microsoft.Jet.OLEDB.4.0;Password=" """;User ID=Admin;Data

and a yellow arrow is pointing at the top line(sub impo...)

what can be the problem?

Simon Lloyd
05-17-2007, 10:55 AM
Cross Posted (http://www.excelforum.com/showthread.php?p=1785319#post1785319) also here (http://www.ozgrid.com/forum/showthread.php?p=358535&posted=1#post358535)

Bob Phillips
05-17-2007, 11:09 AM
and a yellow arrow is pointing at the top line(sub impo...)



and you don't even bother showing us the offendling line(s).

rotemblu
05-17-2007, 11:52 AM
sorry I will try to be more specific now
the macro is here


Sub ImportCalendarData()

'Produced by Kitty using Record A Macro in Excel Tools
'This macro automatically imports the calendar data exported by Outlook
'To the file CurrentCalendarData
'NOTE: There are three instances of the file name and path
'If you use a different file name or a path other than C:\ you need to change that code

With ActiveSheet.QueryTables.Add(Connection:=Array( _
"OLEDB;Provider=Microsoft.Jet.OLEDB.4.0;Password="""";User ID=Admin;Data
Source=C:\CurrentCalendarData.xls;Mode=Share Deny Write;Extended " _
, _
"Properties=""HDR=YES;"";Jet OLEDB:System database="""";Jet
OLEDB:Registry Path="""";Jet OLEDB:Database Password="""";Jet OLEDB:Engine Ty" _
, _
"pe=35;Jet OLEDB:Database Locking Mode=0;Jet OLEDB:Global Partial Bulk
Ops=2;Jet OLEDB:Global Bulk Transactions=1;Jet OLEDB:New D" _ ,
_ "atabase Password="""";Jet OLEDB:Create System Database=False;Jet
OLEDB:Encrypt Database=False;Jet OLEDB:Don't Copy Locale on Compa" _
, "ct=False;Jet OLEDB:Compact Without Replica Repair=False;Jet
OLEDB:SFP=False") _ , Destination:=Range("A1"))
.CommandType = xlCmdTable
.CommandText = Array("Calendar")
.Name = "CurrentCalendarData"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.PreserveColumnInfo = True
.SourceDataFile = "C:\CurrentCalendarData.xls"
.Refresh BackgroundQuery:=False
End With
End Sub



Sub ForgetThePast()
'This macro was written by EE expert brettdj
'It deletes every row for which the date is today or earlier
Dim Myrange As Range
Rows(1).Insert
Range("A1:B1") = "Dummy"
Set Myrange = Intersect(ActiveSheet.UsedRange, Columns("A"))
If Myrange Is Nothing Then Exit Sub
Application.ScreenUpdating = False
With Myrange
.Offset(0, 1).Columns.Insert
.Offset(0, 1).FormulaR1C1 = "=IF(AND(ISNUMBER(RC[-1]),RC[- 1]<=NOW()),""kill"","""")"
.Offset(0, 1).AutoFilter Field:=1, Criteria1:="kill"
.EntireRow.Delete Shift:=xlUp
.Offset(0, 1).Columns.Delete
End With
ActiveSheet.Range("a1").Select
Application.ScreenUpdating = True

End Sub



Sub CloseEmAll()
'This macro was copied by Kitty from an example in
'Microsoft Developers Network help
'It closes all the open workbooks except the current workbook
For Each w In Workbooks
If w.Name <> ThisWorkbook.Name Then
w.Close savechanges:=True
End If
Next w

End Sub

I am folowing all the instruction from the page
I took it from(kitty great calender project)(sorry can't post links)
but with no success

Simon Lloyd
05-17-2007, 12:15 PM
you probably need to set the references for the Microsoft.Jet object