VBA open unknown file with user form and copy content

[Alexandru Paun]

Hello all,

I am quite green to VBA and today I need your help:
I want to use UserForms and ask the user to open a file. This file is generated by an ERP system and everyday has a different name and extension based on the day that it was generated. (.77 .78 .79 etc.)
Because it's a different kind of file, excel is opening it in a new session (which always has a different name)
I have tried to record a macro but because the name changes everyday it's not going to work.
The column delimitation in this file is "#"
This is what I was able to do so far

I appreciate your help.

Private Sub CommandButton1_Click()

Workbooks.OpenText Filename:="C:\Users\Alexandru\Desktop\AU214636002.069", _
Origin:=437, StartRow:=1, DataType:=xlDelimited, TextQualifier:= _
xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, Semicolon:=False, _
Comma:=False, Space:=False, Other:=True, OtherChar:="#", FieldInfo:= _
Array(Array(1, 1), Array(2, 1), Array(3, 1), Array(4, 1), Array(5, 1), Array(6, 1), Array(7 _
, 1), Array(8, 1), Array(9, 1), Array(10, 1), Array(11, 1), Array(12, 1), Array(13, 1), Array _
(14, 1), Array(15, 1), Array(16, 1), Array(17, 1), Array(18, 1), Array(19, 1), Array(20, 1), _
Array(21, 1), Array(22, 1), Array(23, 1), Array(24, 1), Array(25, 1), Array(26, 1), Array( _
27, 1), Array(28, 1), Array(29, 1), Array(30, 1), Array(31, 1), Array(32, 1), Array(33, 1), _
Array(34, 1), Array(35, 1), Array(36, 1), Array(37, 1), Array(38, 1), Array(39, 1), Array( _
40, 1), Array(41, 1), Array(42, 1), Array(43, 1), Array(44, 1), Array(45, 1), Array(46, 1), _
Array(47, 1), Array(48, 1), Array(49, 1), Array(50, 1), Array(51, 1), Array(52, 1), Array( _
53, 1), Array(54, 1), Array(55, 1), Array(56, 1), Array(57, 1), Array(58, 1), Array(59, 1), _
Array(60, 1), Array(61, 1), Array(62, 1), Array(63, 1), Array(64, 1), Array(65, 1), Array( _
66, 1), Array(67, 1), Array(68, 1), Array(69, 1), Array(70, 1), Array(71, 1), Array(72, 1), _
Array(73, 1), Array(74, 1), Array(75, 1), Array(76, 1)), TrailingMinusNumbers:=True

Application.DisplayAlerts = False
Cells.Select
Selection.Copy
Windows("Processor.xlsm").Activate
ActiveSheet.Paste
Range("A1").Select
Windows("AU214636002.069").Activate
ActiveWindow.Close
Range("A1").Select
ActiveWorkbook.Save
Application.DisplayAlerts = True

Unload Me

End Sub

 

[JLGWhiz]

You would need to make the day in the file name a variable. Assuming that you get the file on the same day it is created, then something like this would automatically change daily based on the current date.

[COLOR="#B22222"]Dim dt As Variant
If DateDiff("d", #1/1/2015#, Date) < 100 Then
    dt = "0" & CStr(DateDiff("d", #1/1/2015#, Date) + 1)
Else
    dt = CStr(DateDiff("d", #1/1/2015#, Date) + 1)
End If[/COLOR]
Workbooks.OpenText Filename:="C:\Users\Alexandru\Desktop\AU214636002." [COLOR="#B22222"]& dt[/COLOR]

 

[Alexandru Paun]

Thank you JLGWhiz for your suggestion!
I guess it would work but the name changes as well. That is why I would like to use the userforms to prompt the user to select the file himself.
Thanks again.

 

[Alexandru Paun]

The solution was to just load the "unknown file" and then copy the information. Below is the code:

Private Sub CommandButton2_Click()
MyDir = Environ("USERPROFILE") & "\Desktop\"

Set fp = Application.FileDialog(msoFileDialogFilePicker)
With fp
.AllowMultiSelect = False
.InitialFileName = MyDir
.Title = "Select File(s)"
.Show
End With

On Error GoTo err1
filname = fp.SelectedItems(1)

k = 1
Open filname For Input As #1
Do Until EOF(1)
Line Input #1, line
Sheets(1).Cells(k, "A").Value = line
k = k + 1
Loop
Close #1

Sheets(1).Columns("A").TextToColumns DataType:=xlDelimited, TextQualifier:= _
xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, Semicolon:=False, _
Comma:=False, Space:=False, Other:=True, OtherChar:="#"
Sheets(1).Columns("A:W").AutoFit
Exit Sub

err1:
MsgBox "There was an error while opening file"


End Sub