10 Replies - 12780 Views - Last Post: 08 November 2010 - 12:52 AM Rate Topic: -----

#1 arv1980  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 7
  • Joined: 25-October 10

Merge Cells & Dynamic Range in Excel

Posted 26 October 2010 - 12:00 AM

Hello All,

I want a small help in Excel. I have created a project which has 7 tables. I have placed a Command button which helps me to export data from Access database to Excel worksheet (all tables data in one worksheet). I was able to get to the first part(with help of dreamincode) i.e. Export data of first table from access to excel. The problem is that the next table columns are to be place just below the first table columns. For this I will have to merge 2 columns i.e. Site & Description in one column named description. Also I need to specify range dynamically for the next tables since it will depend on data of previous tables. The table looks something like this.
Engineer Status
In Time: Out Time: Name Site ID: Description: Ticket# Result Medium of complaint

Doctors Status
In Time: Out Time: Name Description: Ticket# Result

& So on...

Thanking you & Waiting For Your Kind Reply.

Regards
Amit

Note: For first table I took reference from this link: http://www.dreaminco...cel-through-vb/

Is This A Good Question/Topic? 0
  • +

Replies To: Merge Cells & Dynamic Range in Excel

#2 arv1980  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 7
  • Joined: 25-October 10

Re: Merge Cells & Dynamic Range in Excel

Posted 26 October 2010 - 12:37 AM

I was able to resolve the Merge issue using
oSheet.Range(oSheet.Cells(15, 4), oSheet.Cells(15, 5)).Merge
can someone help me after this.
Was This Post Helpful? 0
  • +
  • -

#3 Ionut  Icon User is offline

  • D.I.C Lover
  • member icon

Reputation: 385
  • View blog
  • Posts: 1,057
  • Joined: 17-July 10

Re: Merge Cells & Dynamic Range in Excel

Posted 26 October 2010 - 12:54 PM

If you know the number of columns and the last good row(as I saw in the link, there was something like recordset count), so selecting dinamically a range should be:

Set aRange = oSheet.Range("A"&CStr(iLastGoodRow + 1) ":D/>"& cStr(iLastGoodRow + 1)) 'Select the range A20:D20, for example



if you don't know the number of columns:
Set aRange = oSheet.Range("A"&CStr(iLastGoodRow + 1) ":"&Chr(65 + iAnyNumberOfColumns) & cStr(iLastGoodRow + 1)) 'see ASCII table, 65 corresponds to A, and supposing the iAnyNumberOfColumns isn't greater then 25. Otherwise you should write some code to write in AA, AB etc



if you want something else, give me a more detailed description.

Good luck!
Was This Post Helpful? 1
  • +
  • -

#4 thava  Icon User is offline

  • D.I.C Lover
  • member icon

Reputation: 179
  • View blog
  • Posts: 1,599
  • Joined: 17-April 07

Re: Merge Cells & Dynamic Range in Excel

Posted 26 October 2010 - 04:30 PM

just a suggestion
why don't you insert table in a new sheet
and one more thing unless you post your code we couldn't help you
Was This Post Helpful? 0
  • +
  • -

#5 Guest_arv1980*


Reputation:

Re: Merge Cells & Dynamic Range in Excel

Posted 27 October 2010 - 10:06 PM

Thanks a lot Ionut :-) It worked for me.
Now I am facing a couple of issues more.
One is bordering...the heading should be bordered in doublelines while the data should be bordered as normal lines. I tried couple of examples from web but it did not work for me. If you can just give me a hint I would work on that lines.
Second thing I have added a Date picker on the form. When user selects a date it should fetch values for that day and next day between 8:00 am. like if I select the date for report as 10/20/2010 then it should fetch data for 20th 08:00 am to 21st 8 am
This is the query I used
"SELECT * FROM ServiceDesk WHERE Starttime >= # " & DTDailyOps.Value & "And StartTime <=#" & TimeValue(DTDailyOps.Value) & "# AND Starttime <= #" & DTDailyOps.Value + 1 & " #" + "ORDER by Starttime ASC", cn, adOpenStatic, adLockOptimistic

this is how my data gets stored 10/21/2010 6:53:00 AM

Thanks,

Amit
Was This Post Helpful? 0

#6 arv1980  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 7
  • Joined: 25-October 10

Re: Merge Cells & Dynamic Range in Excel

Posted 29 October 2010 - 08:35 AM

Can someone help please?????
Was This Post Helpful? 0
  • +
  • -

#7 Ionut  Icon User is offline

  • D.I.C Lover
  • member icon

Reputation: 385
  • View blog
  • Posts: 1,057
  • Joined: 17-July 10

Re: Merge Cells & Dynamic Range in Excel

Posted 29 October 2010 - 03:03 PM

You're welcome.

For your bordering issue
Dim tmpRange As Range
Dim cell As Range

'for header
Set tmpRange = ActiveSheet.Range(Cells(iTableStartRow, iTableStartColumn), Cells(iTableStartRow, iTableEndColumn))
For Each cell In tmpRange
    cell.BorderAround xlContinuous, xlMedium, xlColorIndexAutomatic
Next cell


This is a snippet that should lead you on the right path. For the rest of the table, set the range from iTableStartRow + 1 and iTableEndRow(that would probably be iTableStartRow + rs.RecordCount) and for the first row in the table use
cell.Borders(xlEdgeRight).LineStyle = xlContinuous
cell.Borders(xlEdgeRight).Weight = xlThin


and for the rest it is easier to use BorderAround.

For the select statement, it's enough to send as parameter the date and time:
select * from Table1 where StartTime >= da and StartTime <=DateAdd("d",1,da)


If you put this query in Access and instead of da you put(for example) "10/27/2010 8:00AM", the query will return the records between the to dates and times.

Good luck,
Ionut
Was This Post Helpful? 1
  • +
  • -

#8 arv1980  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 7
  • Joined: 25-October 10

Re: Merge Cells & Dynamic Range in Excel

Posted 31 October 2010 - 01:21 PM

Thanks for your reply Ionut but for some reasons its not working as I want. I have tried to run query my way and even ur way but its not giving me results as expected. This is how my code looks like
da = DTDailyOps.Value
da1 = DateAdd("d", 1, da)
rsRecordset.Open "SELECT * FROM ServiceDesk WHERE Starttime >= #" & da & "# AND Starttime <=#" & da1 & "# ORDER by Starttime ASC", cn, adOpenStatic, adLockOptimistic
Set oExcel = CreateObject("Excel.Application")

Set oBook = oExcel.Workbooks.Add
Dim DataArray(1 To 200, 1 To 8) As Variant
Dim r As Integer
Dim NumberOfRows, NumberOfRows1 As Integer
NumberOfRows = rsRecordset.RecordCount
If NumberOfRows <> 0 Then rsRecordset.MoveFirst
For r = 1 To NumberOfRows
DataArray(r, 1) = rsRecordset.Fields(0)
DataArray(r, 2) = rsRecordset.Fields(1)
DataArray(r, 3) = rsRecordset.Fields(2)
DataArray(r, 4) = rsRecordset.Fields(3)
DataArray(r, 5) = rsRecordset.Fields(4)
DataArray(r, 6) = rsRecordset.Fields(5)
DataArray(r, 7) = rsRecordset.Fields(6)
DataArray(r, 8) = rsRecordset.Fields(7)
rsRecordset.MoveNext
Next

This is my data

StartTime
10/25/2010 8:27:00 PM
10/25/2010 8:40:00 PM
10/25/2010 10:05:00 PM
10/28/2010 8:39:00 PM
10/28/2010 8:52:00 PM
10/28/2010 10:00:00 PM
10/28/2010 1:12:00 AM
10/29/2010 7:13:00 PM
10/29/2010 6:51:00 PM
10/30/2010 1:04:00 AM

When I try to run query for 28th Oct, it gives me this result in excel file.
10/28/2010 20:39
10/28/2010 20:52
10/29/2010 18:51
10/29/2010 19:13

But this output is wrong. I am not able to figure out where is it going wrong. I tried to save date in variable and tried to run query. I also tried to take input directly from date time picker but no good. In Date Time Picker I am entering date and time both. It looks something like 10/28/2010 08:00. Please help.

Regards,

Amit
Was This Post Helpful? 0
  • +
  • -

#9 Ionut  Icon User is offline

  • D.I.C Lover
  • member icon

Reputation: 385
  • View blog
  • Posts: 1,057
  • Joined: 17-July 10

Re: Merge Cells & Dynamic Range in Excel

Posted 31 October 2010 - 03:36 PM

I don't know what goes wrong for you. With the following code
Dim da As Date, da1 As Date
da = "10/28/2010"
da = da + " 8:00"
da1 = DateAdd("d", 1, da)
Dim rsRecordset As ADODB.Recordset
Dim cn As ADODB.Connection
Set cn = New ADODB.Connection
cn.Open "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Users\Ionut\Desktop\Database3.accdb;User Id=;Password="

Set rsRecordset = New ADODB.Recordset
Dim s As String
s = "SELECT * FROM Table1 WHERE Starttime >=#" & CStr(da) & "# AND Starttime <=#" & CStr(da1) & "# ORDER by Starttime ASC"
rsRecordset.Open s, cn, adOpenStatic, adLockOptimistic
Dim DataArray(1 To 200, 1 To 8) As Variant
Dim r As Integer
Dim NumberOfRows, NumberOfRows1 As Integer
NumberOfRows = rsRecordset.RecordCount
If NumberOfRows <> 0 Then rsRecordset.MoveFirst
For r = 1 To NumberOfRows
DataArray(r, 1) = rsRecordset.Fields(0)
DataArray(r, 2) = rsRecordset.Fields(1)

rsRecordset.MoveNext
Next


and the data for Starttime you mentioned, I get the correct results. Did you try to debug your code and put some watches? Maybe this way you see what is wrong. Another idea :try setting da from your code(hardcoded)
Was This Post Helpful? 0
  • +
  • -

#10 arv1980  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 7
  • Joined: 25-October 10

Re: Merge Cells & Dynamic Range in Excel

Posted 07 November 2010 - 07:13 PM

Thanks Ionut..It worked...Its been more than four years since I have touched VB so my questions and issues sound stupid and I have never worked on Excel via VB that is what is causing issues... this is What I did ... Took a fixed date & time in a variable and then started comparing it with the recordset fields.. If the condition is true then it displays a message box. What I found was that time from recordset was getting displayed in double quotes("")... When I checked I found that the time field in recordset had by some mistake got converted to Memo Field.. I changed it to time field and it started working.... Thanks a lot again :-) .... I cannot appreciate you enough for this...U r just fabulous :-)
Was This Post Helpful? 0
  • +
  • -

#11 Ionut  Icon User is offline

  • D.I.C Lover
  • member icon

Reputation: 385
  • View blog
  • Posts: 1,057
  • Joined: 17-July 10

Re: Merge Cells & Dynamic Range in Excel

Posted 08 November 2010 - 12:52 AM

don't mention it. I'm glad to see the problems were solved
Was This Post Helpful? 0
  • +
  • -

Page 1 of 1