VBA *.xlsx file import; check to see if its there 1st

[Pinaceous]

Hi All,

I've recorded a macro to import an excel xlsx file known as

file1.xlsx

onto an open workbook's sheet.

If the

file1.xlsx

is unavailable an error pops up:

Please Enter Microsoft Access Database Engine OLD DB Initialization Information

My question is; how can I first run a sub to check if the

file1.xlsx

is present on the users desktop. For example:

C:\Users\Desktop\file1.xlsx

?

Here is my code that I'm playing around with that works fine unless the file is not available:

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\file1.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\file1.xlsx"
        .Refresh BackgroundQuery:=False
    
    End With
    
    Exit Sub
End Sub

Any suggestions??

Many thanks!!

Pinaceous

 

[Perpa]

Pinaceous,
The 'GetValue' function below will determine if a file exists or not. See if you can work with that.
The function arguments are:
path = "C:\Users\Desktop" 'Change to suit
file = "file1.xlsx" 'Don't forget the file extension, '.xlsx'
Perpa

Private Function GetValue(path, file)
'   Determines if a file exists
 
    If Right(path, 1) <> "\" Then path = path & "\"
    If Dir(path & file) = "" Then
        GetValue = "File Not Found"
        MsgBox GetValue
        Exit Function
    Else
       MsgBox path & file & "   ...Does Exist"
    End If
End Function

 

[Pinaceous]

Hello Perpa,

Thank you for your pasted code.

Where would I put this code?

In a standard module?

Also, your suggesting

function arguments

I'm learning as I go sorry about that I need to be lead to the situation.

Do they go at the top of the sub right?

Can I put both the function arguments and the private function together?

Thanks,
Pin

 

[Perpa]

Pin,
I had another look at it and I would try putting the code in red font into your existing module as shown.
Perpa

Sub Macro2()
'
    Sheets("Worksheet5").Select

[COLOR=#FF0000]  If Dir("C:\Users\Desktop\file1.xlsx") = "" Then
       Msgbox "File does not Exist on the Desktop"
       Exit Sub
 End if[/COLOR]
   
    With ActiveSheet.ListObjects.Add(SourceType:=0, Source:=Array( _
        "OLEDB;Provider=Microsoft.ACE.OLEDB.12.0;Password="""";User ID=Admin;Data Source=C:\Users\Desktop\file1.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\file1.xlsx"
        .Refresh BackgroundQuery:=False
    
    End With
    
    Exit Sub
End Sub

 

[Pinaceous]

Hey Perpa,

You know I'm really glad you took another look at the code and improvised it. I was thinking about the if statements and/or a case select. I really do appreciate what you wrote!

Let me give it a go and I'll follow up.

Thank you!
Paul

 

[Pinaceous]

Hello Perpa,

I really appreciate your suggestion and I'm trying to make it work.

As you can tell this code that I'm using is a recorded macro that I've recorded on the company computer.

If I specify

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

when I run this sub, it will not function properly unless I use my specific user name and insert it into the code.

I can't seem to figure this one out as I've removed the specific user name in the latter part of the code x2's in the one that I've pasted.

Do you have any suggestions as to how I can go around this folder/file user name hiccup??

Thank you!

Pinaceous

 

[Perpa]

Pin,
The code you furnished in post #1 has two Password="""" segments that I can see.
Originally, you stated: 'how can I first run a sub to check if the 'file1.xlsx' is present on the users desktop.
The complete path and filename you provided was 'C:\Users\Desktop\file1.xlsx'.
You said that the '...code that I'm playing around with... works fine unless the file is not available.'

But now, apparently you need to '...use ... specific user name and insert it into the code...' to check if that file exists on the desktop.

I am not sure I can help you given the structure of your WITH statement. You are obviously working with the work system registry which requires a password. You might try to relocate the red font code lower in the code as I have shown below and see if that works...but I am not sure that is going to help you. If it doesn't work, then just write 'Bump' and see if there is someone else with that code experience that can help you. Good luck.
Perpa

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\file1.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

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

        .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\file1.xlsx"
        .Refresh BackgroundQuery:=False
    
    End With
    
    Exit Sub
End Sub

 

[Pinaceous]

Hello Perpa,

Thank you for reposting your code. I really do appreciate your input.

I did try and see if that worked and it is not doing what I want exactly, but seems like it is a step in the right direction.



The criteria did change and I am sorry about that. I am learning as I am going along.

I'm trying to post this information in a different way on a separate thread: http://Thread: VBA w/ Microsoft ACE OLEDB 12.0 connection strings.

Hopefully, I might get what I am after.

Many thanks again!
Pinaceous