read excel file into ACCESS table, field by field and line by line instead of importing it.

[johnseito]

Is there a way to use VBA to read excel file (excel sheet) into an Access Database table, field by field, line by line, row by row.



I want to do it this way instead of importing it because I want to control it by reading the file into an Access Table.

[John_G]

Create a link to the Excel spreadsheet (External data - Import and Link). The linked spreadsheet can then be used in the same way as a table in VBA, e.g. in a recordset, except that it cannot be updated.

[johnseito]

I have a file with data in one form I want to covert the data into another form and into access table. I don't think link will work.

[aytee111]

You can read a file line by line using "open". However, I believe it has to be a text/csv and it is far simpler to import the file to a temporary table and do all the data manipulation in Access. With import you will have visibility to the raw data without any of Excel's formatting.

[John_G]

I have a file with data in one form I want to covert the data into another form

Please clarify that - your use of the word "form" is confusing. Do you mean you want to convert from one format to another format? If so, please tell us exactly what you need to do, and why you need to do it in Access.

[johnseito]

Yes you are correct from one format to another.
Do it in Access to find the Grand total and sub total and wants to build an application to do it rather than manual.
Needs to format so it can do the query to achieve the desire result instead of not formatting it.

[ssanfu]

Another option is to use automation to open the Excel workbook and loop through the rows and columns (nested Do loops). (Code would be in Access)
Example code is at Ken Snells site

Obviously, you would have to modify the code to fit your requirements.......