Solved

Import Data from Unsaved Excel Spreadsheet to Access Table

Posted on 2006-07-08
20
1,057 Views
Last Modified: 2012-08-14
I have an Access 2000 database application that creates and populates an Excel spreadsheet.

The spreadsheet then does some calculations and generates some new data.  I would like to import this data back into one of my Access tables.

How do I do this?

I looked at the TransferSpreadsheet method, but it has a couple of problems:
1) It requires a filename, but I have not yet stored my spreadsheet so it doesn't have a name
2) It doesn't let you specify a worksheet name and my workbook has multiple worksheets

I am just using the spreadsheet because it has functionality that I can't replicate in Access (e.g. Solver, Normsinv() function, etc.)  so I don't want to have to save it.  And I need the multiple worksheets because of the shear quantity and complexity of the data I am working with.

Any ideas?

For now I am just going to use a brute force technique of creating INSERT statements data field by data field as I need to get something working this weekend.  But I would appreciate something quicker and more elegant.

Thanks.
0
Comment
Question by:dastrw
  • 8
  • 4
  • 4
  • +3
20 Comments
 
LVL 44

Expert Comment

by:GRayL
ID: 17066686
Check:

docmd.transferspreadsheet acimport

If you enter this part of the method from the Immediate Pane (get there by typing Ctl-G) you will be prompted for the rest of the input, including the 'range' which can be expressed as a WorkBookName!WorkSheetName.
0
 
LVL 34

Expert Comment

by:jefftwilley
ID: 17066690
If you're writing data out to one sheet, and you wish to bring data back in from another simultaneously, you can save the spreadsheet temporarily, link to the spreadsheet to grab the data, do whatever with the data, then delete the link and spreadsheet...all within a function.  
I know my explan is rather cryptic, but if you provide a few details, this is doable.
J
0
 
LVL 2

Author Comment

by:dastrw
ID: 17066865
GRayL,

I am doing this programmatically.  So I need a solution that will work in VBA.  The last time I tried to pass an object like a worksheet in a place where it expected a string, I got an unrecoverable error.

jefftwilley,

Yes I know I can do what you want, but the sheet I create is going to be kept for debugging purposes only.  Basically I want to generate the sheet, fill it with data, copy the data back to my database, and then present the sheet to the user.  He can choose to ignore it, look at it and delete it, or save it off.  But I won't know which, and I need to save the data to the database before he makes his decision.

Anyone will be able to use the application from any PC.  I don't want to have my application write files to other people's PCs without them knowing about it.  Especially if I don't need to do it.
0
 
LVL 34

Expert Comment

by:jefftwilley
ID: 17066889
How are you creating the spreadsheet now? Are the "results" on the same sheet as the "Input Data"?
0
 
LVL 2

Author Comment

by:dastrw
ID: 17066909
jefftwilley,

I added a Reference to the MS Excel library to my Access database.  Then in the button click code on a form I have this code:

  'Create Excel Workbook with Worksheets
  Set excelApp = CreateObject("Excel.application")
  Set wb = excelApp.Workbooks.Add

The cool thing about this is that when it is easier to do things in Access I can do them in access and then write the results to cells in Excel  When it is easier to do things in Excel, then I can use the library code to get Excel to do its thing.

Getting things from Access to Excel is easy using QueryTables.

What I need is something like a reverse QueryTable.

Any ideas?
0
 
LVL 34

Expert Comment

by:jefftwilley
ID: 17066932
You mentioned that you were running calculations to get results.
<<<The spreadsheet then does some calculations and generates some new data.  I would like to import this data back into one of my Access tables.>>>

at the same time, you

How are you getting the formulas into the spreadsheet? are you opening a template that already has them in it?

<<<Basically I want to generate the sheet, fill it with data, copy the data back to my database, and then present the sheet to the user.>>>

Easy enough to open a new worksheet, but unless you're copying formulas to the spreadsheet, there won't be much calculating going on.
0
 
LVL 2

Author Comment

by:dastrw
ID: 17066945
How do I get the formulas into the spreadsheet?

I created a query in Access that outputs the formulas rather than the values.  When I execute the QueryTable, the formulas are written to the cells.  

The only problem is that Excel thinks these are strings so it prepends them with a single quote.

All I need to do is a global search and replace to remove all of the single quotes and then everything works fine.

Also, Excel has things like Solver which Access doesn't.  Anything you can do in Excel directly you can pretty much get Access to do.  With the Excel library I can run Excel commands against the Excel workbook with not too many problems.
0
 
LVL 65

Accepted Solution

by:
rockiroads earned 250 total points
ID: 17067235
ok, reading what u want to do, u want the user to possibly keep the Excel version?
But if u are saving to DB, why bother? U can always query the new table and dump in Excel format anyway

Okay, back to your question. I think the best way is to create and save a temp file


Create a tempname, one simple way is to use datetimesec e.g. sfile = Environ("Username") & format(now(),"YYYYMMDDHHNNSS")

file based on userid and datetime

or u can generate a random word and use that

say u save in c:\myexcel

You can also specify sheet names
e.g. this code renames Sheet1 to George

Sheets(1).Name = "George"


You can then import e.g. Importing a file, I am specifying sheet Geoge and cells A1 to Z10

DoCmd.TransferSpreadsheet acImport, , "mylocaltable", "C:\MyExcel\" & sFile, True, "George$A1:Z100"

Once u do your import, u can delete the file    kill "C:\MyExcel\" & sFile

All this is done without .Visible = True so user not aware of excel file being created,imported and deleted

Now u can output your query in Access. If they want to save it, u can either create a button which outputs using DoCmd.OutputTo and use acFormatXLS. This should prompt the user if u dont specify a filename




Now with what u are doing, I am sure you can do this all in VBA without the use of Excel. but if u do use addins like Solver and if u dont want to do your own calculations, then stick with this
0
 
LVL 65

Expert Comment

by:rockiroads
ID: 17067237
my last comment regarding DoCmd.OutputTo, the alternative is to use Excel auto, like u have done to create a unsaved Excel document but based on the new table
In this case, u can just do CopyFromRecordset
0
 
LVL 1

Expert Comment

by:millsco
ID: 17067540
I see that you have started with Excel automation I.e.
Set excelApp = CreateObject("Excel.application")
  Set wb = excelApp.Workbooks.Add

What you need to do is continue with automation to populate the input cells, calculate, run solver or do whatever you do and then read the output cell results back in to Access with VBA.

A few tips:
1) Create the Excel VBA code in Excel first. An easy way to get started is to step through the process manually with the macro recorder switeched on. Now edit the Excel VBA code removing extranuos commands eg navigation and absolute cell references of these are not appropriate.

Also consider giving meaningful range names to your your input and output cell ranges. You will find the Excel code easier to write, debug and maintain.

2) Once you have the procedure working in Excel then add the automation steps in Access I.e.

Dim strOutputResult
Set excelApp = CreateObject("Excel.application")


With excelApp

'==>> Now copy and paste the Excel VBA code here
'==>> Prefix all of the Excel commands with a '.'

'E.g.
   .Workbooks.Add
   .Range("C5").Value = 'Your input data'
   .calculate

   strOutputResult = .Range("B5").Value
   
   etc
End with

'Now append the result data to your Access table
0
Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

 
LVL 2

Author Comment

by:dastrw
ID: 17074358
rockiroads,

>> But if u are saving to DB, why bother? U can always query the new table and dump in Excel format anyway

I need Excel to do some of the work.  I need Excel to display the results, especially the formulas so that others can verify my results.  The problem is not going from the DB to Excel, but from Excel back to the DB.  If I could do all of the work in Access then I would do it all there and just push the results at the end to Excel, but I can't do that.  Especially since I need Solver to generate some of the results for me.


>> DoCmd.TransferSpreadsheet acImport, , "mylocaltable", "C:\MyExcel\" & sFile, True, "George$A1:Z100"

This might work.  I didn't know you could ad the sheet name this way.  I will try it when I get a chance.  I still don't like the idea of creating a temp file, but I guess that's not so bad especially if I create it in the C:\Temp directory.


millsco:

>> 'Now append the result data to your Access table

This is what I don't know how to do.  Actually I do now either using rockiroads suggestion or what I am currently doing which is brute force creation of INSERT statements that I execute row-by-row:

For irow = 1 to nrow
  dbIns = "insert into table values ("
  for icol = 1 to ncol
     dbins =  dbins & ws.cells(irow,nrow).value & ","
  next icol
  querydef1.sql = dbins
  querydef1.execute
next irow

(The above is a simplification.  there are a few other commands to trim off the last comma, etc.  but you get the idea.)

0
 
LVL 34

Expert Comment

by:jefftwilley
ID: 17074444
If you are changing your mind and are creating a temp file then it is advisable to save as a .csv . You can build an import specification using this file format and use transfer spreadsheet to import the data back into your table. This of course triggered from the user's acceptance of the data as presented to him by Excel.
<off topic>
How was the weekend Rocki? Busy?
J
0
 
LVL 2

Author Comment

by:dastrw
ID: 17074537
jefftwilley,

I really don't want to change my mind.

Here is the problem with saving the spreadsheet:

What I want to happen is:
1) Query basic data from Access
2) Run Excel commands against data to get final data
3) Insert final data back into database for archival purposes
4) Show Excel sheet to user
5) User can choose to Save sheet or delete it by pressing Save button

What will happen if I use a saved spreadsheet:
1) Query basic data from Access
2) Run Excel commands against data to get final data
3) Save Excel sheet to C:\Temp
4) Import data back into Access for archival purposes
5) Show Excel sheet to user
6) If user wants to keep Excel sheet he presses Save button.  But what happens is that spreadsheet gets saved in C:\Temp as some generic name.  The next time he runs the program, if he clicks Save it will overwrite the previous file.  So I have to depend on the person clicking 'Save As' rather than 'Save'.  This shouldn't be too much of a stretch, but it is.  Expecting people to press 'Save As' when they press 'Save' 99% of the time is just too much to expect.

So then I would have to do something like trap the 'Save' function and make it behave as a 'Save As' function and then put some code in 'On Close' to reverse the changes I made so that 'Save' behaves like a 'Save'.

Very messy!

0
 
LVL 1

Assisted Solution

by:millsco
millsco earned 150 total points
ID: 17078867
Ok, sorry I didn't go in to much detail on appending the data back to Access. The approach to use is somewhat dependent on the amount and  'shape' of the data in Excel.

E.g. If the data is in a rectangular range and there is a significant amount of it then I would opt for the docmd.transferspreadsheet acimport option. I have found this process to be fast and reliable.

If your Excel data is "contaminated" with headings, explanatory notes, blank rows/columns, sub-totals, totals (as you do if you want to make it user friendly for reviewing purposes) etc. then docmd.transferspreadsheet acimport will still work but you will have a job to cleanse the data in Access before you can make use of it. My experience of this approach is that data reliability and integrity is impacted by the cleansing operation. I prefer to do the data cleansing in Excel before importing to Access. eg delete extraneous columns and rows etc.

If you are after isolated cells and there are not too many of them then use the automation option as I suggested. Copy the cell contents to variables and then append these variables to a table by looping through a recordset in ADO or DAO to do the appends rather than the Insert methodology (but whatever you find easiest). If there are many data cells to capture in this fashion your coding will be come extremely tedious.

If you are writing data to Excel for others to review, presumably you will want to update feedback in to your database? Because you lose control of the data while the reviewer is working with the Excel file you will need to index or tag the Excel data in some way so that you can relate this to the saved version of the data in your database. You may think about saving the file for the user with a unique name. This way they hit the save button and you can use the unique name to be able to re-import the changes at a later stage. If you have to use a generic file name to create the Excel file why not create the Excel file as read only. This will force the user to do a 'Save As'. You need to make sure you have tagged the data in the spreadsheet as you will have no control of the new 'Saved As' file name to identify the data. If you users are really as inexperienced as you make them out to be you could try saving the identifying data in a Hidden or 'Very Hidden' sheet. Average users usually don't pick these sheets in a workbook.

0
 
LVL 27

Assisted Solution

by:jjafferr
jjafferr earned 100 total points
ID: 17079024
1. Without Saving the Excel file:

The following code will read from an open Excel file,
it will read the values from A1 and B1 of Sheet1,

     Dim xlApp As Object
'    On Error Resume Next
   
    'use the open Excel, if nothing is open, then open a new Excel
    Set xlApp = GetObject(, "Excel.Application")
    If xlApp Is Nothing Then
        Set xlApp = CreateObject("Excel.Application")
        If xlApp Is Nothing Then
            MsgBox "Can't find Excel!", vbCritical
            Exit Sub
        End If
    End If
   
     xlApp.Visible = False   'True    
    Set xlWS = xlApp.Worksheets(1)
'    xlWS.EnableCalculation = False

    MsgBox xlWS.[A1].Cells & vbCrLf & xlWS.[B1].Cells

'    xlApp.Quit
'    Set xlApp = Nothing
-------------------------------------------------------------------
2. Without Saving the Excel file:

The following code will read from a specific open Excel file (C:\myExcelFile.xls),
it will read the values from A1 and B1 of Sheet1,

     Dim xlApp As Object
'    On Error Resume Next
   
    'use the open Excel, if nothing is open, then open a new Excel
    Set xlApp = GetObject(, "Excel.Application")
    If xlApp Is Nothing Then
        Set xlApp = CreateObject("Excel.Application")
        If xlApp Is Nothing Then
            MsgBox "Can't find Excel!", vbCritical
            Exit Sub
        End If
    End If
   
     xlApp.Visible = False   'True    
    Set xlWB = xlApp.Workbooks.Open("C:\myExcelFile.xls")
    Set xlWS = xlWB.Worksheets(1)

    MsgBox xlWS.[A1].Cells & vbCrLf & xlWS.[B1].Cells

'    xlApp.Quit
'    Set xlApp = Nothing
-------------------------------------------------------------------
3. How to "SaveAs"

The following code will copy your original Excel file to a new Excel file,
then will work on the new file,
The new file is saved in C:\Temp:


    Dim xlApp As Object
    On Error Resume Next
   
    'use the open Excel, if nothing is open, then open a new Excel
    Set xlApp = GetObject(, "Excel.Application")
    If xlApp Is Nothing Then
        Set xlApp = CreateObject("Excel.Application")
        If xlApp Is Nothing Then
            MsgBox "Can't find Excel!", vbCritical
            Exit Sub
        End If
    End If
   
     xlApp.Visible = False   'True
     
    NewFile = "C:\Temp\myTempExcelFile_" & Format(Now(), "mm-dd-yy") & ".xls"
    FileCopy "C:\Temp\Chart_Template.xls", NewFile


    'open the following Template excel file
    Set xlWB = xlApp.Workbooks.Open(NewFile)
   
    ' use the following Worksheet
    Set xlWS = xlWB.Worksheets(1)
         
    xlWS.[A1].Cells =1
    xlWS.[B1].Cells = "This will be written in cell B1"
         
    'save the file
  xlWB.Save

'    xlApp.Quit
'    Set xlApp = Nothing
-------------------------------------------------------------------
4. Why not run the Excel formula from Access:

The following link tells you how to run an excel formula from Access, while having all the data in Access:
http://www.experts-exchange.com/Databases/MS_Access/Q_21889077.html
-------------------------------------------------------------------


jaffer
0
 
LVL 2

Author Comment

by:dastrw
ID: 17083141
millsco,

>> docmd.transferspreadsheet acimport option

This would be OK if I could run it against the currently open workbook, but it seems that I can't.  When I get a chance I'm thinking about asking for the path of the currently opened and never saved workbook.  Maybe it has a name like 'C:\Temp\OKIFhje2.wls' or some other random name that MS gave it.  Then maybe I can specify that name in the TransferSpreadsheet function and it will work.  We'll see.

>> looping through a recordset in ADO or DAO to do the appends

Do you have any sample code?  I've never used that method before.

>> If you have to use a generic file name to create the Excel file why not create the Excel file as read only.

Great idea!  Also, I hadn't anticipated gathering feedback from users, but from what you say it may be possible to implement.  So I'll think about it.
0
 
LVL 2

Author Comment

by:dastrw
ID: 17083195
jjafferr,

Thanks for the code on reading individual cells, but I already know how to do that and unfortunately I have over a hundred columns and dozens of rows of data to transfer back to Access.

Your copy template code doesn't appear like it will work for me.

However, the links you provided to including Excel functions in queries is very nice.  I can't use it in this application because I need the functions to be stored in the Excel spreadsheet for viewing by the code reviewers.  However, there are a number of occasions where I would like to include a call to an Excel function in a query.  What I have done in the past is created a table of values in Excel and then copied that to a table in my database and then joined to it for the values.  With the code from the link you sent I can skip the table and join and just execute the function directly.
0
 
LVL 1

Expert Comment

by:millsco
ID: 17086148
Sample ADO recordset code

Dim rs As ADODB.Recordset
Set rs = New ADODB.Recordset
Set rs.ActiveConnection = CurrentProject.Connection
rs.CursorType = adOpenKeyset
rs.LockType = adLockOptimistic
rs.Source = "InputDataTable"
rs.Open options:=adCmdTable

0
 
LVL 1

Expert Comment

by:millsco
ID: 17086188
Sorry above is incomplete, too enthusiastic with tab key

Sample ADO recordset code

Dim rs As ADODB.Recordset
Set rs = New ADODB.Recordset
Set rs.ActiveConnection = CurrentProject.Connection
rs.CursorType = adOpenKeyset  'various options available here use intellisense to choose appropriate
rs.LockType = adLockOptimistic  'various options available here use intellisense to choose appropriate
rs.Source = "InputDataTable"
rs.Open options:=adCmdTable

For
   rs.add
   rs("Field1") = xlCell1  
   rs.update   'only necessary for DAO code but I find maintenance easier if I know where the recordset has been updated
Loop
0
 
LVL 2

Author Comment

by:dastrw
ID: 17086220
millsco,

Looks ok if I just need to add a value or two, but what if I have close to two hundred columns and dozens of rows?

What I'm currently doing doesn't seem much less efficient than this.  I imagine some sort of syntax supports updating or appending multi-column records on one line.  But then that's not much different than what I am doing with creating one insert statement for each row.
0

Featured Post

Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

Join & Write a Comment

I originally created this report in Crystal Reports 2008 where there is an option to underlay sections. I initially came across the problem in Access Reports where I was unable to run my border lines down through the entire page as I was using the P…
Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now