VBA to open folder with variable name

[Freedox]

Hi,

Working on a VBA code to allow me open a folder which was created a day before. Path and the partial folder name is static. The one thing that is variable is the end of folder name which would have a random date stamp. Example:

Reporting_Extracts_14112017_82404

VBA so far:

Private Sub fldOpen_Click()
Dim Foldername As String, strDate As String
strDate = Format(Now - 1, "ddmmyyyy")
Foldername = "\\pc\Path\Extracts\Reporting_Extracts_" & strDate & "_*"
Shell "C:\WINDOWS\explorer.exe """ & Foldername & "", vbNormalFocus
End Sub

Just can't figure out how to open Reporting_Extracts_14112017_[ANY_DATE_STAMP]....

Anyone had similar issue?

 

[wideboydixon]

Hi. You'll need to use Dir() to find a matching folder name:

Private Sub fldOpen_Click()

Const staticFolder = "\\pc\Path\Extracts\"

Dim Foldername As String, strDate As String

strDate = Format(Now - 1, "ddmmyyyy")

Foldername = Dir(staticFolder & "Reporting_Extracts_" & strDate & "_*", vbDirectory)

Shell "C:\WINDOWS\explorer.exe """ & staticFolder & Foldername & """", vbNormalFocus

End Sub

WBD

 

[Freedox]

Hi. You'll need to use Dir() to find a matching folder name:

Private Sub fldOpen_Click()

Const staticFolder = "\\pc\Path\Extracts\"

Dim Foldername As String, strDate As String

strDate = Format(Now - 1, "ddmmyyyy")

Foldername = Dir(staticFolder & "Reporting_Extracts_" & strDate & "_*", vbDirectory)

Shell "C:\WINDOWS\explorer.exe """ & staticFolder & Foldername & """", vbNormalFocus

End Sub

WBD

Legend! Worked like a charm! Cheers!

 

[Freedox]

Hi. You'll need to use Dir() to find a matching folder name:

Private Sub fldOpen_Click()

Const staticFolder = "\\pc\Path\Extracts\"

Dim Foldername As String, strDate As String

strDate = Format(Now - 1, "ddmmyyyy")

Foldername = Dir(staticFolder & "Reporting_Extracts_" & strDate & "_*", vbDirectory)

Shell "C:\WINDOWS\explorer.exe """ & staticFolder & Foldername & """", vbNormalFocus

End Sub

WBD

This code worked perfectly to OPEN a folder... Now I'm trying to modify to actually get a file from this folder and import into the ACCESS db... and again... getting nowhere...

Const staticFolder = "\\pc\Path\Extracts\"


Dim strPathFile As String, strFile As String, strPath As String, strDate As String
Dim strTable As String
Dim blnHasFieldNames As Boolean


strDate = Format(Now, "ddmmyyyy")


strPath = Dir(staticFolder & "Reporting_Extracts_" & strDate & "_*", vbDirectory)


blnHasFieldNames = True


strTable = "dbo_MV_REP_LEVEL"
strFile = Dir(strPath & "MV_REP_LEVEL.csv")


If Right(strPath, 1) <> "\" Then
strPath = strPath & "\"
End If
If Dir(strPath & "*.*") = "" Then
MsgBox "The folder doesn't contain (visible) files"
Else

I can't get this code to see this specific folder... If I set as a static, works fine....

 

[wideboydixon]

Const staticFolder = "\\pc\Path\Extracts\"

Dim strPathFile As String, strFile As String, strPath As String, strDate As String
Dim strTable As String
Dim blnHasFieldNames As Boolean

strDate = Format(Now, "ddmmyyyy")
strPath = staticFolder & Dir(staticFolder & "Reporting_Extracts_" & strDate & "_*", vbDirectory)

blnHasFieldNames = True

strTable = "dbo_MV_REP_LEVEL"

If Right(strPath, 1) <> "\" Then
strPath = strPath & "\"
End If
strFile = Dir(strPath & "MV_REP_LEVEL.csv")
If Dir(strPath & "*.*") = "" Then
MsgBox "The folder doesn't contain (visible) files"
Else

Something like that.

WBD

 

[Freedox]

Thanks!!! It worked!

However, I have a separate question... Now this code loops through all files in the folder... And I would only need VBA to take "MV_REP_LEVEL.csv" file... I know I have this set up to take one, but am I missing something there?

 

[wideboydixon]

You only posted partial code. It's hard to say.

WBD

 

[Freedox]

You only posted partial code. It's hard to say.

WBD

I have the full code below:

Private Sub cmdImport_Click()

Const staticFolder = "\\pc\Path\Extracts\"


Dim strPathFile As String, strFile As String, strPath As String, strDate As String
Dim strTable As String
Dim blnHasFieldNames As Boolean


strDate = Format(Now, "ddmmyyyy")


strPath = staticFolder & Dir(staticFolder & "Reporting_Extracts_" & strDate & "_*", vbDirectory)


blnHasFieldNames = True


strTable = "dbo_MV_REP_LEVEL"




If Right(strPath, 1) <> "\" Then
strPath = strPath & "\"
End If
strFile = Dir(strPath & "MV_REP_LEVEL.csv")
If Dir(strPath & "*.*") = "" Then
MsgBox "The folder doesn't contain (visible) files"
Else


'~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
' If folder IS NOT empty, then PURGE
'~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~


DoCmd.OpenQuery "purgeMV_REP_PUBLISHED_WCID_LEVEL"


'~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
' Once purged LOOP file import
'~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~


Do While Len(strFile) > 0
strPathFile = strPath & strFile


DoCmd.TransferText acImportDelim, "spec", strTable, strPathFile, blnHasFieldNames




     strFile = Dir()
Loop


    MsgBox "Files Sucessfully Imported"


End If


End Sub

 

[wideboydixon]

Looks like you need something like this:

Private Sub cmdImport_Click()

Const staticFolder = "\\pc\Path\Extracts\"

Dim strPathFile As String, strFile As String, strPath As String, strDate As String
Dim strTable As String
Dim blnHasFieldNames As Boolean

strDate = Format(Now, "ddmmyyyy")
strPath = staticFolder & Dir(staticFolder & "Reporting_Extracts_" & strDate & "_*", vbDirectory)
If strPath = "" Then
    MsgBox "Reporting extracts folder for today not found"
    Exit Sub
End If

blnHasFieldNames = True
strTable = "dbo_MV_REP_LEVEL"

If Right(strPath, 1) <> "\" Then strPath = strPath & "\"
strFile = Dir(strPath & "MV_REP_LEVEL.csv")
If strFile = "" Then
    MsgBox "The folder doesn't contain MV_REP_LEVEL.csv"
    Exit Sub
End If

'~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
' If folder IS NOT empty, then PURGE
'~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
DoCmd.OpenQuery "purgeMV_REP_PUBLISHED_WCID_LEVEL"

'~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
' Once purged LOOP file import
'~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
strPathFile = strPath & strFile
DoCmd.TransferText acImportDelim, "spec", strTable, strPathFile, blnHasFieldNames

MsgBox "File Sucessfully Imported"

End Sub

WBD

 

[Freedox]

Cheers man! Works like a charm! Case closed