Read formats in Excel, use them to import from A2K to Excel

[u0107]

Hello all,

I guess this is the right forum for this question as this attempts to unite Excel and Access!

I have developed an application from which I want to export some data to another application. The Other application uses Excel as one of the formats of files from which data can be imported. Consequently my process to export into the other application is:

My app -Export-> Excel -Export-> Other Application.

The format of access tables do not match the format of data in the Other application.

My plan is this:

I create an Excel template with the following tabs:

1) Parameters
2) Data Format

In Parameters I define the location of the folder where I want to create the file. There would be other static parameters which are still getting finalized.

In Data Format Tab I define the following:
1) Fields in my table
2) Description
3) Required Flag (Yes/No)
4) Column Letter in the Excel data File
5) Title of the column.

Through VBA, I open this template file, read the names of each field, whether required or not, column letter and title of the column and the location where to store the file.

Next I open a blank excel file, rename one of the tabs to say "Raw Data". In the first row, I put the Titles of the column. In the second and subsequent rows, based on the Field and the corresponding Column Letters, I load values from my Access Tables to the rows

Of course, there will be intermediate arrays used. before I actually write in the Excel Rows..

My questions are:

1) Has anyone done opening, writing into and reading of specific named tabs in Excel and specific cell contents from within those tabs using VBA from WITHIN ACCESS?

2) Are there any sample codes available where I can look and base my coding on it?

Thank you in advance.

Cheers!

Uttam
=================

 

[dk]

Re: Read formats in Excel, use them to import from A2K to Ex

Hi Uttam,

Here is an example of writing a query to a specific sheet within an Excel file.

To read a cell value from within Access you could use automation e.g. :

Sub GetValueFromCell()
    Dim oExcelApp As Object, oExcelWB As Object


    Set oExcelApp = CreateObject("Excel.Application")
    Set oExcelWB = oExcelApp.workbooks.Open("C:\some folder\some file.xls")

    'Read value from sheet 'MySheet', range B2
    MsgBox oExcelWB.worksheets("MySheet").range("B2").Value


    'Close and clean
    oExcelWB.Close False
    Set oExcelWB = Nothing
    oExcelApp.Quit
    Set oExcelApp = Nothing

End Sub

You could also use ADO to read and write values to cells within a workbook. The advantage of this is that you can use standard SQL statements to do this. See this Microsoft example for more details. http://support.microsoft.com/default.aspx?scid=kb;en-us;278973

 

[u0107]

Re: Read formats in Excel, use them to import from A2K to Ex

Hello Dan,

I could not wait to tell you that the Reading Code you have given in your posting works just perfectly!

I am going to try the writing bit a few hours later once I have coded the rest of the logic in my VBA Source for the Access Application.

Thanks a million!

Cheers!

Uttam