Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

I have the following code as part o

Status
Not open for further replies.
TheRiver

TheRiver

IS-IT--Management
Dec 18, 2003
121
GB
I have the following code as part of a module, which I run using a 'RunCode' within a macro.


With Application.FileSearch
.NewSearch
.LookIn = "K:\WL CMDS (Current Financial Year)_\Dudley Group of Hospitals NHS Trust\Inpatient Waiting _List CMDS"
'Below File needs to be updated each month with the _latest filename
.FileName = "08 IWL Nov (QEE).txt"
If .Execute > 0 Then
For i = 1 To .FoundFiles.Count
DoCmd.TransferText acImportFixed, "IPWL 04 _RNA Import", "WL CMDS (RNA00)", .FoundFiles(i)
Next i
End If
End With

The FileName can be any of the following:

01 IWL Apr (QEE).txt
02 IWL May (QEE).txt
03 IWL Jun (QEE).txt
04 IWL Jul (QEE).txt
05 IWL Aug (QEE).txt
06 IWL Sep (QEE).txt
07 IWL Oct (QEE).txt
08 IWL Nov (QEE).txt
09 IWL Dec (QEE).txt
10 IWL Jan (QEE).txt
11 IWL Feb (QEE).txt
12 IWL Mar (QEE).txt

How can I run the module, using the macro but get the option to select which file from the above should be imported?
 
Sort by date Sort by votes
Hi,

I would create a UserForm with a ComboBox storing the values you want. Then from a CommandButton, assign the value of the ComboBox to a variable and use it in your code.

eg.

Code: 
Sub YourSub(strFileName As String)

With Application.FileSearch
        .NewSearch
        .LookIn = "K:\WL CMDS (Current Financial Year)_\Dudley Group of Hospitals NHS Trust\Inpatient Waiting _List CMDS"
        'Below File needs to be updated each month with the _latest filename
        .FileName = strFileName
        If .Execute > 0 Then
            For i = 1 To .FoundFiles.Count
                DoCmd.TransferText acImportFixed, "IPWL 04 _RNA Import", "WL CMDS (RNA00)", .FoundFiles(i)
            Next i
        End If
    End With

End Sub

Hope this helps.


Leigh Moore
Solutions 4 MS Office Ltd
 
Upvote 0 Downvote
Or have a msgbox popup ... in the same code
BUPA helping the NHS ... what is going on!!!!!


Dim str_answer As String
Dim str_filename As String

With Application.FileSearch
.NewSearch
.LookIn = "K:\WL CMDS (Current Financial Year)_\Dudley Group of Hospitals NHS Trust\Inpatient Waiting _List CMDS"
'Below File needs to be updated each month with the _latest filename

Set str_answer = MsgBox("What month?" & Chr(10) & Chr(13) & Chr(10) & Chr(13) & "Please ensure the month is in the abbreviated format ""jan"" for January", vbInformation, "Select Month")
Select Case str_answer
Case "Apr"
str_filename = "IWL Apr(QEE).txt"
Case "May"
str_filename = "IWL May(QEE).txt"
Case "Jun"
str_filename = "IWL Jun(QEE).txt"
Case "Jul"
str_filename = "IWL Jul(QEE).txt"
Case "Aug"
str_filename = "IWL Aug(QEE).txt"
Case "Sep"
str_filename = "IWL Sep(QEE).txt"
Case "Oct"
str_filename = "IWL Oct(QEE).txt"
Case "Nov"
str_filename = "IWL Nov(QEE).txt"
Case "Dec"
str_filename = "IWL Dec(QEE).txt"
Case "Jan"
str_filename = "IWL jan(QEE).txt"
Case "Feb"
str_filename = "IWL Feb(QEE).txt"
Case "Mar"
str_filename = "IWL Mar(QEE).txt"
Case Else
'unknown file name - would be better if this problem was handled better
End Select
.FileName = str_filename
If .Execute > 0 Then
For i = 1 To .FoundFiles.Count
DoCmd.TransferText acImportFixed, "IPWL 04 _RNA Import", "WL CMDS (RNA00)", .FoundFiles(i)
Next i
End If
End With

Tiny

Perfection is Everything
If it worked first time we wont be here!
 
Upvote 0 Downvote
How do I go about doing this?

Then from a CommandButton, assign the value of the ComboBox to a variable and use it in your code.
 
Upvote 0 Downvote
I added yor code tinymind and get a error message of Compile error - object required???

And hightlights the str_answer in the following line:
Set str_answer = MsgBox("What month?" & Chr(10) & _Chr(13) & Chr(10) & Chr(13) & "Please ensure the month is _in the abbreviated format ""jan"" for January", _vbInformation, "Select Month")
 
Upvote 0 Downvote
Hi,

The Click event of the CommandButton would look something like this;

Code: 
Private Sub cmdCommandButton_Click()

Dim strFileName As String

strFileName = Me.cboComboBox
Call UpdateFile(strFileName)

End Sub

The above code then calls the UpdateFile sub, passing the file name to it as
Code: 
strFileName.

Code: 
Public Sub UpdateFile(strFileName As String)

With Application.FileSearch
        .NewSearch
        .LookIn = "K:\WL CMDS (Current Financial Year)_\Dudley Group of Hospitals NHS Trust\Inpatient Waiting _List CMDS"
        'Below File needs to be updated each month with the _latest filename
        .FileName = strFileName
        If .Execute > 0 Then
            For i = 1 To .FoundFiles.Count
                DoCmd.TransferText acImportFixed, "IPWL 04 _RNA Import", "WL CMDS (RNA00)", .FoundFiles(i)
            Next i
        End If
    End With

End Sub

Hope this is a little clearer, if not, let me know.


Leigh Moore
Solutions 4 MS Office Ltd
 
Upvote 0 Downvote
whoops ...

_vbInformation remove the underscore _

T

Perfection is Everything
If it worked first time we wont be here!
 
Upvote 0 Downvote
Ignore my last post ....

The str should be this ...

Set str_answer = MsgBox("What month? - Please ensure the month is in the abbreviated format Jan for January", vbInformation, "Select Month")

Tiny

Perfection is Everything
If it worked first time we wont be here!
 
Upvote 0 Downvote
Still get the error messge!!!

Set str_answer = MsgBox("What month?" & Chr(10) & Chr(13) & Chr(10) & Chr(13) & "Please ensure the month is in the abbreviated format ""jan"" for January", vbInformation, "Select Month")

This is what it looks like now,

 
Upvote 0 Downvote
I still get the message even with your latest post.
 
Upvote 0 Downvote
remove Set before str_answer ...

Tiny

Perfection is Everything
If it worked first time we wont be here!
 
Upvote 0 Downvote
I tried removing the Set and it runs msg pops up, bu then crashes,program error and access is closed.
 
Upvote 0 Downvote
Gimme 5 Minutes and I will get you a working sample that I will email to you ... but I need you email address!

Tiny

Perfection is Everything
If it worked first time we wont be here!
 
Upvote 0 Downvote
My email address is enderjit.aujla@wwb-pct.nhs.uk
 
Upvote 0 Downvote
just looking at the codes leigh provided, my module has all its data held in a 'function' does this make a difference when you ref to a 'public'
 
Upvote 0 Downvote
The River;

a quick look at Tinymind's code I spotted a couple of problems.

1. there is no need for a set statement its a string not an object

2. use inputbox not msgbox

hth

redapples

Want the best answers? See FAQ181-2886

 
Upvote 0 Downvote
I made the changes you suggest, Input and the Set things.

I run the macro and get the option to enter the month once it works fine in that sense but it does not actually import the data across!!!

Anyone help?
 
Upvote 0 Downvote
This is my code:

With Application.FileSearch
.NewSearch
.LookIn = "K:\WL CMDS (Current Financial Year)_\Dudley Group of Hospitals NHS Trust\Inpatient Waiting _List CMDS"
'Below File needs to be updated each month with the _latest filename

str_answer = InputBox("What month?" & Chr(10) & Chr_(13) & Chr(10) & Chr(13) & "Please ensure the month is in _the abbreviated format ""Jan"" for January", _vbInformation, "Select Month")
Select Case str_answer
Case "Apr"
str_filename = "01 IWL Apr(QEE).txt"
Case "May"
str_filename = "02 IWL May(QEE).txt"
Case "Jun"
str_filename = "03 IWL Jun(QEE).txt"
Case "Jul"
str_filename = "04 IWL Jul(QEE).txt"
Case "Aug"
str_filename = "05 IWL Aug(QEE).txt"
Case "Sep"
str_filename = "06 IWL Sep(QEE).txt"
Case "Oct"
str_filename = "07 IWL Oct(QEE).txt"
Case "Nov"
str_filename = "08 IWL Nov(QEE).txt"
Case "Dec"
str_filename = "09 IWL Dec(QEE).txt"
Case "Jan"
str_filename = "10 IWL jan(QEE).txt"
Case "Feb"
str_filename = "11 IWL Feb(QEE).txt"
Case "Mar"
str_filename = "12 IWL Mar(QEE).txt"
Case Else
'unknown file name - would be better if _this problem was handled better
End Select
.FileName = str_filename
If .Execute > 0 Then
For i = 1 To .FoundFiles.Count
DoCmd.TransferText acImportFixed, "IPWL _04 RNA Import", "WL CMDS (RNA00)", .FoundFiles(i)
Next i
End If
End With

The problem is around For i = 1 To .FoundFiles.Count But I dont know what it is doing wrong here!!!!
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

Moss100
  • Locked
  • Question
Remove everything including the comma from the last comma of a string. 1
Replies
10
Views
348
Andrzejek
Andrzejek
Moss100
  • Locked
  • Question
Vba code I can call from a query to reformat text
Replies
8
Views
226
Moss100
Moss100
feipezi
  • Locked
  • Question
How to get the macros back as they vanish somehow in a AccessDB?
Replies
8
Views
297
combo
combo
JimStrand
  • Locked
  • Question
Trying to Use Form Control as Variable in Report Control Source 2
Replies
5
Views
273
Andrzejek
Andrzejek
Moss100
  • Locked
  • Question
Code not working 1
Replies
5
Views
149
Darrylles
Darrylles

Part and Inventory Search

Sponsor

Back
Top