Theo Kouwenhoven
asked on
Transfer files to Excel
To my opinion this question doesn't belong here but I will try any way, because it's Client Access related. Putting it in the Excel area will probably give the reaction "Client what??"
so......
Every month one of our users asks me to help to download a file from the as/400 in excel.
(i'ts done with the excel CA addin "Transfer from as/400").
I already have saved the transfer file "GetMyFile.DFT" but he is stil not able to do it.
I like to creat a Sheet that will automatically starts the transfer...... How to do?????
Regards....
so......
Every month one of our users asks me to help to download a file from the as/400 in excel.
(i'ts done with the excel CA addin "Transfer from as/400").
I already have saved the transfer file "GetMyFile.DFT" but he is stil not able to do it.
I like to creat a Sheet that will automatically starts the transfer...... How to do?????
Regards....
You could create a data transfer shortcut for the user that creates an Excel file and runs automatically when he/she opens it. The kicker is that the target filename would always be the same. Maybe a big deal, maybe not.
Create a new transfer.
Open Properties and click on the Startup tab.
Select 'Run transfer request automatically'
Click 'OK'
Select 'File' for the Output device.
Click the Details button and pick one of the BIFFx(Excel) file types. (you might have to experiment a bit. BIFF8 worked for me and Excel97)
Fill in the other stuff and save it.
Put a shortcut on the user's desktop.
I thought a fella could create an automatic macro in Excel but, at least in Excel97, the CA add-in doesn't get recorded when I try recording a macro. Maybe it works in other versions.
--cs
Create a new transfer.
Open Properties and click on the Startup tab.
Select 'Run transfer request automatically'
Click 'OK'
Select 'File' for the Output device.
Click the Details button and pick one of the BIFFx(Excel) file types. (you might have to experiment a bit. BIFF8 worked for me and Excel97)
Fill in the other stuff and save it.
Put a shortcut on the user's desktop.
I thought a fella could create an automatic macro in Excel but, at least in Excel97, the CA add-in doesn't get recorded when I try recording a macro. Maybe it works in other versions.
--cs
ASKER
Hi Dave,
I was already busy to do it with the cwbtf, but using the excel add-un sould be preferabel. I will wait for an other solution, otherwise I will copy and paste this moanday morning.
Thanks (again).
I was already busy to do it with the cwbtf, but using the excel add-un sould be preferabel. I will wait for an other solution, otherwise I will copy and paste this moanday morning.
Thanks (again).
murphey2:
I work very little on the Excel side, so I have to ask questions...
What does your user do with the file? Is it simply loaded or is it updated and uploaded back to your AS/400? If it's simply loaded, does it matter what form it's in? That is, could you create a .CSV file for the user or even a .HTML file? Excel can handle either and both could be generated on your AS/400 and stored in a directory for the user. Then it'd be a simple drag/drop or double-click from a mapped drive or even a desktop shortcut.
Tom
I work very little on the Excel side, so I have to ask questions...
What does your user do with the file? Is it simply loaded or is it updated and uploaded back to your AS/400? If it's simply loaded, does it matter what form it's in? That is, could you create a .CSV file for the user or even a .HTML file? Excel can handle either and both could be generated on your AS/400 and stored in a directory for the user. Then it'd be a simple drag/drop or double-click from a mapped drive or even a desktop shortcut.
Tom
ASKER
Tom
Thanks for the response, I can also create an XLS file on the as/400 and write it to maped drive, but the users are not alowed to use maped as/400 driver (company policy).
So I have to download it with cwbtf or the excel build in function, Normally the build in function is used, but some users seems even to be to stupid to remember something or read a very simple work-instuction. :-)
Thanks
Thanks for the response, I can also create an XLS file on the as/400 and write it to maped drive, but the users are not alowed to use maped as/400 driver (company policy).
So I have to download it with cwbtf or the excel build in function, Normally the build in function is used, but some users seems even to be to stupid to remember something or read a very simple work-instuction. :-)
Thanks
hi murphey,
i don't have access to cwbtf and as/400 right now, but i think you can do it this way:
create a macro named auto_exec it will be automatically opened when the workbook is opened.
i'm not very good in the VB macro too, but i think you can use "record macro" method, just record the steps to open the .dft file, and save it as auto_exec macro.
i don't have access to cwbtf and as/400 right now, but i think you can do it this way:
create a macro named auto_exec it will be automatically opened when the workbook is opened.
i'm not very good in the VB macro too, but i think you can use "record macro" method, just record the steps to open the .dft file, and save it as auto_exec macro.
ASKER
Hi Dave,
Your sugestion works (partialy), but when the system is slow (what happens frequently) the the macro try to load the file before it is downloaded.
Your sugestion works (partialy), but when the system is slow (what happens frequently) the the macro try to load the file before it is downloaded.
ASKER
Hi Dave,
Your sugestion works (partialy), but when the system is slow (what happens frequently) the the macro try to load the file before it is downloaded.
Your sugestion works (partialy), but when the system is slow (what happens frequently) the the macro try to load the file before it is downloaded.
Hi
is it running on NT/2k/XP or 95/98/me
is it running on NT/2k/XP or 95/98/me
Have you checked theauto run / close on completionj under properties / startup?
The checkapp should wait till the transfer closes before returning control back to the open statement.
Dave
The checkapp should wait till the transfer closes before returning control back to the open statement.
Dave
ASKER
Hi Dave,
It's running on W2K and both checkboxes are checked.
It's running on W2K and both checkboxes are checked.
murphey2:
Note that via the /QNTC file system, it isn't necessary for the user to map a drive -- the AS/400 could write directly to a directory on the user's PC. If it's Win2K, it _might_ be easy if the user id/passwords match.
If they don't match, it's possible for the AS/400 job to do a profile switch to do the transfer under a profile that does match.
Tom
Note that via the /QNTC file system, it isn't necessary for the user to map a drive -- the AS/400 could write directly to a directory on the user's PC. If it's Win2K, it _might_ be easy if the user id/passwords match.
If they don't match, it's possible for the AS/400 job to do a profile switch to do the transfer under a profile that does match.
Tom
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Hi I have had a reques to comment the VB code so here it is.
Private Sub Workbook_Open()
'Note By default, the Shell function runs other programs asynchronously. This means
'that a program started with Shell might not finish executing before the statements
'following the Shell function are executed.
'Set a1 to the windows shell process.
a1 = Shell("C:\PROGRA~1\IBM\CLI ENT~1\cwbt f.exe U:downnload.dtf")
Call ChkApp(a1) '' loop until the download has finished
Workbooks.Open Filename:="U:downladedwork book.xls" 'open the donwloaded file
end sub
'' check if an applciation is still open
''Apid is a windows event number created by Shell - this is like an AS/400 job number
Public Sub ChkApp(Apid)
Dim t1 As Double
Dim t2 As Double
'' Global error capture - this exits the sub when the application is no longer running
On Error GoTo NoApp
'' Loop to infinity
Do
'' ensure t2 is > timer (23:59:59 + 2 seconds = 00:00:01)
Do Until t2 > Timer
t2 = Timer + 2
Loop
'' wait 2 seconds (release CPU to other windows tasks)
Do Until Timer > t2
DoEvents
Loop
'' activate the requested application, when it has finished
''an error will be isued, this will exit the infiity loop
AppActivate Apid, False '' false = do not wait for the application - just check it is there
Loop
'' Exit the sub
Resume1:
Exit Sub
'' When the application has finished the error trapping will force the code here
NoApp:
Resume Resume1 '' I like to tidy up with a resume!
End Sub
Regards
dave
Private Sub Workbook_Open()
'Note By default, the Shell function runs other programs asynchronously. This means
'that a program started with Shell might not finish executing before the statements
'following the Shell function are executed.
'Set a1 to the windows shell process.
a1 = Shell("C:\PROGRA~1\IBM\CLI
Call ChkApp(a1) '' loop until the download has finished
Workbooks.Open Filename:="U:downladedwork
end sub
'' check if an applciation is still open
''Apid is a windows event number created by Shell - this is like an AS/400 job number
Public Sub ChkApp(Apid)
Dim t1 As Double
Dim t2 As Double
'' Global error capture - this exits the sub when the application is no longer running
On Error GoTo NoApp
'' Loop to infinity
Do
'' ensure t2 is > timer (23:59:59 + 2 seconds = 00:00:01)
Do Until t2 > Timer
t2 = Timer + 2
Loop
'' wait 2 seconds (release CPU to other windows tasks)
Do Until Timer > t2
DoEvents
Loop
'' activate the requested application, when it has finished
''an error will be isued, this will exit the infiity loop
AppActivate Apid, False '' false = do not wait for the application - just check it is there
Loop
'' Exit the sub
Resume1:
Exit Sub
'' When the application has finished the error trapping will force the code here
NoApp:
Resume Resume1 '' I like to tidy up with a resume!
End Sub
Regards
dave
open excel press alt F11 to get VB code
select this workbook
vb code
Private Sub Workbook_Open()
a1 = Shell("C:\PROGRA~1\IBM\CLI
Call ChkApp(a1)
Workbooks.Open Filename:="U:downladedwork
end sub
'' check if an applciation is still open
Sub ChkApp(Apid)
Dim t1 As Double
Dim t2 As Double
On Error GoTo NoApp
Do
Do Until t2 > Timer
t2 = Timer + 2
Loop
Do Until Timer > t2
DoEvents
Loop
AppActivate Apid, False
Loop
Resume1:
Exit Sub
NoApp:
Resume Resume1
End Sub
dave