By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
425,811 Members | 785 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 425,811 IT Pros & Developers. It's quick & easy.

export MsAccess data to Excel

P: 1
Hello everyone,

I created a database in Access and on my form, I inserted a button that exports data into a ready Excel template. The export works fine, but I have one problem and it's from my code. First of all, the data from the Access database is inserted in an Excel calendar. The calendar is numbered with days 36 to -18. By exemple, if a calendar statement is for day 36, then it will be inserted in the specific column. For now, statements that belong to day 36 to 30 are inserted in the specific columns, but statements that belong to day 29 to 23 and so on are not inserted where they have to. These statements are inserted in the same columns with the previous statements from day 36 to day 30.
There's a copy of my code for the export:

Expand|Select|Wrap|Line Numbers
  1. Private Sub Command28_Click()
  2.  
  3.     Dim db As DAO.Database
  4.     Dim rst As DAO.Recordset, RstDay As DAO.Recordset
  5.     Dim appexcel As Excel.Application
  6.     Dim wbexcel As Excel.Workbook
  7.  
  8.     Dim I As Integer, j As Integer
  9.     Dim LastLigne As Boolean
  10.     Set db = CurrentDb
  11.  
  12.     Set RstDay = db.OpenRecordset("Select Distinct EventDay From TblCalendar")
  13.  
  14.     Set appexcel = CreateObject("Excel.Application")
  15.     appexcel.Visible = True
  16.     Set wbexcel = appexcel.Workbooks.Open("C:\Documents and Settings\mconea\My Documents\EMS\EMS Calendar Excel.xls")
  17.  
  18.     appexcel.Sheets("Sheet1").Select
  19.  
  20.  
  21.     Do Until RstDay.EOF
  22.         I = 4
  23.         ' Recherche de la colonne  remplir
  24.         Select Case RstDay("EventDay")
  25.             Case 36
  26.                 j = 1
  27.  
  28.             Case 35
  29.                 j = 2
  30.  
  31.             Case 34
  32.                 j = 3
  33.  
  34.             Case 33
  35.                 j = 4
  36.  
  37.             Case 32
  38.                 j = 5
  39.  
  40.             Case 31
  41.                 j = 6
  42.  
  43.             Case 30
  44.                 j = 7
  45.  
  46.             Case 29
  47.                 j = 1
  48.  
  49.             Case 28
  50.                 j = 2
  51.  
  52.             Case 27
  53.                 j = 3
  54.  
  55.             Case 26
  56.                 j = 4
  57.  
  58.             Case 25
  59.                 j = 5
  60.  
  61.             Case 24
  62.                 j = 6
  63.  
  64.             Case 23
  65.                 j = 7
  66.         End Select
  67.  
  68.         LastLigne = False
  69.  
  70.         Do Until LastLigne
  71.             If appexcel.Cells(I, j) <> "" Then ' si la cellule n'est pas vide la ligne avance
  72.                 I = I + 1
  73.             Else
  74.                 LastLigne = True
  75.             End If
  76.         Loop
  77.  
  78.         Set rst = db.OpenRecordset("Select * From TblCalendar Where EventDay='" & RstDay("EventDay") & "'")
  79.  
  80.         Do Until rst.EOF
  81.             appexcel.Cells(I, j) = rst![Calendar_E]
  82.             I = I + 1
  83.             rst.MoveNext
  84.         Loop
  85.         RstDay.MoveNext
  86.     Loop
  87.  
  88. Set xlSheet = Nothing
  89. Set xlBook = Nothing
  90. Set xlApp = Nothing
  91.  
  92. End Sub

For exemple, if a statement belong to day 36, that means Case 36 and j=1 for the line. All of statements begin to be inserted at I=4. I tried to insert another loop after Case 30 so that line for the statements that are for days 29 to 23 begin at I=21, but it didn't worked. That's why statements for days 29 to 23 are mixed with statements from days 36 to 30.
Does anyone have an idea?

Thanks

stronghold888
Feb 6 '07 #1
Share this Question
Share on Google+
3 Replies


NeoPa
Expert Mod 15k+
P: 31,418
I can't look at this in any detail right now, but I've directed some other experts to come in and have a look, so hopefully someone can help.
Feb 7 '07 #2

Rabbit
Expert Mod 10K+
P: 12,357
So how is it you want it to lay out?

Like this?
Expand|Select|Wrap|Line Numbers
  1. Day36 Day35 Day34 Day33 Day32 Day31 Day30 Day29 Day28 ...
  2.  
Or did you want it like this?
Expand|Select|Wrap|Line Numbers
  1. Day36
  2. Day35
  3. Day34
  4. Day33
  5. Day32
  6. Day30
  7. Day29
  8. Day28
  9. ...
  10.  
Or did you want to lay it out in some alternate format?
Feb 7 '07 #3

ADezii
Expert 5K+
P: 8,623
Hello everyone,

I created a database in Access and on my form, I inserted a button that exports data into a ready Excel template. The export works fine, but I have one problem and it's from my code. First of all, the data from the Access database is inserted in an Excel calendar. The calendar is numbered with days 36 to -18. By exemple, if a calendar statement is for day 36, then it will be inserted in the specific column. For now, statements that belong to day 36 to 30 are inserted in the specific columns, but statements that belong to day 29 to 23 and so on are not inserted where they have to. These statements are inserted in the same columns with the previous statements from day 36 to day 30.
There's a copy of my code for the export:

Expand|Select|Wrap|Line Numbers
  1. Private Sub Command28_Click()
  2.  
  3.     Dim db As DAO.Database
  4.     Dim rst As DAO.Recordset, RstDay As DAO.Recordset
  5.     Dim appexcel As Excel.Application
  6.     Dim wbexcel As Excel.Workbook
  7.  
  8.     Dim I As Integer, j As Integer
  9.     Dim LastLigne As Boolean
  10.     Set db = CurrentDb
  11.  
  12.     Set RstDay = db.OpenRecordset("Select Distinct EventDay From TblCalendar")
  13.  
  14.     Set appexcel = CreateObject("Excel.Application")
  15.     appexcel.Visible = True
  16.     Set wbexcel = appexcel.Workbooks.Open("C:\Documents and Settings\mconea\My Documents\EMS\EMS Calendar Excel.xls")
  17.  
  18.     appexcel.Sheets("Sheet1").Select
  19.  
  20.  
  21.     Do Until RstDay.EOF
  22.         I = 4
  23.         ' Recherche de la colonne  remplir
  24.         Select Case RstDay("EventDay")
  25.             Case 36
  26.                 j = 1
  27.  
  28.             Case 35
  29.                 j = 2
  30.  
  31.             Case 34
  32.                 j = 3
  33.  
  34.             Case 33
  35.                 j = 4
  36.  
  37.             Case 32
  38.                 j = 5
  39.  
  40.             Case 31
  41.                 j = 6
  42.  
  43.             Case 30
  44.                 j = 7
  45.  
  46.             Case 29
  47.                 j = 1
  48.  
  49.             Case 28
  50.                 j = 2
  51.  
  52.             Case 27
  53.                 j = 3
  54.  
  55.             Case 26
  56.                 j = 4
  57.  
  58.             Case 25
  59.                 j = 5
  60.  
  61.             Case 24
  62.                 j = 6
  63.  
  64.             Case 23
  65.                 j = 7
  66.         End Select
  67.  
  68.         LastLigne = False
  69.  
  70.         Do Until LastLigne
  71.             If appexcel.Cells(I, j) <> "" Then ' si la cellule n'est pas vide la ligne avance
  72.                 I = I + 1
  73.             Else
  74.                 LastLigne = True
  75.             End If
  76.         Loop
  77.  
  78.         Set rst = db.OpenRecordset("Select * From TblCalendar Where EventDay='" & RstDay("EventDay") & "'")
  79.  
  80.         Do Until rst.EOF
  81.             appexcel.Cells(I, j) = rst![Calendar_E]
  82.             I = I + 1
  83.             rst.MoveNext
  84.         Loop
  85.         RstDay.MoveNext
  86.     Loop
  87.  
  88. Set xlSheet = Nothing
  89. Set xlBook = Nothing
  90. Set xlApp = Nothing
  91.  
  92. End Sub

For exemple, if a statement belong to day 36, that means Case 36 and j=1 for the line. All of statements begin to be inserted at I=4. I tried to insert another loop after Case 30 so that line for the statements that are for days 29 to 23 begin at I=21, but it didn't worked. That's why statements for days 29 to 23 are mixed with statements from days 36 to 30.
Does anyone have an idea?

Thanks

stronghold888
I am a little fuzzy on this current scenario but if you are trying to set the Row Indicator (I) to 21 for Event Dats 23 thru 29 while still incrementing the Columns (j) sequentially, then try this. If not, please explain in more detail:

Expand|Select|Wrap|Line Numbers
  1. 'code above and below the inserted block has intentionally been omitted
  2.     Case 30
  3.         j = 7
  4.       Case 23 To 29
  5.        I = 21
  6.         Select Case MyRS![ID]
  7.             Case 29
  8.               j = 1
  9.             Case 28
  10.               j = 2
  11.             Case 27
  12.               j = 3
  13.             Case 26
  14.               j = 4
  15.             Case 25
  16.               j = 5
  17.             Case 24
  18.               j = 6
  19.             Case 23
  20.               j = 7
  21.         End Select
Feb 7 '07 #4

Post your reply

Sign in to post your reply or Sign up for a free account.