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

One column not importing

P: 82
Hi,

SQL/Access DB. Have code that imports an Excel file. Everything has been working fine until I added a new field to the table that is getting the imported data. I have tried everything that I can think of in the Excel file including re-entering the data in the new column (field) but that Excel column is not getting imported. Anyone have any thoughts? Thanks in advance.
Nov 13 '13 #1

✓ answered by Seth Schrock

I'm not very good with Excel VBA (which is what most of this is). The only idea that I have at this point is that rng1 isn't picking up the data in the new column when you set it in line 28. The range that you are selecting is A2:BR2. If you uncomment your line 30, then the range would expand to A2:BW2.

Share this Question
Share on Google+
8 Replies


Seth Schrock
Expert 2.5K+
P: 2,931
Are you doing this through code, manually going through the wizard, or using a saved import process?

Also, you say you have a SQL/Access DB. Does that mean that the tables are stored in a MS SQL Server or a MySQL backend (or Oracle or PostGre SQL)?
Nov 13 '13 #2

P: 82
@Seth Schrock
I am using code. MS SQL Server backend and Access front end. Code follows.
Expand|Select|Wrap|Line Numbers
  1. Public Sub ImportRDESpreadSheet(iYear As Integer, iMonth As Integer, sFileName As String)
  2.  
  3. Dim objXL As Excel.Application
  4. Dim xlWB As Workbook
  5. Dim xlWS As Worksheet
  6. Dim rng1 As Range, rng2 As Range, thisRng As Range
  7.  
  8. Dim rs As ADODB.Recordset
  9. Dim ssql As String, row As Integer, i As Integer
  10.  
  11. 'open RDEReports
  12. ssql = "Select * from RDEReports Where ID = 0;"
  13. Set rs = New ADODB.Recordset
  14. rs.Open ssql, CurrentProject.Connection, adOpenKeyset, adLockOptimistic
  15.  
  16.  
  17. 'open spread sheet
  18. Set objXL = New Excel.Application
  19. objXL.Visible = True
  20.  
  21. 'Set xlWB = objXL.Workbooks.Open(AppPath & "RDE_UPD_Full_Recognition_ME_120811.xls")
  22. Set xlWB = objXL.Workbooks.Open(sFileName)
  23.  
  24. Set xlWS = xlWB.Sheets(1)
  25.  
  26. row = 2
  27.  
  28. Set rng1 = xlWS.Range(xlWS.Cells(row, 1), xlWS.Cells(row, 70))
  29.  
  30. 'Set rng1 = xlWS.Range(xlWS.Cells(row, 1), xlWS.Cells(row, 75))
  31.  
  32. Do While rng1.Item(1, 1) > ""
  33.  
  34.     Debug.Print rng1.Item(1, 1)
  35.  
  36.     rs.AddNew
  37.  
  38.  For i = 1 To 70
  39.  
  40.  
  41.   '  For i = 1 To 75
  42.  
  43.         rs.Fields(i).Value = rng1.Item(1, i)
  44.  
  45.  
  46.     Next
  47.  
  48.     rs!Imported = Date
  49.     rs!ReportYear = iYear
  50.     rs!ReportMonth = iMonth
  51.  
  52.     rs.Update
  53.  
  54.     row = row + 1
  55.  
  56.   Set rng1 = xlWS.Range(xlWS.Cells(row, 1), xlWS.Cells(row, 70))
  57. 'Set rng1 = xlWS.Range(xlWS.Cells(row, 1), xlWS.Cells(row, 75))
  58.  
  59. Loop
  60.  
  61. rs.Close
  62.  
  63.  
  64. xlWB.Close
  65. objXL.Quit
  66.  
  67. Set objXL = Nothing
  68.  
  69.     Dim cmd As ADODB.Command
  70.     Dim cnn As New ADODB.Connection
  71.  
  72. 'Update NCQA end dates to reflect 3 year recognition
  73.  
  74.    ssql = "Update RDEReports Set RDEReports.[End Recognition Date] = DateAdd(""yyyy"",3,[rdereports].[Begin Recognition Date]) " _
  75.           & " Where (([RDEReports].[Program Name] In ('N1001','N1002','N1003','N1004','N1010')) " _
  76.           & " AND ((DateDiff(""yyyy"",[RDEReports].[Begin Recognition Date],[RDEReports].[End Recognition Date]))=2)) "
  77.  
  78.     Set cnn = CurrentProject.Connection
  79.            Set cmd = New ADODB.Command
  80.          cnn.BeginTrans
  81.            With cmd
  82.              .CommandType = adCmdText
  83.              .ActiveConnection = cnn
  84.              .CommandText = ssql
  85.              .Execute
  86.            End With
  87.          cnn.CommitTrans
  88.  
  89. MsgBox "Finished Importing RDE for " & iMonth & "/" & iYear
  90.  
  91.  
  92.  
  93. End Sub
Nov 13 '13 #3

Seth Schrock
Expert 2.5K+
P: 2,931
I'm not very good with Excel VBA (which is what most of this is). The only idea that I have at this point is that rng1 isn't picking up the data in the new column when you set it in line 28. The range that you are selecting is A2:BR2. If you uncomment your line 30, then the range would expand to A2:BW2.
Nov 13 '13 #4

ADezii
Expert 5K+
P: 8,607
As indicated by Seth, if you are trying to Import the Data in an Excel Worksheet from 70 contiguous Columns in Row 2 starting at A2, then the Logic should be:
Expand|Select|Wrap|Line Numbers
  1. '**** Code has intentionally been omitted ****
  2. Dim rng1 As Excel.Range
  3. Dim rng2 As Excel.Range
  4. Dim intCol As Integer
  5.  
  6. intCol = 0      'Initialize
  7.  
  8. Set rng2 = ActiveSheet.Range("A2:BR2")
  9.  
  10. For Each rng1 In rng2
  11.   intCol = intCol + 1
  12.     rs.Fields(intCol).Value = rng1.Value
  13. Next
  14. '**** Code has intentionally been omitted ****
Nov 13 '13 #5

P: 82
I started to do that but then commented it out as i kept getting an error message. I'll try again after a quick meeting I need to attend. Thanks to you both.
Nov 13 '13 #6

P: 82
Tried your ideas and that one column still isn't importing. The field's data type matches the format of the column in excel. Any other ideas would be appreciated. Thanks much.
Nov 13 '13 #7

P: 82
My mistake. Seth's suggestion did work. Thanks.
Nov 13 '13 #8

Seth Schrock
Expert 2.5K+
P: 2,931
Glad to be able to help.
Nov 13 '13 #9

Post your reply

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