Import anything with start name (1 Viewer)

Infinite · Jan 9, 2016

Hello! I have the following code:

Code:

Private Sub Command10_Click()

 

   Dim LWordDoc As String
   Dim oApp As Object

   'Path to the word document
   LWordDoc = "C:\Users\Username\Downloads\" & "order_export_" & "*"

   If Dir(LWordDoc) = "" Then
      MsgBox "Document not found."

   Else
      'Create an instance of MS Word
      Set oApp = CreateObject(Class:="Word.Application")
      oApp.Visible = True

      'Open the Document
      oApp.Documents.Open FileName:=LWordDoc
   End If

End Sub

The name of the word file im tying to import is order_export_20160109_195806.docx. What I want is the user to download the item sales from any of the sites we sell products on (Amazon, Ebay, etc, etc) and then click a button, and it imports the sales. But the problem is each of them have different names. Like for our main site the names all start with then name "order_export_" but the end has a random 10 digit number sequence. I just want it to find the "order_export_" and import that. Thus the " & "order_export_" & "*". I just want it to look for anything with that starting, and then import. Is what im trying to do possible? Thanks for the help!

sneuberg · Jan 10, 2016

The Dir function http://www.techonthenet.com/access/functions/file/dir.php allows wild cards (*). You should be able to get the file with that.

sneuberg · Jan 10, 2016

I used notepad to create a file and saved it in the Downloads folder as order_export_.txt. When I ran the follow code it showed up in the immediate window.

Code:

Public Sub Test()

Dim LWordDoc As String
Dim UserPath As String
UserPath = Environ$("USERPROFILE")

LWordDoc = UserPath & "\Downloads\" & "order_export_" & "*"
Debug.Print Dir(LWordDoc)


End Sub

Infinite · Jan 10, 2016

Code:

Private Sub Command10_Click()
Dim LResult As String


If LResult = Len(Dir("C:\Users\Username\Downloads\order_export*.csv")) = 0 Then
   MsgBox "This file does NOT exist."
Else
   MsgBox "This file does exist."
End If

End Sub

How do I get the LResult = to work?

Also, thanks for such a quick reply! That link is exactly what I wanted!

I want LResult to be the link it finds if it works.

sneuberg · Jan 10, 2016

Infinite said:

Infinite · Jan 10, 2016

I want LResult to be the link it finds if it works.
That way, I can have the LResult do other things. In the code im trying to get LResult to = the link if it does exist.

sneuberg · Jan 10, 2016

If you mean by link the path and file then

Code:

Private Sub Command10_Click()
Dim LResult As String

LResult = Dir("C:\Users\Username\Downloads\order_export*.csv")
If LResult = Len(LResult) = 0 Then
   MsgBox "This file does NOT exist."
Else
   MsgBox "This file does exist."
End If

End Sub

Infinite · Jan 10, 2016

Code:

Private Sub Command10_Click()
Dim csvFile As String
Dim txtFile As String
Dim xlsxFile As String

csvFile = Dir("C:\Users\Joshua\Downloads\!Import Sales From Online\*.csv")
txtFile = Dir("C:\Users\Joshua\Downloads\!Import Sales From Online\*.txt")
xlsxFile = Dir("C:\Users\Joshua\Downloads\!Import Sales From Online\*.xlsx")

If Len(Dir("C:\Users\Joshua\Downloads\!Import Sales From Online\*.csv")) = 0 Then
   MsgBox "There are NO .csv file(s)."
Else
   MsgBox "There is a .csv file."
   End If
   
If Len(Dir("C:\Users\Joshua\Downloads\!Import Sales From Online\*.txt")) = 0 Then
      MsgBox "There are NO .txt file(s)."
Else
   MsgBox "There is a .txt file."
End If

If Len(Dir("C:\Users\Joshua\Downloads\!Import Sales From Online\*.xlsx")) = 0 Then
      MsgBox "There are NO .xlsx file(s)."
Else
   MsgBox "There is a .xlsx file."
End If

End Sub

That is what I so far. What im trying to do is like I said, import it. Now that it knows what files I just need to figure out how to import them. I googled around quit a bit, tried all the replies that looked in any way workable, and I its still not working. How would I import a .txt, .csv, or .xlsx into access? Or should I star another thread about that?

Thanks for all your help so far!

sneuberg · Jan 10, 2016

For importing or linking an excel spreadsheet I suggest looking at DoCmd.TransferSpreadSheet https://msdn.microsoft.com/en-us/library/office/ff844793.aspx

I don't know about csv and txt files. I suggestion posting a new thread and include a narrative of what you want to do once they are imported. Also are these downloads the same data in different formats or totally different? This type of information would help the forum members help you.

sneuberg · Jan 10, 2016

Oh and if you just want these documents imported as attachments, let us know in your post.

Infinite · Jan 10, 2016

Quick question. How do I set a file path as a string? Im trying to get:

Code:

csvFile = Dir("C:\Users\Joshua\Downloads\!Import Sales From Online\*.csv")
csvFile As String
Me.Text51 = csvFile

But im not sure how to go about doing that

sneuberg · Jan 10, 2016

Dim csvFile As String
csvFile = Dir("C:\Users\Joshua\Downloads\!Import Sales From Online\*.csv")

Infinite · Jan 10, 2016

Code:

Dim csvFile As String
csvFile = Dir("C:\Users\Joshua\Downloads\!Import Sales From Online\*.csv")


Dim KillFile As String
KillFile = "csvFile"
'Check that file exists
If Len(Dir$(KillFile)) > 0 Then
    'First remove readonly attribute, if set
 SetAttr KillFile, vbNormal
    'Then delete the file
     Kill KillFile
End If
End Sub

Why isnt that working? That looks like it should work fine.

Infinite · Jan 10, 2016

Never mind. I got it:

Code:

Dim LResponse As Integer
Dim FilePath As String


FilePath = Dir("C:\Users\Joshua\Downloads\!Import Sales From Online\*.csv")


LResponse = MsgBox("Are you sure you want to delete the file " & FilePath & "" & _
vbCrLf & "Warning! This will delete it PERMANENTLY! (A really really long time)" _
, vbYesNo, "FOR. EVER.")
If LResponse = vbYes Then
   On Error Resume Next
    Kill "C:\Users\Joshua\Downloads\!Import Sales From Online\*.aasdf*"
    On Error GoTo 0
    Else
    
End If

That works just fine.

Import anything with start name (1 Viewer)

Infinite

More left to learn.

sneuberg

AWF VIP

sneuberg

AWF VIP

Infinite

More left to learn.

sneuberg

AWF VIP

Infinite

More left to learn.

sneuberg

AWF VIP

Infinite

More left to learn.

sneuberg

AWF VIP

sneuberg

AWF VIP

Infinite

More left to learn.

sneuberg

AWF VIP

Infinite

More left to learn.

Infinite

More left to learn.

Similar threads

Users who are viewing this thread