Create Excel tabs and move them to ACCESS

Smphilli

New Member
Joined
Oct 26, 2008
Messages
10
I have a requirement to take an excel report, do much manipulation to the data and create a new worksheet with the data. This part is done.

I decomposed the data so that I could feed it into an Access file with multiple required tables.

I would like to create a table directly from excel and inject it into the access database.

Is this possible? Is it possible to take an existing workbook, open it, move a tab (or multiple tabs) into an access data base as a predefined table name?

Additionally, if the database has the table already, but you want to replace it, can you bypass the warning / querry to replace the existing?
 

Excel Facts

How to change case of text in Excel?
Use =UPPER() for upper case, =LOWER() for lower case, and =PROPER() for proper case. PROPER won't capitalize second c in Mccartney
Fazza,
I got the post, have read and am trying to apply. You left with the thought that it could be pulled from multiple worksheets and/or multiple excel files all at once. That is what I am truly interested in.

I have a loop that will take the same excel page and make multiple pages in the same workbook based on that one page. The multiple pages correspond to multiple tables I would like to make in multiple base Access databases.

So,
1. Should I, as I make the specific sheets that go to a single data base make a seperate workbook? If so, how can I do that? It would actually work better if the seperate workbooks could be named 1 - n for simplicity.
2. Once all the workbooks are made, how can I then cycle through each workbook, import the tables into the base database and then save and move onto the next workbook?

Thanks in advance
Shawn
 
Upvote 0
Hi Shawn.

Your descriptions are good however there is still some chance for misunderstanding (on my part).

The first post asked if it was possible to take data from an Excel workbook and move multiple worksheets into an Access file. This I believe is now answered - it is possible.

In post #4 a workbook with multiple worksheets to go into multiple databases is described. (I wonder why the multiple worksheets are required? Might they be just different filtered subsets of a single large table? In which case the filtering could be done in SQL from the single starting table without the extra step of creating multiple worksheets. A bit like this example done in Excel http://www.mrexcel.com/forum/showthread.php?t=324454 In that thread one worksheet was split into multiple worksheets. It is trivial to change the connection and destination table reference so that the data went into multiple Access tables instead of multiple Excel worksheets.)

For your question numbered 1., this is now a question about Excel and I'm not 100% clear on the situation. So, I can not answer that. If this makes sense, it might not really matter whether you create multiple worksheets or multiple workbooks - if you even need to! Just structure it how you think is best and that should be OK. If it turns out later that you find out a different approach is better, you can change it then if you want to.


For 2., there are two approaches. Basically
  1. Open an ADO connection, loop through each source data file and loop again for each table to be populated. For each source table have a ADO_connection.Execute command to do the work. Basically just input the SQL. [I'm sure there will be examples of this in numerous old forum threads but I can't immediately recall any. Suggest you google for some. If I have some time next week I will search for examples.] Or,
  2. For each table to be populated, create a single SQL command that pulls the data from all source tables at once.
Suggest you use the first.

Regards, Fazza
 
Upvote 0
Fazza,
I just noticed your location, how's the weather?

Let me explain a bit more.

I have a process to manipulate data out of a standardized spreadsheet.

Each worksheet in the workbook looks exactly like one of many ACCESS database tables in a single ACCESS database.

Task 1: I want to make the requesit excel worksheets from the base document changing certain variables and save them in their own workbook. This will give me, say ten, excel documents that look exactly alike except some slight variable changes.

Task 2: I want to extract from each excel workbook the worksheets and put them into a standard access database, save it under a new name. Then move to the next in the series of ten workbooks.

I believe the ADO connection will work well. I am just not sure how to:
1. With having just a single source document open, make and save workbooks.
2. Without opening those created documents, extract the worksheets I need and simply replace the pre-existing tables in the generic Access database.
3. Then save that "changed" generic database under a new name.

I will be toying with it today. I simply have a lot of VBA code that does the first half that I do not want to abandon. I have been emporting the worksheets manually, but I have to do this hundreds of times in a row and would simply like to automate the process.

Shawn
 
Upvote 0
Fazza,
I tried your original bit of code and successfully got a worksheet in my excel document to write into my access database. So, that part is done.

One Step Closer.

Whippy!
 
Upvote 0
From your pm
Fazza,
You gave me a lot of help putting data into an access database and I have tried many versions of extracting from the same database back into the excel document. I can't seem to make any of them work.

You provided this code and I really just need the reverse of the same. Is there such a thing?

Sub demo()
Dim objRS As Object
Set objRS = CreateObject("ADODB.Recordset")

objRS.Open "INSERT INTO MyTable SELECT * FROM [Sheet1$] IN '" & ThisWorkbook.FullName & "' 'Excel 8.0;'", _
"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & "D:\test\test_db.mdb"

Set objRS = Nothing
End Sub

Maybe like http://www.mrexcel.com/forum/showthread.php?t=320723

If you search for "recordset copyfromrecordset" and similar you should find many posts. copyfromrecordset will put the data from a rs to a wks range WITHOUT the headers: you need to separately add them. Again many posts will show this too. Search should find them. Maybe like,
Code:
Sub ADO_to_newWbk()
 
  Dim i As Long
  Dim strConn As String
  Dim strSQL As String
  Dim objRS As Object
  Dim wbkNew As Workbook
 
  strConn = Join$(Array("Provider=Microsoft.Jet.OLEDB.4.0; Data Source=", _
      ActiveWorkbook.FullName, ";Extended Properties=""Excel 8.0;"""), vbNullString)
 
  strSQL = Join$(Array( _
      "SELECT *", _
      "FROM MyData", _
      "WHERE whatever = 999", _
      "ORDER BY somefield"), vbCr)
 
  Set wbkNew = Workbooks.Add(template:=xlWBATWorksheet)
 
  Set objRS = CreateObject("ADODB.Recordset")
  With objRS
    .Open strSQL, strConn
    wbkNew.Worksheets(1).Cells(2, 1).CopyFromRecordset objRS
    For i = 0 To .fields.Count - 1
      wbkNew.Worksheets(1).Cells(1, i + 1).Value = .fields(i).Name
    Next i
    .Close
  End With
  Set objRS = Nothing
  Set wbkNew = Nothing
End Sub
 
Upvote 0

Forum statistics

Threads
1,213,535
Messages
6,114,198
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top