Help please with MACROS ???

[shariefoo]

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]

Put the name of the function that returns the path in the filename box like this (if the function was called GetFile)

=GetFile()

 

[shariefoo]

Thanx But...

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]

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]

Man.. I have only one thing to tell you now..

I LOVE YOU

 

[shariefoo]

Sorry. Back again ;-)

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]

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]

See how much better it is to post to the forum than message someone direct?

 

[shariefoo]

Sorry.

So can u help me please ??

 

[Mile-O]

Did Mailman's suggestion not fix it?

 

[Mile-O]

Instead of Yes, try True. May not get a result but worth a try.

 

[namliam]

Instead of Yes, try True. May not get a result but worth a try.

Geez... True offcourse... My bad...

Good catch Mile...

 

[shariefoo]

Spelling Mistake

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 ???????




Help please ?

 

[shariefoo]

THANKS

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

 

[Mile-O]