Export Data to excel sheet from Acess with reference to excel cells (Dynamic)

E

Ebraham

Board Regular
Joined
Mar 21, 2015
Messages
215
Hi All,

The below code will extract data from Access in to excel sheet with whatever the user inputs the range between the two input box.

But i want whatever the number is enter in column E should return only that.

Any help or guide is highly appreciated.

Code: 
[TABLE="width: 64"]
 <colgroup><col width="64" style="width:48pt"> </colgroup><tbody>[TR]
  [TD="width: 64"]Option Explicit[/TD]
 [/TR]
 [TR]
  [TD="class: xl63"]'change the path as per suits.[/TD]
 [/TR]
 [TR]
  [TD]Const constraccess As String =  "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Users\Jalal  Kasmani\Desktop\Excel\Excel VBA\Wise Owls & Sujeet\30, 31, 32 connections  SQL\Movies.accdb;Persist Security Info=False;"[/TD]
 [/TR]
 [TR]
  [TD]    [/TD]
 [/TR]
 [TR]
  [TD]Sub copydatafromdatabaselatebinding()[/TD]
 [/TR]
 [TR]
  [TD]    [/TD]
 [/TR]
 [TR]
  [TD]Dim moviesconn As Object[/TD]
 [/TR]
 [TR]
  [TD]Dim moviesdata As Object[/TD]
 [/TR]
 [TR]
  [TD]Dim moviesfield As Object[/TD]
 [/TR]
 [TR]
  [TD]    [/TD]
 [/TR]
 [TR]
  [TD]Set moviesconn =  CreateObject("adodb.connection")[/TD]
 [/TR]
 [TR]
  [TD]Set moviesdata =  CreateObject("adodb.recordset")[/TD]
 [/TR]
 [TR]
  [TD]    [/TD]
 [/TR]
 [TR]
  [TD]moviesconn.ConnectionString =  constraccess[/TD]
 [/TR]
 [TR]
  [TD]moviesconn.Open[/TD]
 [/TR]
 [TR]
  [TD]    [/TD]
 [/TR]
 [TR]
  [TD]On Error GoTo Closeconnection[/TD]
 [/TR]
 [TR]
  [TD]With moviesdata[/TD]
 [/TR]
 [TR]
  [TD]     .activeconnection = moviesconn[/TD]
 [/TR]
 [TR]
  [TD]     .Source = getsqlstring[/TD]
 [/TR]
 [TR]
  [TD]     .locktype = 1[/TD]
 [/TR]
 [TR]
  [TD]     .cursortype = 0[/TD]
 [/TR]
 [TR]
  [TD]     .Open[/TD]
 [/TR]
 [TR]
  [TD]End With[/TD]
 [/TR]
 [TR]
  [TD]    [/TD]
 [/TR]
 [TR]
  [TD]Worksheets.Add[/TD]
 [/TR]
 [TR]
  [TD]    [/TD]
 [/TR]
 [TR]
  [TD]On Error GoTo Closerecordset[/TD]
 [/TR]
 [TR]
  [TD]For Each moviesfield In moviesdata.Fields[/TD]
 [/TR]
 [TR]
  [TD]ActiveCell.Value = moviesfield.Name[/TD]
 [/TR]
 [TR]
  [TD]ActiveCell.Offset(0, 1).Select[/TD]
 [/TR]
 [TR]
  [TD]Next moviesfield[/TD]
 [/TR]
 [TR]
  [TD]    [/TD]
 [/TR]
 [TR]
  [TD]Range("a1").Select[/TD]
 [/TR]
 [TR]
  [TD]Range("a2").CopyFromRecordset  moviesdata[/TD]
 [/TR]
 [TR]
  [TD]Range("a1").CurrentRegion.EntireColumn.AutoFit[/TD]
 [/TR]
 [TR]
  [TD]    [/TD]
 [/TR]
 [TR]
  [TD]On Error GoTo 0[/TD]
 [/TR]
 [TR]
  [TD]Closerecordset:[/TD]
 [/TR]
 [TR]
  [TD]moviesdata.Close[/TD]
 [/TR]
 [TR]
  [TD]Closeconnection:[/TD]
 [/TR]
 [TR]
  [TD]moviesconn.Close[/TD]
 [/TR]
 [TR]
  [TD]    [/TD]
 [/TR]
 [TR]
  [TD]End Sub[/TD]
 [/TR]
 [TR]
  [TD]    [/TD]
 [/TR]
 [TR]
  [TD]Function getsqlstring() As String[/TD]
 [/TR]
 [TR]
  [TD]    [/TD]
 [/TR]
 [TR]
  [TD]Dim minlength As Integer, maxlength As  Integer[/TD]
 [/TR]
 [TR]
  [TD]Dim SQLstring As String[/TD]
 [/TR]
 [TR]
  [TD]    [/TD]
 [/TR]
 [TR]
  [TD]minlength =  Application.InputBox("Enter the shortest runtime", Type:=1)[/TD]
 [/TR]
 [TR]
  [TD]maxlength =  Application.InputBox("Enter the longest runtime", Type:=1)[/TD]
 [/TR]
 [TR]
  [TD]    [/TD]
 [/TR]
 [TR]
  [TD]SQLstring = "SELECT FilmName,  FilmReleaseDate, FilmRunTimeMinutes " & _[/TD]
 [/TR]
 [TR]
  [TD]     "From tblFilm " & _[/TD]
 [/TR]
 [TR]
  [TD]     "WHERE FilmRunTimeMinutes BETWEEN " & minlength & _[/TD]
 [/TR]
 [TR]
  [TD]     " And " & maxlength & _[/TD]
 [/TR]
 [TR]
  [TD]     " ORDER BY FilmRunTimeMinutes ASC;"[/TD]
 [/TR]
 [TR]
  [TD]    [/TD]
 [/TR]
 [TR]
  [TD]getsqlstring = SQLstring[/TD]
 [/TR]
 [TR]
  [TD]    [/TD]
 [/TR]
 [TR]
  [TD]End Function[/TD]
[/TR]
</tbody>[/TABLE]
 
Sort by date Sort by votes
The changes you provided does not work. Bcoz when you xlup in column E it stands E1000. This is bcoz the formula is entered from cell E2 to E1000.
Only cell E2,E3 and E4 has numbers all other cells are blank this time.
But this will not be the case all the time,numbers can be found with formula from E2 till E150 or E120... etc and all the other cell will remain blank till E1000.
I want to extract from access depending on each number entered in column E and not for the range as my macro above nor for the first and last number in ColE. I want the extract for each number.
 
Upvote 0

Excel Facts

Back into an answer in Excel
Use Data, What-If Analysis, Goal Seek to find the correct input cell value to reach a desired result
Well I'm lost because your sql statement can only use two numbers:
Code: 
SQLstring = "SELECT FilmName,  FilmReleaseDate, FilmRunTimeMinutes " & _

     "From tblFilm " & _

     "WHERE FilmRunTimeMinutes BETWEEN " & minlength & _

     " And " & maxlength & _

     " ORDER BY FilmRunTimeMinutes ASC;"

So how can it do you any good to get each number when you can only use two?
 
Upvote 0
This is an approach to getting your max and min, assuming there are blanks or empty cells (but it will count cells with the actual number zero in them if such cells are present).

It's still not clear to me what you want to do with EACH number when your query is using only a MinLength and MaxLength as variables (that's only two numbers).

Code: 
Sub foo()

Dim c As Range
Dim r As Range
Dim minLength As Long
Dim maxLength As Long

Set r = Worksheets(1).Range("E1:E1000")
minLength = 2147483647
maxLength = -2147483648#

For Each c In r
    If IsNumeric(c.Value) And Len(c.Value) > 0 Then
        If c.Value = myMin(c.Value, minLength) Then
            minLength = c.Value
        End If
        If c.Value = myMax(c.Value, maxLength) Then
            maxLength = c.Value
        End If
    End If
Next c

Debug.Print minLength
Debug.Print maxLength

End Sub

Private Function myMin(ByRef a As Long, ByRef b As Long) As Long
    If a <= b Then
        myMin = a
    Else
        myMin = b
    End If
End Function

Private Function myMax(ByRef a As Long, ByRef b As Long) As Long
    If a >= b Then
        myMax = a
    Else
        myMax = b
    End If
End Function
 
Upvote 0
I had copied the code from internet.
Where to enter your code. Can you edit the code and paste the edition and give me.
Did you used my code and saw what is that code doing.if not then that code is extracting from access with whateever is entered in inputbox based on min and maxlenght and whatever comes in between.
You can remove min and maxlength idea. U have to loop in colE where numbers are found - go to access - extract-paste on the samesheet of excel.

If I am still not clear then tell me I will give you the link of excel and access sheet link.
 
Upvote 0
Your query is written as follows:
Select ... From .... Where FilmRunTimeMinutes BETWEEN minLength and maxLength

So you must have a minLength and a maxLength when you run the query.
If you want to do that for each number, you have only one number. Let's say the number is 150.
In that case do you want your query to be:
Select ... From .... Where FilmRunTimeMinutes BETWEEN 150 and 150
Because that is the same as:
Select ... From .... Where FilmRunTimeMinutes = 150
 
Upvote 0
xenou said:
Your query is written as follows:
Select ... From .... Where FilmRunTimeMinutes BETWEEN minLength and maxLength

So you must have a minLength and a maxLength when you run the query.
If you want to do that for each number, you have only one number. Let's say the number is 150.
In that case do you want your query to be:
Select ... From .... Where FilmRunTimeMinutes BETWEEN 150 and 150
Because that is the same as:
Select ... From .... Where FilmRunTimeMinutes = 150
Click to expand...

I do not have any clue about Access and SQL.
You can change the query as per my need.

Yes. I want to do for each number which are there in column E.
if i have numbers in cell E2,E3,E4 ie 156,1425,1654.

Note: Numbers will keep changing and the range can also change from E2:E4 to E2:E100.
 
Upvote 0
if i have numbers in cell E2,E3,E4 ie 156,1425,1654.
Click to expand...
So that means running three queries. Where do you want the query results to go?

I do not have any clue about Access and SQL.
You can change the query as per my need.
Click to expand...
I can't really write your queries and all your code for you. If you really don't know about Access or SQL then you shouldn't be doing this kind of thing!
 
Upvote 0
xenou said:
So that means running three queries. Where do you want the query results to go?
Click to expand...

There can be numbers in cell E2:E4 or it can increase or decrease. it should be saved in the same excel sheet.

I can't really write your queries and all your code for you.
Click to expand...
I dont need all the code from you.. just an editions are required.

If you really don't know about Access or SQL then you shouldn't be doing this kind of thing!
Click to expand...

Please do not tell me what i should do and what i should i not do. i m here to learn and i will learn. i am here from last six months and this is the first time i had written my post around 4-5 times just to explain what i required. Being a Excel MVP this was not at all expected from you.
 
Upvote 0
Your code won't work because you are (I guess) wanting to run multiple queries but they all get copied to the same place so every query will overwrite the last one.

If minLength and maxLength are the same (because you only have one number, not two) I suggest you call the variable something like filmLength and change the SQL to Select ... From ... Where FilmRunTimeMinutes = filmLength. However, it will still work if you make both minLength and maxLength the same and run it as it is.

At this point I will bow out of this thread. Good luck with your project.
 
Upvote 0
xenou said:
Your code won't work because you are (I guess) wanting to run multiple queries but they all get copied to the same place so every query will overwrite the last one.

If minLength and maxLength are the same (because you only have one number, not two) I suggest you call the variable something like filmLength and change the SQL to Select ... From ... Where FilmRunTimeMinutes = filmLength. However, it will still work if you make both minLength and maxLength the same and run it as it is.

At this point I will bow out of this thread. Good luck with your project.
Click to expand...

Ok.. Np
 
Upvote 0
You must log in or register to reply here.

Similar threads

T
  • Question Question
Clear table in Access from Excel
Replies
2
Views
664
tiredofit
T

Forum statistics

Threads
1,225,725
Messages
6,186,650
Members
453,367
Latest member
bookiiemonster

Share this page

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