Data from Excel to Access Table


G

Guest

Hello,
I have this code setup in Excel but I want to be able to upload multiple
rows at one time. Is there a way to incorporate this into my code? Also, is
there a way to upload cells that are blank as well? Right now it won't allow
me to upload cells that are blank. Can this be done?

Sub UploadData()

Dim MyCn As ADODB.Connection
Dim SQLStr As String

Set MyCn = New ADODB.Connection

MyCn.Open "DRIVER={Microsoft Access Driver (*.mdb)}; " & _
"DBQ=\\Bcar1\bcar-net\Quality-ISO\Quality-Control\BC-Quality-Control\BC
QA mdb\BC Quality Action
Database.mdb;SystemDB=\\Bcar1\bcar-net\Quality-ISO\Quality-Control\BC-Quality-Control\BC QA mdb\Sys\sys.mdw;" & _
"Uid=Admin;" & _
"Pwd=password;"
' Replace actual Access file path here
' Note: If database has userID and password, need to specify them here also
' by appending "; UID=MyUserID; PWD=MyPassword"
' - if trouble accessing the file do a net search for help on Connection
Strings

SQLStr = "INSERT INTO [Table1] " _
& "Values ('" & Range("A10").Value & "', '" & Range("B10").Value & "',
'" _
& Range("C10").Value & "', '" & Range("D10").Value & "', '" &
Range("E10").Value & "', '" _
& Range("F10").Value & "', '" & Range("G10").Value & "', '" &
Range("H10").Value & "', '" _
& Range("I10").Value & "', '" & Range("J10").Value & "', '" &
Range("K10").Value & "', '" _
& Range("L10").Value & "', '" & Range("M10").Value & "', '" &
Range("N10").Value & "', '" _
& Range("O10").Value & "', '" & Range("P10").Value & "')"
' NOTE: The above assumes all fields are TEXT data type, that is why the "'"s;
' might have trouble with other data types unless you match the format
expected
' by the database

MyCn.Execute SQLStr

MyCn.Close
Set MyCn = Nothing

End Sub
 
Ad

Advertisements

G

Guest

Because this data is used by the users in excel and then it needs to be
uploaded into Access to be used with additional data already in access to run
reports, etc.

exceluserforeman said:
Why ACCESS? Why do you not just use EXCEL?

Secret Squirrel said:
Hello,
I have this code setup in Excel but I want to be able to upload multiple
rows at one time. Is there a way to incorporate this into my code? Also, is
there a way to upload cells that are blank as well? Right now it won't allow
me to upload cells that are blank. Can this be done?

Sub UploadData()

Dim MyCn As ADODB.Connection
Dim SQLStr As String

Set MyCn = New ADODB.Connection

MyCn.Open "DRIVER={Microsoft Access Driver (*.mdb)}; " & _
"DBQ=\\Bcar1\bcar-net\Quality-ISO\Quality-Control\BC-Quality-Control\BC
QA mdb\BC Quality Action
Database.mdb;SystemDB=\\Bcar1\bcar-net\Quality-ISO\Quality-Control\BC-Quality-Control\BC QA mdb\Sys\sys.mdw;" & _
"Uid=Admin;" & _
"Pwd=password;"
' Replace actual Access file path here
' Note: If database has userID and password, need to specify them here also
' by appending "; UID=MyUserID; PWD=MyPassword"
' - if trouble accessing the file do a net search for help on Connection
Strings

SQLStr = "INSERT INTO [Table1] " _
& "Values ('" & Range("A10").Value & "', '" & Range("B10").Value & "',
'" _
& Range("C10").Value & "', '" & Range("D10").Value & "', '" &
Range("E10").Value & "', '" _
& Range("F10").Value & "', '" & Range("G10").Value & "', '" &
Range("H10").Value & "', '" _
& Range("I10").Value & "', '" & Range("J10").Value & "', '" &
Range("K10").Value & "', '" _
& Range("L10").Value & "', '" & Range("M10").Value & "', '" &
Range("N10").Value & "', '" _
& Range("O10").Value & "', '" & Range("P10").Value & "')"
' NOTE: The above assumes all fields are TEXT data type, that is why the "'"s;
' might have trouble with other data types unless you match the format
expected
' by the database

MyCn.Execute SQLStr

MyCn.Close
Set MyCn = Nothing

End Sub
 
G

Guest

Then maybe the stuff in ACCESS can be sent to Excel and the reports can be
generated from there??

HOWEVER For each cell that is blank, put an apostrophe in it. ' Then maybe
it will accept the "blank" cell or maybe a Quote mark "


Secret Squirrel said:
Because this data is used by the users in excel and then it needs to be
uploaded into Access to be used with additional data already in access to run
reports, etc.

exceluserforeman said:
Why ACCESS? Why do you not just use EXCEL?

Secret Squirrel said:
Hello,
I have this code setup in Excel but I want to be able to upload multiple
rows at one time. Is there a way to incorporate this into my code? Also, is
there a way to upload cells that are blank as well? Right now it won't allow
me to upload cells that are blank. Can this be done?

Sub UploadData()

Dim MyCn As ADODB.Connection
Dim SQLStr As String

Set MyCn = New ADODB.Connection

MyCn.Open "DRIVER={Microsoft Access Driver (*.mdb)}; " & _
"DBQ=\\Bcar1\bcar-net\Quality-ISO\Quality-Control\BC-Quality-Control\BC
QA mdb\BC Quality Action
Database.mdb;SystemDB=\\Bcar1\bcar-net\Quality-ISO\Quality-Control\BC-Quality-Control\BC QA mdb\Sys\sys.mdw;" & _
"Uid=Admin;" & _
"Pwd=password;"
' Replace actual Access file path here
' Note: If database has userID and password, need to specify them here also
' by appending "; UID=MyUserID; PWD=MyPassword"
' - if trouble accessing the file do a net search for help on Connection
Strings

SQLStr = "INSERT INTO [Table1] " _
& "Values ('" & Range("A10").Value & "', '" & Range("B10").Value & "',
'" _
& Range("C10").Value & "', '" & Range("D10").Value & "', '" &
Range("E10").Value & "', '" _
& Range("F10").Value & "', '" & Range("G10").Value & "', '" &
Range("H10").Value & "', '" _
& Range("I10").Value & "', '" & Range("J10").Value & "', '" &
Range("K10").Value & "', '" _
& Range("L10").Value & "', '" & Range("M10").Value & "', '" &
Range("N10").Value & "', '" _
& Range("O10").Value & "', '" & Range("P10").Value & "')"
' NOTE: The above assumes all fields are TEXT data type, that is why the "'"s;
' might have trouble with other data types unless you match the format
expected
' by the database

MyCn.Execute SQLStr

MyCn.Close
Set MyCn = Nothing

End Sub
 
G

Guest

There are many reports and queries already set up in Access to analyze the
data. And plus the users like using Access rather than Excel.

How can I fix my code to be able to import more than just one row of data?
Right now I have it set up so it will import data from cell A10:O10. What
about multiple rows? How would I do this?

exceluserforeman said:
Then maybe the stuff in ACCESS can be sent to Excel and the reports can be
generated from there??

HOWEVER For each cell that is blank, put an apostrophe in it. ' Then maybe
it will accept the "blank" cell or maybe a Quote mark "


Secret Squirrel said:
Because this data is used by the users in excel and then it needs to be
uploaded into Access to be used with additional data already in access to run
reports, etc.

exceluserforeman said:
Why ACCESS? Why do you not just use EXCEL?

:

Hello,
I have this code setup in Excel but I want to be able to upload multiple
rows at one time. Is there a way to incorporate this into my code? Also, is
there a way to upload cells that are blank as well? Right now it won't allow
me to upload cells that are blank. Can this be done?

Sub UploadData()

Dim MyCn As ADODB.Connection
Dim SQLStr As String

Set MyCn = New ADODB.Connection

MyCn.Open "DRIVER={Microsoft Access Driver (*.mdb)}; " & _
"DBQ=\\Bcar1\bcar-net\Quality-ISO\Quality-Control\BC-Quality-Control\BC
QA mdb\BC Quality Action
Database.mdb;SystemDB=\\Bcar1\bcar-net\Quality-ISO\Quality-Control\BC-Quality-Control\BC QA mdb\Sys\sys.mdw;" & _
"Uid=Admin;" & _
"Pwd=password;"
' Replace actual Access file path here
' Note: If database has userID and password, need to specify them here also
' by appending "; UID=MyUserID; PWD=MyPassword"
' - if trouble accessing the file do a net search for help on Connection
Strings

SQLStr = "INSERT INTO [Table1] " _
& "Values ('" & Range("A10").Value & "', '" & Range("B10").Value & "',
'" _
& Range("C10").Value & "', '" & Range("D10").Value & "', '" &
Range("E10").Value & "', '" _
& Range("F10").Value & "', '" & Range("G10").Value & "', '" &
Range("H10").Value & "', '" _
& Range("I10").Value & "', '" & Range("J10").Value & "', '" &
Range("K10").Value & "', '" _
& Range("L10").Value & "', '" & Range("M10").Value & "', '" &
Range("N10").Value & "', '" _
& Range("O10").Value & "', '" & Range("P10").Value & "')"
' NOTE: The above assumes all fields are TEXT data type, that is why the "'"s;
' might have trouble with other data types unless you match the format
expected
' by the database

MyCn.Execute SQLStr

MyCn.Close
Set MyCn = Nothing

End Sub
 
G

Guest

I ma out of my depth here but ...
where it says
& "Values ('" & Range("A10").Value & "', '" & Range("B10").Value & "',
etc...



maybe just
& "Values ('" & Range("A1:O10").Value,

which covers all the rows instead of layering each cell intot he table one
by one.

Otherwise a for loop for each cell range written to a string

Maybe ask in ACCESS section
- -Mark

Secret Squirrel said:
There are many reports and queries already set up in Access to analyze the
data. And plus the users like using Access rather than Excel.

How can I fix my code to be able to import more than just one row of data?
Right now I have it set up so it will import data from cell A10:O10. What
about multiple rows? How would I do this?

exceluserforeman said:
Then maybe the stuff in ACCESS can be sent to Excel and the reports can be
generated from there??

HOWEVER For each cell that is blank, put an apostrophe in it. ' Then maybe
it will accept the "blank" cell or maybe a Quote mark "


Secret Squirrel said:
Because this data is used by the users in excel and then it needs to be
uploaded into Access to be used with additional data already in access to run
reports, etc.

:

Why ACCESS? Why do you not just use EXCEL?

:

Hello,
I have this code setup in Excel but I want to be able to upload multiple
rows at one time. Is there a way to incorporate this into my code? Also, is
there a way to upload cells that are blank as well? Right now it won't allow
me to upload cells that are blank. Can this be done?

Sub UploadData()

Dim MyCn As ADODB.Connection
Dim SQLStr As String

Set MyCn = New ADODB.Connection

MyCn.Open "DRIVER={Microsoft Access Driver (*.mdb)}; " & _
"DBQ=\\Bcar1\bcar-net\Quality-ISO\Quality-Control\BC-Quality-Control\BC
QA mdb\BC Quality Action
Database.mdb;SystemDB=\\Bcar1\bcar-net\Quality-ISO\Quality-Control\BC-Quality-Control\BC QA mdb\Sys\sys.mdw;" & _
"Uid=Admin;" & _
"Pwd=password;"
' Replace actual Access file path here
' Note: If database has userID and password, need to specify them here also
' by appending "; UID=MyUserID; PWD=MyPassword"
' - if trouble accessing the file do a net search for help on Connection
Strings

SQLStr = "INSERT INTO [Table1] " _
& "Values ('" & Range("A10").Value & "', '" & Range("B10").Value & "',
'" _
& Range("C10").Value & "', '" & Range("D10").Value & "', '" &
Range("E10").Value & "', '" _
& Range("F10").Value & "', '" & Range("G10").Value & "', '" &
Range("H10").Value & "', '" _
& Range("I10").Value & "', '" & Range("J10").Value & "', '" &
Range("K10").Value & "', '" _
& Range("L10").Value & "', '" & Range("M10").Value & "', '" &
Range("N10").Value & "', '" _
& Range("O10").Value & "', '" & Range("P10").Value & "')"
' NOTE: The above assumes all fields are TEXT data type, that is why the "'"s;
' might have trouble with other data types unless you match the format
expected
' by the database

MyCn.Execute SQLStr

MyCn.Close
Set MyCn = Nothing

End Sub
 
Ad

Advertisements

G

Guest

I tried the (A10:010") theory and it didn't work.

Do you know how to write it using the "loop"?

exceluserforeman said:
I ma out of my depth here but ...
where it says
& "Values ('" & Range("A10").Value & "', '" & Range("B10").Value & "',
etc...



maybe just
& "Values ('" & Range("A1:O10").Value,

which covers all the rows instead of layering each cell intot he table one
by one.

Otherwise a for loop for each cell range written to a string

Maybe ask in ACCESS section
- -Mark

Secret Squirrel said:
There are many reports and queries already set up in Access to analyze the
data. And plus the users like using Access rather than Excel.

How can I fix my code to be able to import more than just one row of data?
Right now I have it set up so it will import data from cell A10:O10. What
about multiple rows? How would I do this?

exceluserforeman said:
Then maybe the stuff in ACCESS can be sent to Excel and the reports can be
generated from there??

HOWEVER For each cell that is blank, put an apostrophe in it. ' Then maybe
it will accept the "blank" cell or maybe a Quote mark "


:

Because this data is used by the users in excel and then it needs to be
uploaded into Access to be used with additional data already in access to run
reports, etc.

:

Why ACCESS? Why do you not just use EXCEL?

:

Hello,
I have this code setup in Excel but I want to be able to upload multiple
rows at one time. Is there a way to incorporate this into my code? Also, is
there a way to upload cells that are blank as well? Right now it won't allow
me to upload cells that are blank. Can this be done?

Sub UploadData()

Dim MyCn As ADODB.Connection
Dim SQLStr As String

Set MyCn = New ADODB.Connection

MyCn.Open "DRIVER={Microsoft Access Driver (*.mdb)}; " & _
"DBQ=\\Bcar1\bcar-net\Quality-ISO\Quality-Control\BC-Quality-Control\BC
QA mdb\BC Quality Action
Database.mdb;SystemDB=\\Bcar1\bcar-net\Quality-ISO\Quality-Control\BC-Quality-Control\BC QA mdb\Sys\sys.mdw;" & _
"Uid=Admin;" & _
"Pwd=password;"
' Replace actual Access file path here
' Note: If database has userID and password, need to specify them here also
' by appending "; UID=MyUserID; PWD=MyPassword"
' - if trouble accessing the file do a net search for help on Connection
Strings

SQLStr = "INSERT INTO [Table1] " _
& "Values ('" & Range("A10").Value & "', '" & Range("B10").Value & "',
'" _
& Range("C10").Value & "', '" & Range("D10").Value & "', '" &
Range("E10").Value & "', '" _
& Range("F10").Value & "', '" & Range("G10").Value & "', '" &
Range("H10").Value & "', '" _
& Range("I10").Value & "', '" & Range("J10").Value & "', '" &
Range("K10").Value & "', '" _
& Range("L10").Value & "', '" & Range("M10").Value & "', '" &
Range("N10").Value & "', '" _
& Range("O10").Value & "', '" & Range("P10").Value & "')"
' NOTE: The above assumes all fields are TEXT data type, that is why the "'"s;
' might have trouble with other data types unless you match the format
expected
' by the database

MyCn.Execute SQLStr

MyCn.Close
Set MyCn = Nothing

End Sub
 
G

Guest

hello,


Sub Grunt()
dim Cell
dim varX
range("A10:p10").select
for each cell in selection
if cell.text="" then
cell.text=" ,"
else
varx=varx & cell.text & ","
end if
next
'Your access code here...

end sub

I've used a space instead of an apostrophe.

You may have to "wiggle" the code it a bit to get it right.
SQLStr = "INSERT INTO [Table1] " _
& "Values (" & varx & ")"

Whether the word "Values" is required, I do not know.

Maybe you could record a macro where the range is already preselected, to
see if it picks up the range.



Secret Squirrel said:
I tried the (A10:010") theory and it didn't work.

Do you know how to write it using the "loop"?

exceluserforeman said:
I ma out of my depth here but ...
where it says
& "Values ('" & Range("A10").Value & "', '" & Range("B10").Value & "',
etc...



maybe just
& "Values ('" & Range("A1:O10").Value,

which covers all the rows instead of layering each cell intot he table one
by one.

Otherwise a for loop for each cell range written to a string

Maybe ask in ACCESS section
- -Mark

Secret Squirrel said:
There are many reports and queries already set up in Access to analyze the
data. And plus the users like using Access rather than Excel.

How can I fix my code to be able to import more than just one row of data?
Right now I have it set up so it will import data from cell A10:O10. What
about multiple rows? How would I do this?

:

Then maybe the stuff in ACCESS can be sent to Excel and the reports can be
generated from there??

HOWEVER For each cell that is blank, put an apostrophe in it. ' Then maybe
it will accept the "blank" cell or maybe a Quote mark "


:

Because this data is used by the users in excel and then it needs to be
uploaded into Access to be used with additional data already in access to run
reports, etc.

:

Why ACCESS? Why do you not just use EXCEL?

:

Hello,
I have this code setup in Excel but I want to be able to upload multiple
rows at one time. Is there a way to incorporate this into my code? Also, is
there a way to upload cells that are blank as well? Right now it won't allow
me to upload cells that are blank. Can this be done?

Sub UploadData()

Dim MyCn As ADODB.Connection
Dim SQLStr As String

Set MyCn = New ADODB.Connection

MyCn.Open "DRIVER={Microsoft Access Driver (*.mdb)}; " & _
"DBQ=\\Bcar1\bcar-net\Quality-ISO\Quality-Control\BC-Quality-Control\BC
QA mdb\BC Quality Action
Database.mdb;SystemDB=\\Bcar1\bcar-net\Quality-ISO\Quality-Control\BC-Quality-Control\BC QA mdb\Sys\sys.mdw;" & _
"Uid=Admin;" & _
"Pwd=password;"
' Replace actual Access file path here
' Note: If database has userID and password, need to specify them here also
' by appending "; UID=MyUserID; PWD=MyPassword"
' - if trouble accessing the file do a net search for help on Connection
Strings

SQLStr = "INSERT INTO [Table1] " _
& "Values ('" & Range("A10").Value & "', '" & Range("B10").Value & "',
'" _
& Range("C10").Value & "', '" & Range("D10").Value & "', '" &
Range("E10").Value & "', '" _
& Range("F10").Value & "', '" & Range("G10").Value & "', '" &
Range("H10").Value & "', '" _
& Range("I10").Value & "', '" & Range("J10").Value & "', '" &
Range("K10").Value & "', '" _
& Range("L10").Value & "', '" & Range("M10").Value & "', '" &
Range("N10").Value & "', '" _
& Range("O10").Value & "', '" & Range("P10").Value & "')"
' NOTE: The above assumes all fields are TEXT data type, that is why the "'"s;
' might have trouble with other data types unless you match the format
expected
' by the database

MyCn.Execute SQLStr

MyCn.Close
Set MyCn = Nothing

End Sub
 
G

Guest

Hello,

So what code am I eliminating from my code? I'm a little confused as to
which code I need to remove. Also, not sure what you mean about wiggling my
code. What do I need to do to wiggle it?

Thanks!

exceluserforeman said:
hello,


Sub Grunt()
dim Cell
dim varX
range("A10:p10").select
for each cell in selection
if cell.text="" then
cell.text=" ,"
else
varx=varx & cell.text & ","
end if
next
'Your access code here...

end sub

I've used a space instead of an apostrophe.

You may have to "wiggle" the code it a bit to get it right.
SQLStr = "INSERT INTO [Table1] " _
& "Values (" & varx & ")"

Whether the word "Values" is required, I do not know.

Maybe you could record a macro where the range is already preselected, to
see if it picks up the range.



Secret Squirrel said:
I tried the (A10:010") theory and it didn't work.

Do you know how to write it using the "loop"?

exceluserforeman said:
I ma out of my depth here but ...
where it says
& "Values ('" & Range("A10").Value & "', '" & Range("B10").Value & "',
etc...



maybe just
& "Values ('" & Range("A1:O10").Value,

which covers all the rows instead of layering each cell intot he table one
by one.

Otherwise a for loop for each cell range written to a string

Maybe ask in ACCESS section
- -Mark

:

There are many reports and queries already set up in Access to analyze the
data. And plus the users like using Access rather than Excel.

How can I fix my code to be able to import more than just one row of data?
Right now I have it set up so it will import data from cell A10:O10. What
about multiple rows? How would I do this?

:

Then maybe the stuff in ACCESS can be sent to Excel and the reports can be
generated from there??

HOWEVER For each cell that is blank, put an apostrophe in it. ' Then maybe
it will accept the "blank" cell or maybe a Quote mark "


:

Because this data is used by the users in excel and then it needs to be
uploaded into Access to be used with additional data already in access to run
reports, etc.

:

Why ACCESS? Why do you not just use EXCEL?

:

Hello,
I have this code setup in Excel but I want to be able to upload multiple
rows at one time. Is there a way to incorporate this into my code? Also, is
there a way to upload cells that are blank as well? Right now it won't allow
me to upload cells that are blank. Can this be done?

Sub UploadData()

Dim MyCn As ADODB.Connection
Dim SQLStr As String

Set MyCn = New ADODB.Connection

MyCn.Open "DRIVER={Microsoft Access Driver (*.mdb)}; " & _
"DBQ=\\Bcar1\bcar-net\Quality-ISO\Quality-Control\BC-Quality-Control\BC
QA mdb\BC Quality Action
Database.mdb;SystemDB=\\Bcar1\bcar-net\Quality-ISO\Quality-Control\BC-Quality-Control\BC QA mdb\Sys\sys.mdw;" & _
"Uid=Admin;" & _
"Pwd=password;"
' Replace actual Access file path here
' Note: If database has userID and password, need to specify them here also
' by appending "; UID=MyUserID; PWD=MyPassword"
' - if trouble accessing the file do a net search for help on Connection
Strings

SQLStr = "INSERT INTO [Table1] " _
& "Values ('" & Range("A10").Value & "', '" & Range("B10").Value & "',
'" _
& Range("C10").Value & "', '" & Range("D10").Value & "', '" &
Range("E10").Value & "', '" _
& Range("F10").Value & "', '" & Range("G10").Value & "', '" &
Range("H10").Value & "', '" _
& Range("I10").Value & "', '" & Range("J10").Value & "', '" &
Range("K10").Value & "', '" _
& Range("L10").Value & "', '" & Range("M10").Value & "', '" &
Range("N10").Value & "', '" _
& Range("O10").Value & "', '" & Range("P10").Value & "')"
' NOTE: The above assumes all fields are TEXT data type, that is why the "'"s;
' might have trouble with other data types unless you match the format
expected
' by the database

MyCn.Execute SQLStr

MyCn.Close
Set MyCn = Nothing

End Sub
 
G

Guest

before your code starts but after the Sub xxx()
dim Cell
dim varX
range("A10:p10").select
for each cell in selection
if cell.text="" then
cell.text=" ,"
else
varx=varx & cell.text & ","
end if
next


where your code says

SQLStr = "INSERT INTO [Table1] " _

instead of al that range data put


& "Values (" & varx & ")"


I meant wiggle by assuming you wrote the code in the first place and knew
that nothing works the first time round and you have to "wiggle" the code (ie
make minor changes) a bit.

And I am still not sure whetther it will work.



Secret Squirrel said:
Hello,

So what code am I eliminating from my code? I'm a little confused as to
which code I need to remove. Also, not sure what you mean about wiggling my
code. What do I need to do to wiggle it?

Thanks!

exceluserforeman said:
hello,


Sub Grunt()
dim Cell
dim varX
range("A10:p10").select
for each cell in selection
if cell.text="" then
cell.text=" ,"
else
varx=varx & cell.text & ","
end if
next
'Your access code here...

end sub

I've used a space instead of an apostrophe.

You may have to "wiggle" the code it a bit to get it right.
SQLStr = "INSERT INTO [Table1] " _
& "Values (" & varx & ")"

Whether the word "Values" is required, I do not know.

Maybe you could record a macro where the range is already preselected, to
see if it picks up the range.



Secret Squirrel said:
I tried the (A10:010") theory and it didn't work.

Do you know how to write it using the "loop"?

:

I ma out of my depth here but ...
where it says
& "Values ('" & Range("A10").Value & "', '" & Range("B10").Value & "',
etc...



maybe just
& "Values ('" & Range("A1:O10").Value,

which covers all the rows instead of layering each cell intot he table one
by one.

Otherwise a for loop for each cell range written to a string

Maybe ask in ACCESS section
- -Mark

:

There are many reports and queries already set up in Access to analyze the
data. And plus the users like using Access rather than Excel.

How can I fix my code to be able to import more than just one row of data?
Right now I have it set up so it will import data from cell A10:O10. What
about multiple rows? How would I do this?

:

Then maybe the stuff in ACCESS can be sent to Excel and the reports can be
generated from there??

HOWEVER For each cell that is blank, put an apostrophe in it. ' Then maybe
it will accept the "blank" cell or maybe a Quote mark "


:

Because this data is used by the users in excel and then it needs to be
uploaded into Access to be used with additional data already in access to run
reports, etc.

:

Why ACCESS? Why do you not just use EXCEL?

:

Hello,
I have this code setup in Excel but I want to be able to upload multiple
rows at one time. Is there a way to incorporate this into my code? Also, is
there a way to upload cells that are blank as well? Right now it won't allow
me to upload cells that are blank. Can this be done?

Sub UploadData()

Dim MyCn As ADODB.Connection
Dim SQLStr As String

Set MyCn = New ADODB.Connection

MyCn.Open "DRIVER={Microsoft Access Driver (*.mdb)}; " & _
"DBQ=\\Bcar1\bcar-net\Quality-ISO\Quality-Control\BC-Quality-Control\BC
QA mdb\BC Quality Action
Database.mdb;SystemDB=\\Bcar1\bcar-net\Quality-ISO\Quality-Control\BC-Quality-Control\BC QA mdb\Sys\sys.mdw;" & _
"Uid=Admin;" & _
"Pwd=password;"
' Replace actual Access file path here
' Note: If database has userID and password, need to specify them here also
' by appending "; UID=MyUserID; PWD=MyPassword"
' - if trouble accessing the file do a net search for help on Connection
Strings

SQLStr = "INSERT INTO [Table1] " _
& "Values ('" & Range("A10").Value & "', '" & Range("B10").Value & "',
'" _
& Range("C10").Value & "', '" & Range("D10").Value & "', '" &
Range("E10").Value & "', '" _
& Range("F10").Value & "', '" & Range("G10").Value & "', '" &
Range("H10").Value & "', '" _
& Range("I10").Value & "', '" & Range("J10").Value & "', '" &
Range("K10").Value & "', '" _
& Range("L10").Value & "', '" & Range("M10").Value & "', '" &
Range("N10").Value & "', '" _
& Range("O10").Value & "', '" & Range("P10").Value & "')"
' NOTE: The above assumes all fields are TEXT data type, that is why the "'"s;
' might have trouble with other data types unless you match the format
expected
' by the database

MyCn.Execute SQLStr

MyCn.Close
Set MyCn = Nothing

End Sub
 
G

Guest

I will try that and see what happens.

The code does work but it was only set up to upload just the one row from
A10:O10. So since it worked should I still leave my code for the SQLStr or
use yours?

exceluserforeman said:
before your code starts but after the Sub xxx()
dim Cell
dim varX
range("A10:p10").select
for each cell in selection
if cell.text="" then
cell.text=" ,"
else
varx=varx & cell.text & ","
end if
next


where your code says

SQLStr = "INSERT INTO [Table1] " _

instead of al that range data put


& "Values (" & varx & ")"


I meant wiggle by assuming you wrote the code in the first place and knew
that nothing works the first time round and you have to "wiggle" the code (ie
make minor changes) a bit.

And I am still not sure whetther it will work.



Secret Squirrel said:
Hello,

So what code am I eliminating from my code? I'm a little confused as to
which code I need to remove. Also, not sure what you mean about wiggling my
code. What do I need to do to wiggle it?

Thanks!

exceluserforeman said:
hello,


Sub Grunt()
dim Cell
dim varX
range("A10:p10").select
for each cell in selection
if cell.text="" then
cell.text=" ,"
else
varx=varx & cell.text & ","
end if
next
'Your access code here...

end sub

I've used a space instead of an apostrophe.

You may have to "wiggle" the code it a bit to get it right.
SQLStr = "INSERT INTO [Table1] " _
& "Values (" & varx & ")"

Whether the word "Values" is required, I do not know.

Maybe you could record a macro where the range is already preselected, to
see if it picks up the range.



:

I tried the (A10:010") theory and it didn't work.

Do you know how to write it using the "loop"?

:

I ma out of my depth here but ...
where it says
& "Values ('" & Range("A10").Value & "', '" & Range("B10").Value & "',
etc...



maybe just
& "Values ('" & Range("A1:O10").Value,

which covers all the rows instead of layering each cell intot he table one
by one.

Otherwise a for loop for each cell range written to a string

Maybe ask in ACCESS section
- -Mark

:

There are many reports and queries already set up in Access to analyze the
data. And plus the users like using Access rather than Excel.

How can I fix my code to be able to import more than just one row of data?
Right now I have it set up so it will import data from cell A10:O10. What
about multiple rows? How would I do this?

:

Then maybe the stuff in ACCESS can be sent to Excel and the reports can be
generated from there??

HOWEVER For each cell that is blank, put an apostrophe in it. ' Then maybe
it will accept the "blank" cell or maybe a Quote mark "


:

Because this data is used by the users in excel and then it needs to be
uploaded into Access to be used with additional data already in access to run
reports, etc.

:

Why ACCESS? Why do you not just use EXCEL?

:

Hello,
I have this code setup in Excel but I want to be able to upload multiple
rows at one time. Is there a way to incorporate this into my code? Also, is
there a way to upload cells that are blank as well? Right now it won't allow
me to upload cells that are blank. Can this be done?

Sub UploadData()

Dim MyCn As ADODB.Connection
Dim SQLStr As String

Set MyCn = New ADODB.Connection

MyCn.Open "DRIVER={Microsoft Access Driver (*.mdb)}; " & _
"DBQ=\\Bcar1\bcar-net\Quality-ISO\Quality-Control\BC-Quality-Control\BC
QA mdb\BC Quality Action
Database.mdb;SystemDB=\\Bcar1\bcar-net\Quality-ISO\Quality-Control\BC-Quality-Control\BC QA mdb\Sys\sys.mdw;" & _
"Uid=Admin;" & _
"Pwd=password;"
' Replace actual Access file path here
' Note: If database has userID and password, need to specify them here also
' by appending "; UID=MyUserID; PWD=MyPassword"
' - if trouble accessing the file do a net search for help on Connection
Strings

SQLStr = "INSERT INTO [Table1] " _
& "Values ('" & Range("A10").Value & "', '" & Range("B10").Value & "',
'" _
& Range("C10").Value & "', '" & Range("D10").Value & "', '" &
Range("E10").Value & "', '" _
& Range("F10").Value & "', '" & Range("G10").Value & "', '" &
Range("H10").Value & "', '" _
& Range("I10").Value & "', '" & Range("J10").Value & "', '" &
Range("K10").Value & "', '" _
& Range("L10").Value & "', '" & Range("M10").Value & "', '" &
Range("N10").Value & "', '" _
& Range("O10").Value & "', '" & Range("P10").Value & "')"
' NOTE: The above assumes all fields are TEXT data type, that is why the "'"s;
' might have trouble with other data types unless you match the format
expected
' by the database

MyCn.Execute SQLStr

MyCn.Close
Set MyCn = Nothing

End Sub
 
Ad

Advertisements

G

Guest

I think you should leave it the old way.

By creating for .. loops etc will make large volume data quicker to move:

but it would be interesting if my code worked at all ...


- -Mark

Secret Squirrel said:
I will try that and see what happens.

The code does work but it was only set up to upload just the one row from
A10:O10. So since it worked should I still leave my code for the SQLStr or
use yours?

exceluserforeman said:
before your code starts but after the Sub xxx()
dim Cell
dim varX
range("A10:p10").select
for each cell in selection
if cell.text="" then
cell.text=" ,"
else
varx=varx & cell.text & ","
end if
next


where your code says

SQLStr = "INSERT INTO [Table1] " _

instead of al that range data put


& "Values (" & varx & ")"


I meant wiggle by assuming you wrote the code in the first place and knew
that nothing works the first time round and you have to "wiggle" the code (ie
make minor changes) a bit.

And I am still not sure whetther it will work.



Secret Squirrel said:
Hello,

So what code am I eliminating from my code? I'm a little confused as to
which code I need to remove. Also, not sure what you mean about wiggling my
code. What do I need to do to wiggle it?

Thanks!

:

hello,


Sub Grunt()
dim Cell
dim varX
range("A10:p10").select
for each cell in selection
if cell.text="" then
cell.text=" ,"
else
varx=varx & cell.text & ","
end if
next
'Your access code here...

end sub

I've used a space instead of an apostrophe.

You may have to "wiggle" the code it a bit to get it right.
SQLStr = "INSERT INTO [Table1] " _
& "Values (" & varx & ")"

Whether the word "Values" is required, I do not know.

Maybe you could record a macro where the range is already preselected, to
see if it picks up the range.



:

I tried the (A10:010") theory and it didn't work.

Do you know how to write it using the "loop"?

:

I ma out of my depth here but ...
where it says
& "Values ('" & Range("A10").Value & "', '" & Range("B10").Value & "',
etc...



maybe just
& "Values ('" & Range("A1:O10").Value,

which covers all the rows instead of layering each cell intot he table one
by one.

Otherwise a for loop for each cell range written to a string

Maybe ask in ACCESS section
- -Mark

:

There are many reports and queries already set up in Access to analyze the
data. And plus the users like using Access rather than Excel.

How can I fix my code to be able to import more than just one row of data?
Right now I have it set up so it will import data from cell A10:O10. What
about multiple rows? How would I do this?

:

Then maybe the stuff in ACCESS can be sent to Excel and the reports can be
generated from there??

HOWEVER For each cell that is blank, put an apostrophe in it. ' Then maybe
it will accept the "blank" cell or maybe a Quote mark "


:

Because this data is used by the users in excel and then it needs to be
uploaded into Access to be used with additional data already in access to run
reports, etc.

:

Why ACCESS? Why do you not just use EXCEL?

:

Hello,
I have this code setup in Excel but I want to be able to upload multiple
rows at one time. Is there a way to incorporate this into my code? Also, is
there a way to upload cells that are blank as well? Right now it won't allow
me to upload cells that are blank. Can this be done?

Sub UploadData()

Dim MyCn As ADODB.Connection
Dim SQLStr As String

Set MyCn = New ADODB.Connection

MyCn.Open "DRIVER={Microsoft Access Driver (*.mdb)}; " & _
"DBQ=\\Bcar1\bcar-net\Quality-ISO\Quality-Control\BC-Quality-Control\BC
QA mdb\BC Quality Action
Database.mdb;SystemDB=\\Bcar1\bcar-net\Quality-ISO\Quality-Control\BC-Quality-Control\BC QA mdb\Sys\sys.mdw;" & _
"Uid=Admin;" & _
"Pwd=password;"
' Replace actual Access file path here
' Note: If database has userID and password, need to specify them here also
' by appending "; UID=MyUserID; PWD=MyPassword"
' - if trouble accessing the file do a net search for help on Connection
Strings

SQLStr = "INSERT INTO [Table1] " _
& "Values ('" & Range("A10").Value & "', '" & Range("B10").Value & "',
'" _
& Range("C10").Value & "', '" & Range("D10").Value & "', '" &
Range("E10").Value & "', '" _
& Range("F10").Value & "', '" & Range("G10").Value & "', '" &
Range("H10").Value & "', '" _
& Range("I10").Value & "', '" & Range("J10").Value & "', '" &
Range("K10").Value & "', '" _
& Range("L10").Value & "', '" & Range("M10").Value & "', '" &
Range("N10").Value & "', '" _
& Range("O10").Value & "', '" & Range("P10").Value & "')"
' NOTE: The above assumes all fields are TEXT data type, that is why the "'"s;
' might have trouble with other data types unless you match the format
expected
' by the database

MyCn.Execute SQLStr

MyCn.Close
Set MyCn = Nothing

End Sub
 
Ad

Advertisements

G

Guest

So there is no way to have it pick up more than one row? I don't know how to
write the loop code. I want each row to be transferred to a table as new
records.

exceluserforeman said:
I think you should leave it the old way.

By creating for .. loops etc will make large volume data quicker to move:

but it would be interesting if my code worked at all ...


- -Mark

Secret Squirrel said:
I will try that and see what happens.

The code does work but it was only set up to upload just the one row from
A10:O10. So since it worked should I still leave my code for the SQLStr or
use yours?

exceluserforeman said:
before your code starts but after the Sub xxx()
dim Cell
dim varX
range("A10:p10").select
for each cell in selection
if cell.text="" then
cell.text=" ,"
else
varx=varx & cell.text & ","
end if
next


where your code says

SQLStr = "INSERT INTO [Table1] " _

instead of al that range data put


& "Values (" & varx & ")"


I meant wiggle by assuming you wrote the code in the first place and knew
that nothing works the first time round and you have to "wiggle" the code (ie
make minor changes) a bit.

And I am still not sure whetther it will work.



:

Hello,

So what code am I eliminating from my code? I'm a little confused as to
which code I need to remove. Also, not sure what you mean about wiggling my
code. What do I need to do to wiggle it?

Thanks!

:

hello,


Sub Grunt()
dim Cell
dim varX
range("A10:p10").select
for each cell in selection
if cell.text="" then
cell.text=" ,"
else
varx=varx & cell.text & ","
end if
next
'Your access code here...

end sub

I've used a space instead of an apostrophe.

You may have to "wiggle" the code it a bit to get it right.
SQLStr = "INSERT INTO [Table1] " _
& "Values (" & varx & ")"

Whether the word "Values" is required, I do not know.

Maybe you could record a macro where the range is already preselected, to
see if it picks up the range.



:

I tried the (A10:010") theory and it didn't work.

Do you know how to write it using the "loop"?

:

I ma out of my depth here but ...
where it says
& "Values ('" & Range("A10").Value & "', '" & Range("B10").Value & "',
etc...



maybe just
& "Values ('" & Range("A1:O10").Value,

which covers all the rows instead of layering each cell intot he table one
by one.

Otherwise a for loop for each cell range written to a string

Maybe ask in ACCESS section
- -Mark

:

There are many reports and queries already set up in Access to analyze the
data. And plus the users like using Access rather than Excel.

How can I fix my code to be able to import more than just one row of data?
Right now I have it set up so it will import data from cell A10:O10. What
about multiple rows? How would I do this?

:

Then maybe the stuff in ACCESS can be sent to Excel and the reports can be
generated from there??

HOWEVER For each cell that is blank, put an apostrophe in it. ' Then maybe
it will accept the "blank" cell or maybe a Quote mark "


:

Because this data is used by the users in excel and then it needs to be
uploaded into Access to be used with additional data already in access to run
reports, etc.

:

Why ACCESS? Why do you not just use EXCEL?

:

Hello,
I have this code setup in Excel but I want to be able to upload multiple
rows at one time. Is there a way to incorporate this into my code? Also, is
there a way to upload cells that are blank as well? Right now it won't allow
me to upload cells that are blank. Can this be done?

Sub UploadData()

Dim MyCn As ADODB.Connection
Dim SQLStr As String

Set MyCn = New ADODB.Connection

MyCn.Open "DRIVER={Microsoft Access Driver (*.mdb)}; " & _
"DBQ=\\Bcar1\bcar-net\Quality-ISO\Quality-Control\BC-Quality-Control\BC
QA mdb\BC Quality Action
Database.mdb;SystemDB=\\Bcar1\bcar-net\Quality-ISO\Quality-Control\BC-Quality-Control\BC QA mdb\Sys\sys.mdw;" & _
"Uid=Admin;" & _
"Pwd=password;"
' Replace actual Access file path here
' Note: If database has userID and password, need to specify them here also
' by appending "; UID=MyUserID; PWD=MyPassword"
' - if trouble accessing the file do a net search for help on Connection
Strings

SQLStr = "INSERT INTO [Table1] " _
& "Values ('" & Range("A10").Value & "', '" & Range("B10").Value & "',
'" _
& Range("C10").Value & "', '" & Range("D10").Value & "', '" &
Range("E10").Value & "', '" _
& Range("F10").Value & "', '" & Range("G10").Value & "', '" &
Range("H10").Value & "', '" _
& Range("I10").Value & "', '" & Range("J10").Value & "', '" &
Range("K10").Value & "', '" _
& Range("L10").Value & "', '" & Range("M10").Value & "', '" &
Range("N10").Value & "', '" _
& Range("O10").Value & "', '" & Range("P10").Value & "')"
' NOTE: The above assumes all fields are TEXT data type, that is why the "'"s;
' might have trouble with other data types unless you match the format
expected
' by the database

MyCn.Execute SQLStr

MyCn.Close
Set MyCn = Nothing

End Sub
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top