[VB6][Excel2003] Access File openen vanuit Excel macro

[Gulf_5]

Hoi allemaal,

twijfelde of dit topic thuishoorde in OFF of P&W, maar vorige keer was ie hiernaartoe verplaatst, dus hoop dat het goed is zo.

Situatie:
Ik heb een aantal xls files die door middel van complexe macro's (geschreven in VB) worden voorbereid voor het inlezen naar een access-file waarna ze in een SQL server worden opgenomen.
Ik heb nu een Excel-file gemaakt die dient als een soort switchboard, omdat het gros van de handelingen in Excel plaatsvindt, en van waaruit alle macro's worden aangestuurd.
Het lukt om alle Excel-based macro's op te starten door middel van macrobuttons, maar het .mdb bestand opstarten lukt nog niet... Het is de bedoeling dat door een druk op de knop een .mdb wordt opgestart (in die .mdb zit een autoexec macro die weer een module start waardoor de hele riedel vanzelf doorgaat.)

Ik heb al diverse GoT-topics doorgespit, maar ik kom niet verder dan DoCmd, Shell etc met foutmeldingen, terwijl dit helemaal niet zo ingewikkeld zou moeten zijn... gewoon iets in de trend van OpenFile "filename.mdb",3(maximized focus is dat geloof ik) of iets dergelijks.
Hoop dat iemand me kan helpen. thanks !

[Boss]

Volgens mij moet je bij dat soort commando's wel het volledige pad naar het bestand opgeven.

Anders kan je ook in Excell VBA de Access library 'activeren' (extra -> verwijzingen) en dan via code een Access database definieren en openen.

[Gulf_5]

sowieso gebruikte ik al het volledige pad, het bestand moet namelijk wel uitwisselbaar blijven tussen de verschillende computers @ hier.

in VBA de Access Object for Office 11 library geactiveerd, maar het lukt me nog altijd niet om de file geopend te krijgen. Via "workbooks.open filename etc..." wordt de mdb geopend in excel zelf. Via shell wil ook niet. Heb je misschien (of iemand anders) een voorbeeldje van een code hiervoor?
BVD

Edit: bij het uitvoeren van de macro met commando Shell("G:\pad\filename"),3 krijg ik de melding:
Run-time error '5': Invalid procedure call or argument


EDIT:
Ik ben er in geslaagd om een work-around te creeren, dus het werkt nu wel.
Maar ik ben nog steeds geinteresseerd in een VBA-code die dit van mijn work-around overneemt.

[ Voor 34% gewijzigd door Gulf_5 op 15-11-2005 11:30 ]

[Boss]

Je wilt ook geen Workbook openen, dat is namelijk een Excel onderdeel.

Even een klein opzetje:

Visual Basic:

Sub Test()
Dim oAcc As New Access.Application
  oAcc.OpenCurrentDatabase "c:\MijnDatabase.mdb", False
  'Doe je Access ding
  oAcc.CloseCurrentDatabase
End Sub

[sanfranjake]

Misschien is het handig om even het stukje code wat niet werkt hier te plaatsen, zodat we met je mee kunnen kijken
En ja, VBA wordt vrijwel alleen voor Office gebruikt, vandaar dat het in Officesuites en -software hoort. Zie .oisyn in "Nieuw subforum: Officesuites en -softwar..." voor iets meer uitleg.

[Gulf_5]

@ Boss, bedankt voor je code, het is me alleen niet gelukt om m werkend te krijgen, ws omdat ik er teveel tussen heb gepropt aan handelingen.

@ Sanfranjake, zou t stukje code wel willen plaatsen als ik m niet al heel erg hard had weggegooid

Hoe dan ook, ik zal even uitleggen wat mn workaround is geworden. Ik heb een icon gemaakt en daar een hyperlink onder gehangen die het bestand nu opstart. In de Access file heb ik een Macro gemaakt die AutoExec heet, en die als eerste regel RunCode Autoexec heeft. Daardoor wordt de module genaamd AutoExec gestart. En die module is gewoon in VBA gemaakt, waardoor ik het allemaal weer een beetje begrijp

Nu heb ik alleen wel weer een ander probleempje. Als ik de AutoExec macro start vanuit Access zelf, gaat alles goed en wordt na het DoCmd.Quit commando Access braaf afgesloten. Maar als ik Access start vanuit Excel, wordt wel de database uiteindelijk afgesloten maar blijft het Access hoofdvenster gewoon open staan en komt er in Excel een foutmelding:
An Unexpected Error Has Occured.

Volgens mij is de code verder wel goed (omdat ie t vanuit Access zelf wel goed doet), en ligt t ergens anders aan, misschien dat jullie me nu daarmee kunnen helpen.

code:

Function runmacroonstartup()

    If MsgBox("Do you want to start import procedure for KANA response?", vbYesNo, "Start KANA import Procedure") = vbYes Then
        GoTo Macrovervolg1
        DoCmd.Quit
        Else
    If MsgBox("Do you want to use the database-file anyway?", vbYesNo, "Use file anyway?") = vbYes Then
        Exit Function
        Else
        DoCmd.Quit
    End If
    End If

Macrovervolg1:
    DoCmd.OpenTable "kana import from txt", acViewNormal, acEdit
    DoCmd.RunCommand (acCmdSelectAllRecords)
    SetWarnings = False
    DoCmd.RunCommand (acCmdDeleteRecord)
    SetWarnings = True
    DoCmd.Close
    DoCmd.TransferText acImportDelim, "kana import", "KANA import from txt", "G:\Reporting\Projects\KANA Response DB\Marnix\SIMULATIEFOLDER\OUTPUT EXCEL VOOR INPUT ACCESS\KANA import weekly.txt", False
    DoCmd.Quit

End Function

Zoals je ziet is t allemaal vrij simpel en gebruik ik niet zoveel (geen ) toelichtingen in mijn macro... maar dat komt meer omdat ik pas vorige week met VBA begonnen ben (vind t wel erg boeiend en sta versteld van de mogelijkheden )

Ow, en hoe doen jullie die VBA zo mooi exporteren in t forum ?

edit: vba code mooi gemaakt

[ Voor 4% gewijzigd door Gulf_5 op 16-11-2005 09:44 . Reden: layout ]

[Boss]

Code maken doe je door de tekst tussen code-tags te zetten:

code:

[code=vba]
  Hier je code
[/code]

Ik zou dit toch volledig vanuit Excel doen. Als ik het goed begrijp wil je op deze manier gegevens uit een txt file overzetten naar een tabel die op een SQL server staat? Is het dan niet makkelijker om die file in 1x over te zetten? Hoewel die database waarschijnlijk ook nog wel voor andere dingen wordt gebruikt?

Hoe ziet de code er nu uit die Access opstart vanuit Excel?

[Gulf_5]

Dit is de code die ik gebruik om excel bestand 1 vanaf een server te plukken en gereed te maken voor export naar text, waarna het bestand als txt wordt weggezet.
Toelichting: het pad vanaf Marnix\Simulatiefolder etc is een testomgeving.

code:

Sub KanaDB()

i = 1
x = 1

Application.ScreenUpdating = False

Workbooks.Open Filename:= _
        "G:\Reporting\Projects\KANA Response DB\Marnix\SIMULATIEFOLDER\BRONBESTANDEN\Message Volume last week by mailbox.xls"
Application.DisplayAlerts = False
ActiveWorkbook.SaveAs Filename:= _
        "G:\Reporting\Projects\KANA Response DB\Marnix\SIMULATIEFOLDER\OUTPUT EXCEL VOOR INPUT ACCESS\KANA import weekly.txt", FileFormat:=xlText _
        , CreateBackup:=False
Application.DisplayAlerts = True
        
Range("A1:U10").Select
Selection.Find(What:="Dates Found:", After:=ActiveCell, LookIn:=xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows, Searchdirection:=xlNext, MatchCase:=False).Select
pos = InStr(1, ActiveCell.Value, ":") + 2
datum = Mid(ActiveCell.Value, pos, 10)
week = Format(datum, "ww", vbMonday, vbFirstFullWeek)
jaar = Format(datum, "yyyy", vbMonday, vbFirstFullWeek)


Range("X1").Select
ActiveCell.EntireColumn.Delete
Range("AB1").Select
ActiveCell.EntireColumn.Delete


'adding new sheet for copy paste special values for deleting graphical images.


Sheets.Add
Sheets("KANA import weekly").Select
Cells.Select
Selection.Copy
Sheets("Sheet1").Select
Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
        xlNone, SkipBlanks:=False, Transpose:=False
Sheets("KANA import weekly").Select
    Application.CutCopyMode = False
    Application.DisplayAlerts = False
    ActiveWindow.SelectedSheets.Delete
    Application.DisplayAlerts = True
    ' Sheets("KANA import weekly").Select
    ' Sheets("KANA import weekly").Name = "Sheet1"
Range("A1").Select


'deleting rows and columns, unmerge merged cells
Do Until i = 300
    If InStr(1, ActiveCell.Value, "@") Then
        ActiveCell.EntireRow.Select
        Selection.Interior.ColorIndex = 2
        Selection.Font.ColorIndex = 1
        ActiveCell.Offset(1, 0).Select
    Else
        ActiveCell.EntireRow.Delete
    End If
    i = i + 1
Loop

Columns("A:Z").Select
    Range("a81").Activate
        With Selection
            .VerticalAlignment = xlTop
            .Orientation = 0
            .AddIndent = False
            .ShrinkToFit = False
            .ReadingOrder = xlContext
            .MergeCells = False
        End With

Range("A1").Select
Do Until x = 35
    If ActiveCell.Value = "" Then
        ActiveCell.EntireColumn.Delete
    Else
        ActiveCell.Offset(0, 1).Select
    End If
    x = x + 1
Loop

Columns("B:Z").Select
Range("B1").Activate
Selection.NumberFormat = "general"

Range("L1").Select
ActiveCell.EntireColumn.Delete
Range("M1").Select
ActiveCell.EntireColumn.Delete

Columns("M:IV").Select
    Range("O2").Activate
    Selection.EntireColumn.Hidden = True

Range("A1:L1000").Select
Selection.Copy
Sheets.Add
Sheets("Sheet2").Select
Range("A1").Select
Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
        xlNone, SkipBlanks:=False, Transpose:=False
Sheets("Sheet1").Select
    Application.CutCopyMode = False
    Application.DisplayAlerts = False
    ActiveWindow.SelectedSheets.Delete
    Application.DisplayAlerts = True
    Sheets("Sheet2").Select
    Sheets("Sheet2").Name = "Sheet1"
Range("A1").Select

ActiveWorkbook.Save

ActiveWorkbook.Saved = True
ActiveWorkbook.Close
Application.ScreenUpdating = True
MsgBox "KANA response preparation procedure is completed successfully", vbOKOnly, "Ready"

End Sub

Dit is de code waarmee ik excel bestand 2 klaarmaak en opsla als tekst.

code:

Sub phonedbprep()

Application.ScreenUpdating = False

Workbooks.Open Filename:= _
        "G:\Reporting\Projects\KANA Response DB\Marnix\SIMULATIEFOLDER\BRONBESTANDEN\pincodes summary report.htm"

Rows("1:25").Delete
Cells.Select

Selection.Find(What:="Total Calls", After:=ActiveCell, LookIn:=xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows, Searchdirection:=xlNext, MatchCase:=False).Select
Selection.EntireRow.Delete

r = 1
c = 3
Cells(1, 3).Select

Do Until ActiveCell.Offset(0, -2) = ""

'splitting time
i = 3

posUur = Mid(ActiveCell.Value, i, 1)
posMin = Mid(ActiveCell.Value, i + 2, 2)
posSec = Mid(ActiveCell.Value, i + 5, 2)

ActiveCell.Offset(0, 1).Select
ActiveCell = posUur
ActiveCell.Offset(0, 1).Select
ActiveCell = posMin
ActiveCell.Offset(0, 1).Select
ActiveCell = posSec

r = r + 1

Cells(r, c).Select
Loop

Range("C1").Select
Selection.EntireColumn.Delete

Application.DisplayAlerts = False
ActiveWorkbook.SaveAs Filename:= _
        "G:\Reporting\Projects\KANA Response DB\Marnix\SIMULATIEFOLDER\OUTPUT EXCEL VOOR INPUT ACCESS\pincodesummreport.txt", FileFormat:=xlText _
        , CreateBackup:=False
Application.DisplayAlerts = True

ActiveWorkbook.Saved = True
ActiveWorkbook.Close
Application.ScreenUpdating = True
MsgBox "Pincode preparation procedure is completed successfully", vbOKOnly, "Ready"

End Sub

Onderstaande is de code die gebruikt wordt om het txt bestand in te lezen in de Access tabel. De code wordt opgestart door de AutoExec functie in Access *(dus bij t opstarten).
Deze werkt nog niet helemaal foutloos, maar dat komt omdat ik de code eerst had geschreven in het Macro-gedeelte van Access... dat werkte niet zo lekker, dus ben m handmatig aan t inbrengen in VBA.

code:

Function runmacroonstartup()

    If MsgBox("Do you want to start import procedure for KANA response?", vbYesNo, "Start KANA import Procedure") = vbYes Then
        GoTo Macrovervolg1
        DoCmd.Quit
        Else
    If MsgBox("Do you want to use the database-file anyway?", vbYesNo, "Use file anyway?") = vbYes Then
        Exit Function
        Else
        DoCmd.Quit
    End If
    End If

Macrovervolg1:
    DoCmd.OpenTable "kana import from txt", acViewNormal, acEdit
    DoCmd.RunCommand (acCmdSelectAllRecords)
    SetWarnings = False
    DoCmd.RunCommand (acCmdDeleteRecord)
    SetWarnings = True
    DoCmd.Close
    DoCmd.TransferText acImportDelim, "kana import", "KANA import from txt", "G:\Reporting\Projects\KANA Response DB\Marnix\SIMULATIEFOLDER\OUTPUT EXCEL VOOR INPUT ACCESS\KANA import weekly.txt", False
    DoCmd.Quit

End Function

En dit is de code voor de import in Access van het 2e txt bestand.(Deze wordt ook bij opstarten ingeschakeld) Zoals je ziet, hier is eigenlijk het geval wat ik in de vorige code al heb weggewerkt. De macro in Access die hier wordt getriggerd moet nog worden overgezet naar VBA.

code:

Function runmacroonstartup()

If MsgBox("Do you want to start import procedure for Pincode?", vbYesNo, "Start Pincode import Procedure") = vbYes Then
    DoCmd.RunMacro "ImportPhoneDBweekly"
End If

If MsgBox("Do you want to use the database-file anyway?", vbYesNo, "Use file anyway?") = vbYes Then
    Exit Function
    Else
    DoCmd.Quit
              
End If

De code voor het opstarten van Access vanuit Excel is momenteel niets meer dan een hyperlink, die precies doet wat ik momenteel wil. De code zoals ik hem hiervoor had (na hulp van Boss) zag er ongeveer zo uit.

code:

Sub Test() 
Dim oAcc As New Access.Application 
   oAcc.OpenCurrentDatabase "G:\reporting\Projects\KANA Response DB\Marnix\SIMULATIEFOLDER\OUTPUT ACCESS\KANA response for import.mdb", False 
   If MsgBox("Do you want to start import procedure for Pincode?", vbYesNo, "Start Pincode import Procedure") = vbYes Then
    DoCmd.RunMacro "ImportPhoneDBweekly"
End If

If MsgBox("Do you want to use the database-file anyway?", vbYesNo, "Use file anyway?") = vbYes Then
    Exit Function
                      
End If
   oAcc.CloseCurrentDatabase 
End Sub

tot slot heb ik dus een excel bestand die dus alles aanstuurt. Het komt in het kort hierop neer:
Knop 1: start macro 1, bewerk xls -> txt, exit excel
Knop 2: start acces-file, autoexec start import proces, exit access

Knop 3: start macro 2, bewerk xls -> txt, exit excel
Knop 4: start access-file, autoexec start import proces, exit access

Knop 2 en 4 zijn momenteel dus hyperlinks...

Ben benieuwd naar jullie reactie

[ego-streel modus]
Hmm, nu ik t zo bekijk lijkt t heel wat voor als je net 4 dagen met VBA bezig bent
[/ego-streel modus]