Go Back   Access World Forums > Microsoft Access Discussion > Modules & VBA

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 03-18-2015, 12:06 AM   #1
Graham63
Newly Registered User
 
Join Date: Mar 2012
Posts: 20
Thanks: 2
Thanked 0 Times in 0 Posts
Graham63 is on a distinguished road
Question VBA import to excel add criteria to set range

Hi Guys,
I've adapted some code I found which works and allows me to import data from my access table "Device Text" to a specific range in an existing excel worksheet by copying a field "TextEdit" in the recordset. the code is placed in a module behind a command button on an excel worksheet. I have used import to excel because this seems to be less complicated for my needs than exporting from access.
Everything works fine if a single range is set , however I would like to set the range(rng) criteria in the code below to start at a certain cell depending on the value in another field in my access table called "LoopID" this is a number field which is not unique and can be 1,2,3 or 4. I know it wont be evaluated but I've entered the gist in red in my code to give you an idea. I'm self teaching and can use basic code but I'm not sure how to approach this.
I don't need to import it but I suspect I have to bring LoopID into the recordset somehow to use it,
Thanks in advance

Code:
 'DIM STATEMENTS
Dim strMyPath As String, strDBName As String, strDB As String, strSQL As String
Dim i As Long, n As Long, lFieldCount As Long
Dim rng As Range
 'instantiate an ADO object using Dim with the New keyword:
Dim adoRecSet As New ADODB.Recordset
Dim connDB As New ADODB.Connection
 '--------------
'THE CONNECTION OBJECT
 strDBName = "C:\Users\graha_000\Desktop\Projects Database Working and most recent Backup\Projects Database 22-02-15.accdb"
strMyPath = "C:\Users\graha_000\Desktop\Protec X400 Excel Text Editor.xls"
strDB = strMyPath & "\" & strDBName
  'Connect to a data source:
connDB.Open ConnectionString:="Provider = Microsoft.ACE.OLEDB.12.0; data source=" & strDBName
 '--------------
'OPEN RECORDSET, ACCESS RECORDS AND FIELDS
 Dim ws As Worksheet
'set the worksheet:
Set ws = ActiveWorkbook.Sheets("Text")
 'Set the ADO Recordset object:
Set adoRecSet = New ADODB.Recordset
 'Opening the table named SalesManager:
strTable = "DeviceText"
 '--------------
 'copy all records from the selected fields (TextEdit):
strSQL = "SELECT TextEdit FROM DeviceText"
adoRecSet.Open Source:=strSQL, ActiveConnection:=connDB, CursorType:=adOpenDynamic, LockType:=adLockOptimistic
If LoopID = 1 Then
Set rng = ws.Range("C10")
lFieldCount = adoRecSet.Fields.Count
If LoopID = 2 Then
Set rng = ws.Range("C138")
lFieldCount = adoRecSet.Fields.Count
If LoopID = 3 Then
Set rng = ws.Range("C266")
lFieldCount = adoRecSet.Fields.Count
If LoopID = 4 Then
Set rng = ws.Range("C394")
lFieldCount = adoRecSet.Fields.Count
End If
End If
End If
End If
'copy column names in first row of the worksheet:
'rng.Offset(0, i).Value = adoRecSet.Fields(i).Name
 'Next i
 
'copy record values starting from second row of the worksheet:
rng.Offset(0, 0).CopyFromRecordset adoRecSet
 'select a column range:
Range(ws.Columns(1), ws.Columns(lFieldCount)).AutoFit
 adoRecSet.Close
 
'copy record values starting from second row of the worksheet:
'rng.Offset(0, 0).CopyFromRecordset adoRecSet
'to copy 4 rows and 3 columns of the recordset to excel worksheet:
'rng.Offset(1, 0).CopyFromRecordset Data:=adoRecSet, MaxRows:=4, MaxColumns:=3
 'select a column range:
'Range(ws.Columns(1), ws.Columns(lFieldCount)).AutoFit
'worksheet columns are deleted because this code is only for demo:
'Range(ws.Columns(1), ws.Columns(lFieldCount)).Delete
 'adoRecSet.Close
 '--------------


Last edited by Mile-O; 03-18-2015 at 02:41 AM. Reason: Adding [CODE] [/CODE] tags for readability
Graham63 is offline   Reply With Quote
Old 03-19-2015, 08:19 AM   #2
Rx_
Nothing In Moderation
 
Rx_'s Avatar
 
Join Date: Oct 2009
Location: Denver, Colorado
Posts: 2,794
Thanks: 635
Thanked 336 Times in 307 Posts
Rx_ has a spectacular aura about Rx_ has a spectacular aura about Rx_ has a spectacular aura about
Re: VBA import to excel add criteria to set range

It is my first cup of coffee after a long night.
I kind of see what you are trying to accomplish. Just a little fuzzy on the business logic goals.
Let me suggest rewriting your business objective so we can make better suggestions.
Also, this site has an Excel forum that is typically Access Programming where Excel programming is involved. Just guessing that there is enough Excel activity in this request for your question to get more attention there.
The Excel Forum is found here: http://www.access-programmers.co.uk/...splay.php?f=55
__________________
Were you lucky enough to get an answer? Please mark your question as [SOLVED] The original poster can go to Thread Tools to mark it as Solved.

Quotation Thomas Jefferson: "Peace is that brief glorious moment in history when everybody stands around reloading."

There are 2 Kinds of Countries on this Planet
1. Those that use the Metric System
2. Those that had a man walk on the moon

Denver, Colorado - The "Mile High City" - non-metric!
Rx_ is offline   Reply With Quote
Old 03-19-2015, 08:25 AM   #3
Frothingslosh
Premier Pale Stale Ale
 
Frothingslosh's Avatar
 
Join Date: Oct 2012
Location: Flint, Michigan, USA
Posts: 3,194
Thanks: 82
Thanked 452 Times in 410 Posts
Frothingslosh will become famous soon enough Frothingslosh will become famous soon enough
Re: VBA import to excel add criteria to set range

For starters, I would replace those nested IF...THEN blocks with a single SELECT CASE block.

Gimme a few minutes to dig something up - I have some code sitting around here that involves setting an excel range directly through Access.

Edit: Okay, the code I was thinking of was actually in an Excel sheet, and wasn't really helpful. Still, I'd switch your IF blocks in to a SELECT block. After that, you're probably best off re-posting to the Excel subforum.

__________________

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.

Last edited by Frothingslosh; 03-19-2015 at 08:33 AM.
Frothingslosh is offline   Reply With Quote
Old 03-19-2015, 11:49 AM   #4
Graham63
Newly Registered User
 
Join Date: Mar 2012
Posts: 20
Thanks: 2
Thanked 0 Times in 0 Posts
Graham63 is on a distinguished road
Re: VBA import to excel add criteria to set range

Firstly, thanks very much for your informative replies,
To be honest the reason for posting to an Access thread with this one is because the db leads my project and results from some difficulty in doing this the other way around(exporting this from Access to the Excel range). The spreadsheet is an existing .exe which belongs to a customer. The spreadsheet is used within a commissioning software program to upload the text to a control panel. It can be modified programmatically in some ways but the layout and ranges are fixed. I collect the text in Access anyway and there can be over 500 lines in a batch which can be laborious to transfer line by line. I hope this is making a little more sense and based on any further advice i'll flip to the Excel forum. Thanks again
Graham63 is offline   Reply With Quote
Old 03-24-2015, 10:15 AM   #5
mh123
Newly Registered User
 
Join Date: Feb 2014
Posts: 64
Thanks: 11
Thanked 9 Times in 9 Posts
mh123 is on a distinguished road
Re: VBA import to excel add criteria to set range

you can do this easier from Access (imo)...
open your workbook with vba and then set a named range, so with your code above it would be like;
ws.Range("A1:G1").NAME = "cheese"
then you can save as and quit then run a docmd.transferspreadsheet, on the last setting ensure the named range is set to "cheese" and it will export into that specific range no matter which worksheet it's on.

This way you can just dlookup your loopID if you really need it and also, as suggested look into a SELECT CASE block - much cleaner!

I can post example code for you tomorrow if needed not got anything of the sorts on this PC. HTH!
mh123 is offline   Reply With Quote
Old 03-24-2015, 01:01 PM   #6
Graham63
Newly Registered User
 
Join Date: Mar 2012
Posts: 20
Thanks: 2
Thanked 0 Times in 0 Posts
Graham63 is on a distinguished road
Re: VBA import to excel add criteria to set range

Thanks very much for your reply,
I returned to this just yesterday and with some tweaking to the input SQL I've been able to simplify the recordset so that it only needs to start at the range (C:10) and this has provided me with a more straightforward solution. Although I got it working to a certain extent with a case statement it took away the need for it entirely.
However I would very much appreciate your advice on a very similar matter.
I have a similar text field of 192 rows which I want to import from access into excel, the rows follow a particular pattern on the input spreadsheet
Rows 1-35 = (B4:B35)
Rows 36-64 = (D435)
Rows 65-96= (F4:F35)
Rows 97-128 = (H4:H35)
Rows 129- 160= (J4:J35)
Rows 161-192 = (L4:L35)
Thanks Again

Graham63 is offline   Reply With Quote
Reply

Tags
criteria , excel & access , import , range

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Date range in criteria bring back records out of range?? shdale Queries 7 12-05-2012 11:39 AM
Import Excel files choosing only a specific named range Richard1980 Modules & VBA 7 08-15-2012 04:52 AM
Import range from Excel to Access with field names fredalina Modules & VBA 2 12-22-2008 02:21 PM
Named Range Import mdjks Tables 0 03-30-2005 12:33 PM
Import Dynamic Named Range from Excel Ian Mac Modules & VBA 2 02-05-2005 08:47 PM




All times are GMT -8. The time now is 09:56 AM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Featured Forum post


Sponsored Links


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World