VBA import to excel add criteria to set range (1 Viewer)

Graham63

Registered User.
Local time
Today, 09:40
Joined
Mar 22, 2012
Messages
20
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
[COLOR=red]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")
[/COLOR][COLOR=red]lFieldCount = adoRecSet.Fields.Count
End If
End If
End If
End If
[/COLOR]'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 a moderator:

Rx_

Nothing In Moderation
Local time
Today, 03:40
Joined
Oct 22, 2009
Messages
2,803
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/forums/forumdisplay.php?f=55
 

Frothingslosh

Premier Pale Stale Ale
Local time
Today, 05:40
Joined
Oct 17, 2012
Messages
3,276
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.
 
Last edited:

Graham63

Registered User.
Local time
Today, 09:40
Joined
Mar 22, 2012
Messages
20
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
 

mh123

Registered User.
Local time
Today, 09:40
Joined
Feb 26, 2014
Messages
64
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!
 

Graham63

Registered User.
Local time
Today, 09:40
Joined
Mar 22, 2012
Messages
20
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 = (D4:D35)
Rows 65-96= (F4:F35)
Rows 97-128 = (H4:H35)
Rows 129- 160= (J4:J35)
Rows 161-192 = (L4:L35)
Thanks Again
 

Users who are viewing this thread

Top Bottom