Browsing 1

[JonoB]

Hi,

I'm trying to automate the import of a spreadsheet into access...I have the transferspreadsheet part working fine, but this assumes that the filename and location is the same everytime.

What is the code that will let users browse for the relevant file?

Thanks for the help.

 

[IGORB]

What if you use Common Dialog Control from VB6.0.
Then you can browse to any file location.
Also there are 2 additional ways: use API functions or create Class module and call windows explorer.

 

[JonoB]

Using VB Doesnt really help me, as I dont have VB6 installed and I wouldnt want users to have to install it either.

Please can you elaborate on API functions or calling windows explorer?

 

[BillPower]

Hi JonoB and IGORB,

You're both welcome to look at my embarassing site (one of these days I will tidy it up) that attempts to explain this:

http://www.wpower.fsbusiness.co.uk/

CommonDialogAccess2000.zip or CommonDialogAccess97.zip are the files to look at, they all have links to their respective threads in Tek-Tips.

Bill

 

[JonoB]

You the man!!!

 

[JonoB]

Just one quick question, as I am relatively new to coding...I want to run the transferspreasdheet command straight after, so it looks as follows. What do I put in place of the "?"

GetFileInformation (Find_File(glInitDir))
MsgBox "Path: " & glPath + vbCrLf & _
"File: " & glFileName + vbCrLf & _
"Path and File: " & glPath & "\" & glFileName

DoCmd.TransferSpreadsheet acImport, 8, "tblExcelImport", "?", True

 

[BillPower]

I think this should do it for you JonoB :

GetFileInformation (Find_File(glInitDir))
DoCmd.TransferSpreadsheet acImport, 8, "tblExcelImport", glPath & "\" & glFileName, True

Bill

 

[JonoB]

Ahhh, I was enclosing in quotation marks.

Thanks again! Stars have been awarded!

 

[JonoB]

Hi again,

I am experiencing one small problem with this code. When trying to import a second spreadsheet immediately after the first spreadsheet, the program still tries to import the first spreadsheet again, notwithstanding that a different spreadsheet has been selected.

Any ideas on how to get around this?

 

[JoyInOK]

I don't work with Excel too much, but did you set your reference to the first worksheet to nothing
xlMyWorksheet.close
set xlMyWorksheet = nothing
and then reset it to the new file
set xlMyWorksheet = "NewWorksheetPathAndName"
before importing the second time?

 

[BillPower]

Hi JonoB,

Using the example code I gave you, I have just successfully imported 10 dummy spreadsheets (JonoB1.xls - JonoB10.xls) into a table "tblExcelImport".

Have you altered the code in the slightest way? If so, can you post the code, otherwise I honestly can't explain why it shouldn't work for you.

As a last resort send a copy of your db and at least 2 example speadsheets to billpower@cwcom.net

Regards

Bill

 

[MTBChik]

Argh!!

This is what I have been dealing with for the last week. I built an import/export form on a Windows 2000 machine with Access 2000. Now, when I transfer the form and its code to a machine running XP with Acess 2000 it doesn't recognize the GetFileInformation()method.

Where do I need to set references for this class?????

 

[JonoB]

Did you import the GetFileInformation() module into your new database?

Did you compile your code?

You may have to set references to Microsoft Excel and Microsoft Office in vba editor.