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

[Ebraham]

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.

[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]

 

[Micron]

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

I don't understand that statement. If 5 is in column E, you want something to return 5? To where?

 

[Ebraham]

I don't understand that statement. If 5 is in column E, you want something to return 5? To where?

if you will run the above macro there will be two promt of input box to enter the range for FilmRunTimeMinutes.

And based on the range entered, the data will be extracted from the Aceess.

Now what i want is , i do not want the user to enter the range again and again.

So whatever the number is entered in cell E2(minlength) till lastcell(maxlength) of column E then it should extract accordingly.

 

[xenou]

So whatever the number is entered in cell E2(minlength) till lastcell(maxlength) of column E then it should extract accordingly.

According to your code, the input box gives two values, minimum film length and maximum film length.

How do these two values correspond to cell E2(minLength) and LastCell(maxLength)? It sounds like you are saying you have two cells, one that has the first value for minimum film length and one that has the second value for maximum film length. But E2 and LastCell is not two cells. It is maybe one cell (E2) and something else called LastCell, which is unclear.

 

[Micron]

if you will run the above macro there will be two promt of input box to enter the range for FilmRunTimeMinutes.
And based on the range entered, the data will be extracted from the Aceess.
Now what i want is , i do not want the user to enter the range again and again.
So whatever the number is entered in cell E2(minlength) till lastcell(maxlength) of column E then it should extract accordingly.

So what I get out of your answer and re-reading the code, you are prompted 2x for minlength and maxlength values, these are passed to the sql generating function which is used to open a recordset after a connection is opened and the recordset contents are dumped in below the column headings that were just written by the code. The dump looks like a one-time operation to me, so I'm not understanding where the prompt to enter the range comes up "again and again". By that statement you could mean the prompt comes up 2x the next time you want to run this operation, or you could mean you're getting it for every record being dumped into the spreadsheet. If you mean the latter, I don't see how this is happening since you only call the function once and there is no loop involved in pasting the records to the spreadsheet.

If you mean you don't want to have to provide the length values the next time you run this operation, you have to provide a means of letting the user decide whether to use the input box or get the lengths from your spreadsheet. Hint: you can use an Excel user form with two textboxes; one for min, one for max and get these values with one prompt instead of two. This would also allow you to use a control (combo box, option buttons, etc) to let user decide where to get the lengths from. Sorry if my lack of understanding frustrates you, but you have to realize you have the benefit of fully understanding the process and structure whereas I have only little pieces of it.

 

[Ebraham]

According to your code, the input box gives two values, minimum film length and maximum film length.

How do these two values correspond to cell E2(minLength) and LastCell(maxLength)? It sounds like you are saying you have two cells, one that has the first value for minimum film length and one that has the second value for maximum film length. But E2 and LastCell is not two cells. It is maybe one cell (E2) and something else called LastCell, which is unclear.

1) I want to remove the input box.
2) Now in columnE there will be numbers(FilmRunTimeMinutes).

 

[Ebraham]

1) I want to remove the input box.
2) Now in columnE there will be numbers(FilmRunTimeMinutes) as shown below. In the below example there are numbers in only 3 cells. But there can be 100 or more cells filled with numbers in column E.
ColumnE
12000
12500
13500
3) Now when i run the above macro i am entering 12000 in first inputbox and 13500 in the second. Its extracts from access whatever comes between that range.
4) I want the user to extract without the inputbox. The condition should be picked from column E and extraction should be done accordingly.

 

[xenou]

Change your getsql sub:

'minlength = Application.InputBox("Enter the shortest runtime", Type:=1) '--Remove
'maxlength = Application.InputBox("Enter the longest runtime", Type:=1) '--Remove

minlength = Worksheets(1).Cells(1, 5).Value
maxlength = Worksheets(1).Cells(Rows.Count, 5).End(xlUp).Value

Worksheets(1) should be changed to whatever worksheet is correct.
I recommend you always dim all integers as LONG because rows in Excel can exceed the max value for INTEGER
Therefore,

Dim minlength As Long, maxlength As Long

 

[Ebraham]

Change your getsql sub:

'minlength = Application.InputBox("Enter the shortest runtime", Type:=1) '--Remove
'maxlength = Application.InputBox("Enter the longest runtime", Type:=1) '--Remove

minlength = Worksheets(1).Cells(1, 5).Value
maxlength = Worksheets(1).Cells(Rows.Count, 5).End(xlUp).Value

Worksheets(1) should be changed to whatever worksheet is correct.
I recommend you always dim all integers as LONG because rows in Excel can exceed the max value for INTEGER
Therefore,

Dim minlength As Long, maxlength As Long

maxlength = Worksheets(1).Cells(Rows.Count, 5).End(xlUp).Value

The above code does not work as there are some blank cells with formula.

Is there a way to create a loop ? Because i require to extract only the required data(numbers entered in the colE) and not the range.

 

[xenou]

how many values do you need? I thought you needed the first and last only? What would looping do? And what blanks, what formulas? Please be specific. Give Column Letters and Row Numbers of where things are exactly!!