Need help connecting to Excel Spreadsheet/Workbook

[ddelk]

I have data in an Excel spreadsheet. I want to use VB 6.0 to connect to it, then pull data into text boxes or list boxes or whatever. I have created a 'front end' by using VBA in another Sheet in the Workbook, but it is too limiting. Additionally, I need to package it all together so that I can give it to a user. I have figured out how to connect, but if the Excel file gets moved the connection is lost. I cannot use Access as the data is the result of a statistical model and is driven directly into Excel. I simply want to be able to replace the Excel file and continue using the VB front end.
1. Can VB read UNC path names?
2. Is it possible to make the spreadsheet a part of a VB project or would that require recompiling the code each time the spreadsheet changed?
3. Does VB have an "installer" which would load the .exe file and the Excel file into a specified directory?

Thanks in advance.

 

[borgunit]

If I were you, when you connect to the Excel Application, code in the file location to open, but do a check that the file exists. If not, browse to find it and then open the document.

 

[Nunina]

Hi!

I just did a project that used an Excel workbook as the database. Here is the code:

First of all, reference DAO in your project.

Dim DbExcel As DAO.Database
Dim rsExcel As DAO.Recordset
    
'IMEX=1 tells the driver to use Import mode.  This forces mixed data to be converted to text.
    
Set DbExcel = OpenDatabase(strFileNameIn, False, True, "Excel 8.0; HDR=YES; IMEX=1;")
'strFileNameIn is the path of the Excel workbook

Set rsExcel = DbExcel.OpenRecordset("SELECT * FROM [" & sSheetName & "]")
    rsExcel.MoveFirst
    Set ReadExcel = rsExcel
    Set rsExcel = Nothing

Hope this helps!

Nunina

 

[ddelk]

Both suggestions are helpful and I expect to need them both. However, what happens if the spreadsheet path changes between compile time to run time. For instance, I have it in my project directory and use that path name while designing the application. Then I package it all up with Package and Deployment Wizard. Upon installation, the default path for the install is not the path to my project and even then, the user can change the path. What happens to my application? Will it find the spreadsheet and use it wherever it is? Or is there a generic path name I can use so the app will adapt no matter where the spreadsheet is installed?

 

[ca8msm]

You could use either:

1) App.Path & "/excel.xls" - This will use the path of where the exe is loacted

2) Store the path to the xls file in an INI file and just update that if the file ever moves.

 

[johnwm]

Use the VB Packageing and Deployment Wizard (included in VB), or Microsoft Installer (free download from MS) or INNO installer. Any of those will give you an installable package that can include your spreadsheet. Put the spreadsheet in your programs installed directory, and reference it within the project with App.Path (which always points to the directory containing the running version)


________________________________________________________________
If you want to get the best response to a question, please check out FAQ222-2244 first

'People who live in windowed environments shouldn't cast pointers.'

 

[ddelk]

Thanks all. Much obliged for the info. Cheers