Hi---I have to import some data from excel into an Access table--the
code below is working ok but I would like to condense it by running a
loop. The number of records can vary from 1 to 30 in each excel
file--have tried to run a loop through the recs and it quits so I have
just repeated the code below 30 times--any suggestions on how to run a
loop instead and have it quit when it reaches the last line of the
excel file? This code is in an Excel macro
thanks
bob stafford
Sub AppendToAccess()
Dim TheDB As Database
Dim TheRecordset As Recordset
'Sets Access Image Database
Set TheDB = OpenDatabase("C:\ImageTest.mdb")
Set TheRecordset = TheDB.TableDefs("SkinImagedFields"). _
OpenRecordset(dbOpenDynaset)
With TheRecordset
'Checks for Nulls; If null, then Exits Sub. If not, then Appends
Record
If Range("A1") = "" Then
Exit Sub
Else
.AddNew
.Fields("LabNum") = Range("A1").Value
.Fields("BiopsyNum") = Range("B1").Value
.Fields("FieldNum") = Range("C1").Value
.Fields("PositiveNuclei") = Range("D1").Value
.Fields("NegativeNuclei") = Range("E1").Value
.Fields("NonNuclearArea") = Range("F1").Value
.Update
End If
If Range("A2") = "" Then
MsgBox "You Have Imported 1 Record", vbOKOnly, "Added Record
to Access"
Exit Sub
Else
.AddNew
.Fields("LabNum") = Range("A2").Value
.Fields("BiopsyNum") = Range("B2").Value
.Fields("FieldNum") = Range("C2").Value
.Fields("PositiveNuclei") = Range("D2").Value
.Fields("NegativeNuclei") = Range("E2").Value
.Fields("NonNuclearArea") = Range("F2").Value
.Update
End If
If Range("A3") = "" Then
MsgBox "You Have Imported 2 Record", vbOKOnly, "Added Record
to Access"
Exit Sub
Else
.AddNew
.Fields("LabNum") = Range("A3").Value
.Fields("BiopsyNum") = Range("B3").Value
.Fields("FieldNum") = Range("C3").Value
.Fields("PositiveNuclei") = Range("D3").Value
.Fields("NegativeNuclei") = Range("E3").Value
.Fields("NonNuclearArea") = Range("F3").Value
.Update
End If
repeated many times 6 4433
Ally:
Try this instead. It allows the user to specify how many rows to
Append, but will exit out if it hits a blank field in column A before
it reaches that number. If you want to, you can just declare a value
for MyLastCell in your code that is always larger than the number of
rows that would be used in your spreadsheet. This would mean you can
take out the lines referenced under the section that gets the user
input and replace it with the following:
MyLastCell = CInt(30)
Otherwise, it should work as is (fixing any lines that wrapped when I
pasted in the post).
Sub AppendToAccess()
Dim TheDB As Database
Dim TheRecordset As Recordset
Dim MyCurrentCell, MyLastCell
Dim MyCount As Integer
Dim MyMsg As String
'Get number of rows from user--if no input, do nothing
MyLastCell = InputBox("What is the last row number to append?", "Enter
Number of Cells")
If MyLastCell = "" Then Exit Sub
MyLastCell = CInt(MyLastCell)
'Set cell references (Row #, Column #)
Set MyCurrentCell = ActiveSheet.Cells(1, 1) 'A1
Set MyLastCell = ActiveSheet.Cells(MyLastCell, 6) 'F30, if user
enters 30
X = 0
'Sets Access Image Database
Set TheDB = OpenDatabase("C:\ImageTest.mdb")
Set TheRecordset = TheDB.TableDefs("SkinImagedFields"). _
OpenRecordset(dbOpenDynaset)
With TheRecordset
'Checks for Nulls; If null, then Exits Loop. If not, then Appends
Record
Do Until MyCurrentCell.Row > MyLastCell.Row
If MyCurrentCell.Value = "" Then
Exit Do
Else
.AddNew
.Fields("LabNum") = MyCurrentCell.Value
Set MyCurrentCell = MyCurrentCell.Offset(0, 1) 'Move to
column B
.Fields("BiopsyNum") = MyCurrentCell.Value
Set MyCurrentCell = MyCurrentCell.Offset(0, 1) 'Move to
column C
.Fields("FieldNum") = MyCurrentCell.Value
Set MyCurrentCell = MyCurrentCell.Offset(0, 1) 'Move to
column D
.Fields("PositiveNuclei") = MyCurrentCell.Value
Set MyCurrentCell = MyCurrentCell.Offset(0, 1) 'Move to
column E
.Fields("NegativeNuclei") = MyCurrentCell.Value
Set MyCurrentCell = MyCurrentCell.Offset(0, 1) 'Move to
column F
.Fields("NonNuclearArea") = MyCurrentCell.Value
.Update
End If
X = X + 1 'counter for number of records appended to Access
Set MyCurrentCell = ActiveSheet.Cells(X + 1, 1) 'Move to first
cell in next column
Loop
End With
'Create message for MsgBox
Select Case X
Case Is <= 0
MyMsg = "No Records Were Appended."
Case Is = 1
MyMsg = "You Have Imported 1 Record."
Case Else
MyMsg = "You Have Imported " & X & " Records."
End Select
'Display MsgBox
MsgBox MyMsg, vbOKOnly, "Append Results"
End Sub
I tested it beforehand and seems to do what you're requesting. Good
luck!
Jana
Bob:
Try this instead. It allows the user to input the desired number of
rows to append into Access for you, but will exit out as soon as it
reaches an empty cell in column A, even if it hasn't reached that
number yet. If you would prefer, you can replace the section that gets
the user input with a statement that just specifies a max number of
rows to iterate through. Just replace that section with the following
line:
MyLastCell = CInt(30)
This would set the maximum number of rows to 30. You can go a little
larger since it will stop as soon as it hits a blank cell in column A
anyway.
Here is the code, you'll probably have to clean up any word wrapping
that happened when I posted, but I tested it with a database and it
worked fine.
Sub AppendToAccess()
Dim TheDB As Database
Dim TheRecordset As Recordset
Dim MyCurrentCell, MyLastCell
Dim X As Integer
Dim MyMsg As String
'Get number of rows from user--if no input, do nothing
MyLastCell = InputBox("What is the last row number to append?", "Enter
Number of Cells")
If MyLastCell = "" Then Exit Sub
MyLastCell = CInt(MyLastCell)
'Set cell references (Row #, Column #)
Set MyCurrentCell = ActiveSheet.Cells(1, 1) 'A1
Set MyLastCell = ActiveSheet.Cells(MyLastCell, 6) 'F30, if user
enters 30
X = 0
'Sets Access Image Database
Set TheDB = OpenDatabase("C:\ImageTest.mdb")
Set TheRecordset = TheDB.TableDefs("SkinImagedFields"). _
OpenRecordset(dbOpenDynaset)
With TheRecordset
'Checks for Nulls; If null, then Exits Loop. If not, then Appends
Record
Do Until MyCurrentCell.Row > MyLastCell.Row
If MyCurrentCell.Value = "" Then
Exit Do
Else
.AddNew
.Fields("LabNum") = MyCurrentCell.Value
Set MyCurrentCell = MyCurrentCell.Offset(0, 1) 'Move to
column B
.Fields("BiopsyNum") = MyCurrentCell.Value
Set MyCurrentCell = MyCurrentCell.Offset(0, 1) 'Move to
column C
.Fields("FieldNum") = MyCurrentCell.Value
Set MyCurrentCell = MyCurrentCell.Offset(0, 1) 'Move to
column D
.Fields("PositiveNuclei") = MyCurrentCell.Value
Set MyCurrentCell = MyCurrentCell.Offset(0, 1) 'Move to
column E
.Fields("NegativeNuclei") = MyCurrentCell.Value
Set MyCurrentCell = MyCurrentCell.Offset(0, 1) 'Move to
column F
.Fields("NonNuclearArea") = MyCurrentCell.Value
.Update
End If
X = X + 1 'counter for number of records appended to Access
Set MyCurrentCell = ActiveSheet.Cells(X + 1, 1) 'Move to first
cell in next column
Loop
End With
'Create message for MsgBox
Select Case X
Case Is <= 0
MyMsg = "No Records Were Appended."
Case Is = 1
MyMsg = "You Have Imported 1 Record."
Case Else
MyMsg = "You Have Imported " & X & " Records."
End Select
'Display MsgBox
MsgBox MyMsg, vbOKOnly, "Append Results"
End Sub
Good luck!
Jana
Thanks--will try it and let you know how it worked out
Bob
On 24 Feb 2005 17:37:25 -0800, "Jana" <Ba********@gmail.com> wrote: Ally:
Try this instead. It allows the user to specify how many rows to Append, but will exit out if it hits a blank field in column A before it reaches that number. If you want to, you can just declare a value for MyLastCell in your code that is always larger than the number of rows that would be used in your spreadsheet. This would mean you can take out the lines referenced under the section that gets the user input and replace it with the following: MyLastCell = CInt(30)
Otherwise, it should work as is (fixing any lines that wrapped when I pasted in the post).
Sub AppendToAccess()
Dim TheDB As Database Dim TheRecordset As Recordset Dim MyCurrentCell, MyLastCell Dim MyCount As Integer Dim MyMsg As String
'Get number of rows from user--if no input, do nothing MyLastCell = InputBox("What is the last row number to append?", "Enter Number of Cells") If MyLastCell = "" Then Exit Sub MyLastCell = CInt(MyLastCell)
'Set cell references (Row #, Column #) Set MyCurrentCell = ActiveSheet.Cells(1, 1) 'A1 Set MyLastCell = ActiveSheet.Cells(MyLastCell, 6) 'F30, if user enters 30 X = 0
'Sets Access Image Database Set TheDB = OpenDatabase("C:\ImageTest.mdb") Set TheRecordset = TheDB.TableDefs("SkinImagedFields"). _ OpenRecordset(dbOpenDynaset) With TheRecordset
'Checks for Nulls; If null, then Exits Loop. If not, then Appends Record Do Until MyCurrentCell.Row > MyLastCell.Row If MyCurrentCell.Value = "" Then Exit Do Else .AddNew .Fields("LabNum") = MyCurrentCell.Value Set MyCurrentCell = MyCurrentCell.Offset(0, 1) 'Move to column B .Fields("BiopsyNum") = MyCurrentCell.Value Set MyCurrentCell = MyCurrentCell.Offset(0, 1) 'Move to column C .Fields("FieldNum") = MyCurrentCell.Value Set MyCurrentCell = MyCurrentCell.Offset(0, 1) 'Move to column D .Fields("PositiveNuclei") = MyCurrentCell.Value Set MyCurrentCell = MyCurrentCell.Offset(0, 1) 'Move to column E .Fields("NegativeNuclei") = MyCurrentCell.Value Set MyCurrentCell = MyCurrentCell.Offset(0, 1) 'Move to column F .Fields("NonNuclearArea") = MyCurrentCell.Value .Update End If X = X + 1 'counter for number of records appended to Access Set MyCurrentCell = ActiveSheet.Cells(X + 1, 1) 'Move to first cell in next column Loop End With
'Create message for MsgBox Select Case X Case Is <= 0 MyMsg = "No Records Were Appended." Case Is = 1 MyMsg = "You Have Imported 1 Record." Case Else MyMsg = "You Have Imported " & X & " Records." End Select
'Display MsgBox MsgBox MyMsg, vbOKOnly, "Append Results" End Sub
I tested it beforehand and seems to do what you're requesting. Good luck!
Jana
Bob:
How did that work out for you?
Just curious,
Jana
On 9 Mar 2005 15:10:02 -0800, "Jana" <Ba********@gmail.com> wrote: Bob:
How did that work out for you?
Just curious, Jana
Hi--sorry to not get back sooner--it worked exactly as we needed it!!
Thanks very much! Just cannot seem to get my head around looping :(
Yay! Glad it worked out for you :) This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics
by: Mads Petersen |
last post by:
I'm stuck in this code. Hope you can and will help me. I launch it
from excel.
I have made the following code work, but not as i whant. I need the
ranges to be working with something like xlDown....
|
by: Greg Strong |
last post by:
Hello All,
I've written code in a test database with test data. Everything seems
to be working except compact database in VB code per
http://www.mvps.org/access/general/gen0041.htm. The reason I...
|
by: Antoon Vanderplancke |
last post by:
I want to link several worksheets of a workbook as linked tables.
This is the vba-code I use, which don't work
Any suggestions are welcome
sub LinkExcel(strWorkBook)
Dim appXLS as...
|
by: rdemyan via AccessMonster.com |
last post by:
I have a table with about 80 fields. I'm using an import process to populate
the table. It works fine, except for the following:
Users generally don't specify values for a lot of numerical...
|
by: eighthman11 |
last post by:
Hello everyone. I have a table linked to an excel spreadsheet. I have
an append query based on this link table. I receive an error when I
run the append query "numeric field overflow". I have...
|
by: ALaurie10 |
last post by:
I am somewhat familar with access, but have no clue to modules and coding. I am trying to develop code or a module that will enable a user to import a specific excel spreadsheet and its' data into a...
|
by: thanawala27 |
last post by:
Hi,
I wanted to append a cell of an Excel file.
Basically, the excel file is not taking more than 256 characters for a cell at a time.
So i thought of breaking the varaible into 2. n copying it...
|
by: bazdaa |
last post by:
Access 2K3 Append/Update ignore non null cells!
Hi,
I have been tasked with completing an audit of approximately 10,000 items, to which I have generated a list of 40 questions (fields) for...
|
by: premMS143 |
last post by:
How to append Data to an existing Excel file?
means,
I'm using VB as front end & once I exported the data to Excel file & saved it as 'ABC.xls'.
Now, the question/doubt is;
I addded some more...
|
by: Charles Arthur |
last post by:
How do i turn on java script on a villaon, callus and itel keypad mobile phone
|
by: ryjfgjl |
last post by:
If we have dozens or hundreds of excel to import into the database, if we use the excel import function provided by database editors such as navicat, it will be extremely tedious and time-consuming...
|
by: emmanuelkatto |
last post by:
Hi All, I am Emmanuel katto from Uganda. I want to ask what challenges you've faced while migrating a website to cloud.
Please let me know.
Thanks!
Emmanuel
|
by: BarryA |
last post by:
What are the essential steps and strategies outlined in the Data Structures and Algorithms (DSA) roadmap for aspiring data scientists? How can individuals effectively utilize this roadmap to progress...
|
by: Sonnysonu |
last post by:
This is the data of csv file
1 2 3
1 2 3
1 2 3
1 2 3
2 3
2 3
3
the lengths should be different i have to store the data by column-wise with in the specific length.
suppose the i have to...
|
by: Hystou |
last post by:
There are some requirements for setting up RAID:
1. The motherboard and BIOS support RAID configuration.
2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However,...
|
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven...
|
by: Hystou |
last post by:
Overview:
Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows...
| |