UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Transferspreadsheet With Dir Function Using Wildcards, Access 2013    
 
   
g0049978
post Sep 13 2019, 04:36 PM
Post#1



Posts: 26
Joined: 13-September 19



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.
Go to the top of the page
 
theDBguy
post Sep 13 2019, 06:25 PM
Post#2


Access Wiki and Forums Moderator
Posts: 76,409
Joined: 19-June 07
From: SunnySandyEggo


Hi. Welcome to UA! welcome2UA.gif

Did you try using an APPEND query?

--------------------
Just my 2 cents... "And if I claim to be a wise man, it surely means that I don't know" - Kansas
Access Website | Access Blog | Email
Go to the top of the page
 
g0049978
post Sep 14 2019, 09:35 AM
Post#3



Posts: 26
Joined: 13-September 19



No, I haven't - how would I write that?
Go to the top of the page
 
ADezii
post Sep 14 2019, 11:30 AM
Post#4



Posts: 2,679
Joined: 4-February 07
From: USA, Florida, Delray Beach


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

This post has been edited by ADezii: Sep 14 2019, 11:31 AM
Go to the top of the page
 
g0049978
post Sep 16 2019, 10:59 AM
Post#5



Posts: 26
Joined: 13-September 19



Thank you; however, I'm getting the following error: Run-time error '3274' External table is not in the expected format
Go to the top of the page
 
g0049978
post Sep 16 2019, 01:42 PM
Post#6



Posts: 26
Joined: 13-September 19



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?
Go to the top of the page
 
ADezii
post Sep 16 2019, 02:46 PM
Post#7



Posts: 2,679
Joined: 4-February 07
From: USA, Florida, Delray Beach


Did you try changing the
CODE
acSpreadsheetTypeExcel12

Argument?
Go to the top of the page
 
ADezii
post Sep 16 2019, 05:43 PM
Post#8



Posts: 2,679
Joined: 4-February 07
From: USA, Florida, Delray Beach


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


Custom Search


RSSSearch   Top   Lo-Fi    21st October 2019 - 02:26 AM