shariefoo
10-07-2003, 04:04 AM
Hi all,
I have been looking for this for a long long time. Unfortunatly i dint get lucky.
I created a button that lets a user to choose a file by browsing for it. I was successfull in getting the path of the file and saving it in variable.. So far so good..
The question now....
I have a macro that imports data from an excell sheet. I left the path of the file empty in the macro. When i want click a button and select the file, i want the path to be inserted in the "file name" section in the macro automatically. Anyone has any ideas ??
Please ??
Thanx
Mile-O
10-07-2003, 05:47 AM
Put the name of the function that returns the path in the filename box like this (if the function was called GetFile)
=GetFile()
shariefoo
10-07-2003, 10:10 PM
Hi, Thanx for the tip. Seems to work perfectly. However, I still dont know how to implement a code (a function) to import the excel sheet. This is my code
Dim strFilter As String
Dim strInputFileName As String
Dim strtest As String
strFilter = ahtAddFilterItem(strFilter, "Excel Files (*.XLS)", "*.XLS")
strInputFileName = ahtCommonFileOpenSave( _
Filter:=strFilter, OpenFile:=True, _
DialogTitle:="Please select an input file...", _
Flags:=ahtOFN_HIDEREADONLY)
MsgBox strInputFileName
strtest = strInputFileName
The first part opens a window to browse for the file. but then what ?? This is all done a a funtion called getfile()
Please help ?
namliam
10-08-2003, 12:30 AM
Try going into the Acces (VBA) help and typing 'Import Excel file'
*because i am in a good mood:*
You will mostlikely find Docmd.Tranferspreadsheet in the list and find that it is the command to use (same btw in a macro)
Regards
shariefoo
10-08-2003, 01:30 AM
Man.. I have only one thing to tell you now..
I LOVE YOU
shariefoo
10-08-2003, 02:45 AM
OK now i need you help (told ya it wount take long).. Anyways here is my problem.. I am using a code for browsing and its working perfectly.. then i use the following code to import excel data to access.
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, "WOHOO", "C:\Documents and Settings\s269150\Desktop\test.xls, yes
The problem is as following.. this code imports EVERYTHING. and by everything i mean even the titles.. and if table "WOHOO" already exsisted it would say "Run-time error 2391...... Field 'F1' dose not exsist in destination table "WOHOO"..
Help please ?
namliam
10-08-2003, 02:54 AM
You are missing a " at the end of your filename...
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, "WOHOO", "C:\Documents and Settings\s269150\Desktop\test.xls" , yes
Regards
Mile-O
10-08-2003, 02:57 AM
See how much better it is to post to the forum than message someone direct? :rolleyes:
shariefoo
10-08-2003, 02:58 AM
Sorry.
So can u help me please ??
Mile-O
10-08-2003, 03:02 AM
Did Mailman's suggestion not fix it?
Mile-O
10-08-2003, 03:03 AM
Instead of Yes, try True. May not get a result but worth a try.
namliam
10-08-2003, 03:06 AM
Originally posted by Mile-O-Phile
Instead of Yes, try True. May not get a result but worth a try. Geez... True offcourse... My bad...
Good catch Mile...
shariefoo
10-08-2003, 03:08 AM
Sorry Mailman's The " Was there but it seems i did not put it in the forum. My mistake
It actually has a " and still dose not work...
As i said it gives me an error which says feild F1 dose not exsist in the destination table..
I mean the table "WOHOO" is there and it exsists. But the feilds are empty. When i try browsing for a file and importing is gives me that Error ???????
:confused:
Help please ?
shariefoo
10-08-2003, 03:13 AM
Thank you everyone ..
The answer was using true instead of yes..
Sometimes the smallest thing can really have big effects..
Thanx all for your help..
Luv ya all:D :p