How to structure my app (1 Viewer)

[GeorgieB]

Hello,

I've posted before about this project I'm working on, and got a lot a help from the forum, and now I'm adding another post on how to better structure it.
The project is for a construction company and will help them with their estimation for various projects. They receive an Excel file with a lot of lines where they need to estimate how much that would cost(i.e. one line might be: "Installing steel pipes, category 1, size 20 cm"). The file usually has a lot of other columns beside the column with the categories which need estimation, and also headers. They currently go to their excel files, search for steel pipe, than category 1, and then 20 cm, and see how much that would cost in terms of money.

My current imagined "flow":
A user would open this Excel file, use a simple macro in Excel, or just create a new excel file where he/she would copy the lines from the original file in a single column.
This single column Excel file would be then imported into the Access app, where I've created the user interface(matching with the db, add additional lines, remove existing lines...), then after all the matching between the lines and db is done, export it in a excel file.
Then he/she would use a Vlookup to match the lines in the original file to the export and fetch the prices from the export, and use this for the final estimation.

The question is: can I somehow use only the Access interface to open and manipulate the excel file(it usually has headers, a lot of other columns) so I can import only the column with categories I need to estimate, do the "magic", and the export it back without doing the Excel manipulation part?
As I'm writing this a thought came to my mind, that I could import the whole excel, somehow open it in a window so the user can select the column with the categories I need to match, then add a column with the price for each line, and export it back to Excel.

Thanks for your feedback,
George

 

[Ranman256]

use access to open excel and do all the things you need, then save it
then do the access work.

Note: you MUST put Excel in the program REFERENCES, in VBE menu (Alt-F11): TOOLS , REFERENCES

Dim xl As Excel.Application
Set xl = CreateObject("excel.application")
With xl
    .Workbooks.Open sFile
    .Rows("1:3").delete
    .Range("A1").Select
   

    .ActiveWorkbook.SaveAs "c:\NEW filename.xlsx"
    .Quit
End With
Set xl = Nothing

 

[GeorgieB]

Hi,

Once the excel file is open, will the user be able to select with the mouse a given column?
Or move/delete other cells, so basically what you would expect in a regular excel file.

Thanks,
George

 

[Pat Hartman]

Excel automation is not interactive. It is programmatic control over the workbook. That means that you need to know everything necessary beforehand. It's a little like playing pin the tail on the donkey (children's party game. Picture of a donkey is placed on the wall. each child gets a tail with a number on it. One at a time, they are turned around and pointed back at the picture and they place the tail where they think it belongs. Closest to the "spot" wins).

If you don't know the specific format, you might be able to work it out with code if there are "markers" you can look for to orient yourself.

Can you control the format of the bid spreadsheet ahead of time? That is the simplest solution.

 

[GeorgieB]

Excel automation is not interactive. It is programmatic control over the workbook. That means that you need to know everything necessary beforehand. It's a little like playing pin the tail on the donkey (children's party game. Picture of a donkey is placed on the wall. each child gets a tail with a number on it. One at a time, they are turned around and pointed back at the picture and they place the tail where they think it belongs. Closest to the "spot" wins).

If you don't know the specific format, you might be able to work it out with code if there are "markers" you can look for to orient yourself.

Can you control the format of the bid spreadsheet ahead of time? That is the simplest solution.

Hi Pat,

The only way to control the format before importing it would be to ask the user to pre-process it in Excel(which I'll probably do).

Thanks for your answer,
George

 

[Pat Hartman]

Can you make a template that they can use?

 

[GeorgieB]

Yes, I can. Thanks for the tip.