VBA w/ Microsoft ACE OLEDB 12.0 connection strings

[Pinaceous]

Hello Everyone,

I'm working with

Microsoft Office Professional Plus 2013

in using this code below that I've recorded to download a file know as

FileONE.xlsx

.

CODE1:

Sub Macro2()
'
    Sheets("Worksheet5").Select
     
    With ActiveSheet.ListObjects.Add(SourceType:=0, Source:=Array( _
        "OLEDB;Provider=Microsoft.ACE.OLEDB.12.0;Password="""";User ID=Admin;Data Source=C:\Users\Desktop\FileONE.xlsx;Mode=Share Deny " _
        , _
        "Write;Extended Properties=""HDR=YES;"";Jet OLEDB:System database="""";Jet OLEDB:Registry Path="""";Jet OLEDB:Database Password="""";Jet " _
        , _
        "OLEDB:Engine Type=37;Jet OLEDB:Database Locking Mode=0;Jet OLEDB:Global Partial Bulk Ops=2;Jet OLEDB:Global Bulk Transactions=1;" _
        , _
        "Jet OLEDB:New Database Password="""";Jet OLEDB:Create System Database=False;Jet OLEDB:Encrypt Database=False;Jet OLEDB:Don't Copy " _
        , _
        "Locale on Compact=False;Jet OLEDB:Compact Without Replica Repair=False;Jet OLEDB:SFP=False;Jet OLEDB:Support Complex Data=False;" _
        , _
        "Jet OLEDB:Bypass UserInfo Validation=False;Jet OLEDB:Limited DB Caching=False;Jet OLEDB:Bypass ChoiceField Validation=False" _
        ), Destination:=Range("$A$1")).QueryTable
        .CommandType = xlCmdTable
        .CommandText = Array("'Inbound Flight$'")
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .BackgroundQuery = True
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .PreserveColumnInfo = True
        .SourceDataFile = "C:\Users\Desktop\FileONE"
        .Refresh BackgroundQuery:=False
    
    End With
    
    Exit Sub
End Sub

My question is I first want the sub to look for the file to see if it even exists; in simply using:

CODE2:

[COLOR=#ff0000]  If Dir("C:\Users\Desktop\fileONE.xlsx") = "" Then
       Msgbox "File does not Exist on the Desktop"
       Exit Sub
 End if[/COLOR]

In exploring this topic I've ran into https://www.connectionstrings.com/ace-oledb-12-0/ which is indicating to me that I need more to this CODE2 then meets the eye.

Can someone help me piece this information together, so I can make this CODE2 work for my application here??


Many thanks in advance!

R/
Pinaceous

PS. This is an extension from my previous asked thread http://Re: VBA *.xlsx file import; check to see if its there 1st which I found myself off topic. Or am I??

 

[Norie]

Why do you think you need something more?

The code you've posted should be sufficient for checking for the existence of the file.

 

[Pinaceous]

Hello,

When I use code2 as is, it does not for some reason work. Where I have to specifically add which user's (folder) into the code2 for it to work properly. Whereas in code1 I do not have to specify a specific user folder name there.

I'm assuming that I have to incorporate some of that OLEDG and password verberage that can be seen in code1.

 

[Norie]

To check if a file exists all you need is the path to the file and its filename, you don't need any OLEDB stuff or passwords.