Printable Version of Topic

Click here to view this topic in its original format

UtterAccess Forums _ Access Modules _ Transferspreadsheet With Dir Function Using Wildcards

Posted by: g0049978 Sep 13 2019, 04:36 PM

The following code works and tells me the file is there:

Public Sub Find_Document()
Dim sfound As String

sfound = Dir("\\abc\Shares\Public\RoutingPointClosedReport\" & "*" & Format(Now(), "yy") & "*" & Format(Now(), "m") & "*" & Format(Now(), "dd") & "*" & "4" & "*" & "PM" & "*" & ".xlsx")
If sfound <> "" Then
MsgBox ("File Found")
Else
MsgBox ("No file/wildcard matches")
End If

End Sub

Rather than a msgbox saying ("File Found"), I want to transfer the workbook found in the Dir where the worksheet is called "Exception Report" into a table called "Closed4pm". I've tried a lot of things and cannot get it to pull in the file.

Posted by: theDBguy Sep 13 2019, 06:25 PM

Hi. Welcome to UA! welcome2UA.gif

Did you try using an APPEND query?

Posted by: g0049978 Sep 14 2019, 09:35 AM

No, I haven't - how would I write that?

Posted by: ADezii Sep 14 2019, 11:30 AM

One approach to accomplishing what you have requested is to define a Named Range in the Exception Report Worksheet of the External Workbook. For the purposes of this Demo, I named it ExceptionRpt. The Name of this Named Range is now inserted into the Range Argument of the TransferSpreadsheet Method in order to specifically limit the Import to the Specified Range in the Exception Report Spreadsheet. I have tested this Logic and it works quite well. For you, all that is left is to make any necessary substitutions. Obviously, this Code has NOT been tested under your specific conditions. Good Luck with your Project.

CODE
Dim strBasePath As String
Dim strFilter As String
Dim sfound As String


strBasePath = "\\abc\Shares\Public\RoutingPointClosedReport\"
strFilter = "*" & Format(Now(), "yy") & "*" & Format(Now(), "m") & "*" & _
                  Format(Now(), "dd") & "*" & "4" & "*" & "PM" & "*"

sfound = Dir(strBasePath &  strFilter & ".xlsx", vbNormal)

If sfound <> "" Then
  DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel12, "Closed4PM", _
                            strBasePath & sfound, True, "ExceptionRpt"
Else
  MsgBox ("No file/wildcard matches")
End If

Application.RefreshDatabaseWindow

Posted by: g0049978 Sep 16 2019, 10:59 AM

Thank you; however, I'm getting the following error: Run-time error '3274' External table is not in the expected format

Posted by: g0049978 Sep 16 2019, 01:42 PM

If I can find a way to open the file and resave as .xlsx, then I'm able to import; however, this is a .xlsx file that is spit out from an oracle process every hour and this is probably why it doesn't recognize the format. Any ideas on opening and saving as from this wildcard location?

Posted by: ADezii Sep 16 2019, 02:46 PM

Did you try changing the

CODE
acSpreadsheetTypeExcel12

Argument?

Posted by: ADezii Sep 16 2019, 05:43 PM

Can you Upload a sample Oracle Output File stripped of any sensitive Data? Why does the Output File have a *.xlsx Extension?