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

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 05-10-2016, 07:52 PM   #1
Squid1622
Newly Registered User
 
Join Date: May 2012
Posts: 49
Thanks: 9
Thanked 0 Times in 0 Posts
Squid1622 is on a distinguished road
VBA to Define Range for Upload

I need help with this code. I have an excel worksheet that I will import into an access table. The range to be imported will change with each sheet. I'm 90% of the way there, I just need help getting across the finish line. My problem is this: the code below is supposed to find the last row with text and define the range between the last row and the starting cell. Unfortunately this code automatically calculates and names a range that ends 26 rows from where it's supposed to. Any help would be appreciated.

Code:
Private Sub Workbook_BeforeClose(Cancel As Boolean)
Dim lastRow As Long
Dim Range1 As Range
lastRow = Sheet1.Range("B77").End(xlUp).Row
'Let's assume your table begins at cell A3 and has two columns
Set Range1 = Sheet1.Range("B27").Resize(lastRow, 9)
Range1.Name = "WorkRequest"
Set Range1 = Nothing
End Sub
I tried to upload the file, but because it's a .xlsm file, the forum won't let me upload it.


Last edited by Squid1622; 05-11-2016 at 04:31 AM.
Squid1622 is offline   Reply With Quote
Old 05-10-2016, 11:02 PM   #2
namliam
The Mailman - AWF VIP
 
Join Date: Aug 2003
Location: Amsterdam/The Netherlands
Posts: 11,388
Thanks: 0
Thanked 800 Times in 787 Posts
namliam is a glorious beacon of light namliam is a glorious beacon of light namliam is a glorious beacon of light namliam is a glorious beacon of light namliam is a glorious beacon of light
Re: VBA to Define Range for Upload

Quote:
Originally Posted by Squid1622 View Post
Unfortunately this code automatically calculates and names a range that ends 26 rows from where it's supposed to. Any help would be appreciated.

'Let's assume your table begins at cell A3 and has two columns
Set Range1 = Sheet1.Range("B27").Resize(lastRow, 9)
All of the underlined and bolded parts "go together"
__________________
ruoY yppah namliaM si syawla yppah ot pleh
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.



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

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
Remember to use the scales (Bottom left) or the thumb (bottom right)

Always looking for new challenges (Amsterdam Area)
namliam is offline   Reply With Quote
Old 05-11-2016, 04:02 AM   #3
Squid1622
Newly Registered User
 
Join Date: May 2012
Posts: 49
Thanks: 9
Thanked 0 Times in 0 Posts
Squid1622 is on a distinguished road
Re: VBA to Define Range for Upload

I need the range to start in B27 and go 9 columns across the page. The "A3" you bolded was a part of a comment from the original code. I've put the new code below and removed the comment.

Code:
 
Private Sub Workbook_BeforeClose(Cancel As Boolean)
Dim lastRow As Long
Dim Range1 As Range
lastRow = Sheet1.Range("B77").End(xlUp).Row
Set Range1 = Sheet1.Range("B27").Resize(lastRow, 9)
Range1.Name = "WorkRequest"
Set Range1 = Nothing
End Sub

Squid1622 is offline   Reply With Quote
Old 05-11-2016, 11:13 AM   #4
The_Doc_Man
Happy Retired Curmudgeon
 
Join Date: Feb 2001
Location: Suburban New Orleans, LA, USA
Posts: 14,495
Thanks: 91
Thanked 1,667 Times in 1,548 Posts
The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold
Re: VBA to Define Range for Upload

This looks suspiciously like the problem posed by msk7777. It seems to be a class assignment from its appearance. We try to avoid doing class assignments since we don't get graded for them.
__________________
I'm a certified grandpa (3 times now) and proud of it.
Retired over one year and survived being home all day with the wife. She must really love me.
If I have helped you, please either click the thanks or click the scales.
The_Doc_Man is offline   Reply With Quote
Old 05-11-2016, 11:33 AM   #5
Squid1622
Newly Registered User
 
Join Date: May 2012
Posts: 49
Thanks: 9
Thanked 0 Times in 0 Posts
Squid1622 is on a distinguished road
Re: VBA to Define Range for Upload

Definitely not a class assignment. Been out of college a number of years at this point. Essentially, I'm going to distribute an excel sheet that will allow various locations to submit a work order. Each item needing repaired will have it's own line item, but there's no telling how many lines will be in each worksheet. It would be far too tedious to open up each emailed worksheet (since there are well over 200 locations) and assign a static range. I have some code that will then take every saved worksheet in a specific folder and upload the range to an access table. I need to dynamically change the range, but access doesn't accept dynamic ranges.

The problem I'm running into here is that my range starts on "B27", but I have instructions for the locations in the rows above. When I run the code it basically reads from "B1", counts the rows down to "B27" and then adds the commensurate number of blank records to the new range. What I need to know is how to prevent it from adding those blank records to the range on only take into account the records in the range.
Squid1622 is offline   Reply With Quote
Old 05-11-2016, 09:13 PM   #6
Squid1622
Newly Registered User
 
Join Date: May 2012
Posts: 49
Thanks: 9
Thanked 0 Times in 0 Posts
Squid1622 is on a distinguished road
Re: VBA to Define Range for Upload

So I created a work around for my issue above. Instead of trying to make the named range start at "B27", I just moved the directions to their own sheet and made the named range start at "B1". This seems to have corrected the issue and now everything works as it should.

Now for my second issue. I have the code below which should import all the excel files in a given folder in my access table.

Code:
Public Function importExcelSheets(Directory As String, TableName As String) As Long
On Error Resume Next
 Dim strDir As String
 Dim strFile As String
 Dim I As Long
 I = 0
 If Left(Directory, 1) <> "\" Then
     strDir = Directory & "\"
 Else
     strDir = Directory
 End If
 strFile = Dir(strDir & "*.XLSX")
 While strFile <> ""
     I = I + 1
     strFile = strDir & strFile
     Debug.Print "importing " & strFile
    DoCmd.TransferSpreadsheet acImport, , TableName, tblWorkOrder, True, "WorkRequest!"
     strFile = Dir()
 Wend
 importExcelSheets = I
End Function
When I call the function in the immediate window, it does iterate through all the files in the specified folder, but it does not import any of the named range records into the table.

I attempted to trouble shoot this by creating a button and calling the transferspreadsheet protocol with a click. The code I used is:

Code:
Private Sub WorkOrdercmdBut_Click()
DoCmd.TransferSpreadsheet acImport, , "tblWorkOrder", "Z:\utilities\WorkOrders\RichmondVA05112016.xls", True, "Try!"
 End Sub
When I run it though, I get:

Quote:
Run-Time error 3125:

'Try$' is not a valid name. Make sure that it does not include invalid characters or punctuation and that it is not too long.
I've uploaded a copy of the table that I'm trying to import.
Attached Files
File Type: xls RichmondVA05112016.xls (29.5 KB, 94 views)
Squid1622 is offline   Reply With Quote
Old 05-11-2016, 10:09 PM   #7
namliam
The Mailman - AWF VIP
 
Join Date: Aug 2003
Location: Amsterdam/The Netherlands
Posts: 11,388
Thanks: 0
Thanked 800 Times in 787 Posts
namliam is a glorious beacon of light namliam is a glorious beacon of light namliam is a glorious beacon of light namliam is a glorious beacon of light namliam is a glorious beacon of light
Re: VBA to Define Range for Upload

Quote:
Originally Posted by Squid1622 View Post
I need the range to start in B27 and go 9 columns across the page. The "A3" you bolded was a part of a comment from the original code. I've put the new code below and removed the comment.
I guessed as much, however if you change one part of the code, you can expect the code to "misbehave" or behave differently from what you expect.

What I tried to point out is that you changed B1 to B27 and got an extra 26 lines. This is obviously due to the resize adding the number of lines that are stored in lastRow... substracting the surplus rows from lastRow should fix your problem
Set Range1 = Sheet1.Range("B27").Resize(lastRow - 26, 9)

I like to try and "teach a (wo)man to fish, instead of handing them a fish"

Code:
 If Left(Directory, 1) <> "\" Then
     strDir = Directory & "\"
Would you expect this to work? Let me tell you no, it doesn't work

If you are trying to import your named range WorkRequest ... try this
Code:
docmd.Transferspreadsheet acImport, acspreadsheettypeexcel7, "tablename", "Z:\utilities\WorkOrders\RichmondVA05112016.xls", true, "WorkRequest"

__________________
ruoY yppah namliaM si syawla yppah ot pleh
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.



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

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
Remember to use the scales (Bottom left) or the thumb (bottom right)

Always looking for new challenges (Amsterdam Area)
namliam is offline   Reply With Quote
Old 05-12-2016, 03:15 AM   #8
Squid1622
Newly Registered User
 
Join Date: May 2012
Posts: 49
Thanks: 9
Thanked 0 Times in 0 Posts
Squid1622 is on a distinguished road
Re: VBA to Define Range for Upload

Quote:
What I tried to point out is that you changed B1 to B27 and got an extra 26 lines. This is obviously due to the resize adding the number of lines that are stored in lastRow... substracting the surplus rows from lastRow should fix your problem
Set Range1 = Sheet1.Range("B27").Resize(lastRow - 26, 9)
This definitely makes sense and I will try to implement the change when I go to work this morning. It would be much easier for everything to be on one sheet instead of the directions on one and the work sheet on the other. Invariably, people will not look at the directions and assume the know what they're doing.

I'll play around with your suggestions a little as well and see if I can make them work.
Squid1622 is offline   Reply With Quote
Old 05-12-2016, 05:10 AM   #9
Squid1622
Newly Registered User
 
Join Date: May 2012
Posts: 49
Thanks: 9
Thanked 0 Times in 0 Posts
Squid1622 is on a distinguished road
Re: VBA to Define Range for Upload

Ok, so I've amended the method I'm using to this code I found here http://www.access-programmers.co.uk/...d.php?t=158308.

Below is how I've amended it:

Code:
 
 Private Sub WorkOrdercmdBut_Click()
  If MsgBox("This will open the Excel folder for spreadsheet imports.  Continue?", vbYesNoCancel) = vbYes Then
 Dim i As Integer
  Dim tblStr As String
    Dim varItem As Variant
 i = 1
tblStr = ""
       With Application.FileDialog(msoFileDialogFilePicker)
          With .Filters
           .Clear
           .Add "All Files", "*.*"
         End With
              .AllowMultiSelect = True
             .InitialFileName = "Z:\Utilities\WorkOrders"
             .InitialView = msoFileDialogViewDetails
                     If .Show Then
                       For Each varItem In .SelectedItems
                         For i = 1 To Len(varItem)
                          If IsNumeric(Mid(CStr(varItem), i, 1)) Then
                            tblStr = tblStr & Mid(CStr(varItem), i, 1)
                          End If
                        Next i
                         If Right(CStr(varItem), 4) = ".xlsm" Then
 DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel12, "tblWorkOrder", CStr(varItem), True, "WorkRequest!"
                            i = i + 1
                              DoCmd.OpenTable tblStr, acViewNormal, acReadOnly
                                MsgBox "Data Transferred Successfully!"
                                  DoCmd.Close
                                    tblStr = ""
                        End If
                       Next varItem
                    DoCmd.Close
                     End If
       End With
   End If
 End Sub
Everything works right up until the import. I can get the dialog box to open and I can select the files that I need, but when I click ok it does not import my named range. Does the problem lie here?

Code:
 
   i = i + 1
                              DoCmd.OpenTable tblStr, acViewNormal, acReadOnly
                                MsgBox "Data Transferred Successfully!"
                                  DoCmd.Close
                                    tblStr = ""
Squid1622 is offline   Reply With Quote
Old 05-12-2016, 06:38 AM   #10
Squid1622
Newly Registered User
 
Join Date: May 2012
Posts: 49
Thanks: 9
Thanked 0 Times in 0 Posts
Squid1622 is on a distinguished road
Re: VBA to Define Range for Upload

namliam,

Thank you for all your help. I now have a function that does exactly what I want it to do. I was able to make the changes on the code myself and this is what I came up with.

Code:
 
 Private Sub WorkOrdercmdBut_Click()
  If MsgBox("This will open the Excel folder for spreadsheet imports.  Continue?", vbYesNoCancel) = vbYes Then
 Dim i As Integer
  Dim tblStr As String
    Dim varItem As Variant
 i = 1
tblStr = ""
       With Application.FileDialog(msoFileDialogFilePicker)
          With .Filters
           .Clear
           .Add "All Files", "*.*"
         End With
              .AllowMultiSelect = True
             .InitialFileName = "Z:\Utilities\WorkOrders"
             .InitialView = msoFileDialogViewDetails
                     If .Show Then
                       For Each varItem In .SelectedItems
                           DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel12, "tblWorkOrder", CStr(varItem), True, "WorkRequest"
                            i = i + 1
                              DoCmd.OpenTable "tblWorkOrder", acViewNormal, acReadOnly
                                MsgBox "Data Transferred Successfully!"
                                  DoCmd.Close
                                    tblStr = ""
                     Next varItem
                    DoCmd.Close
                     End If
       End With
End If
 
End Sub
Squid1622 is offline   Reply With Quote
Old 05-13-2016, 12:36 AM   #11
namliam
The Mailman - AWF VIP
 
Join Date: Aug 2003
Location: Amsterdam/The Netherlands
Posts: 11,388
Thanks: 0
Thanked 800 Times in 787 Posts
namliam is a glorious beacon of light namliam is a glorious beacon of light namliam is a glorious beacon of light namliam is a glorious beacon of light namliam is a glorious beacon of light
Re: VBA to Define Range for Upload

Glad you were able to solve your problem

__________________
ruoY yppah namliaM si syawla yppah ot pleh
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.



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

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
Remember to use the scales (Bottom left) or the thumb (bottom right)

Always looking for new challenges (Amsterdam Area)
namliam is offline   Reply With Quote
The Following User Says Thank You to namliam For This Useful Post:
Squid1622 (05-18-2016)
Reply

Tags
excel & access , import , vba access 2010 , vba access 2010 excel

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Define Excel Range from VBA in Access rachelkm2 Modules & VBA 3 12-14-2010 06:16 PM
Iif to define where clause jimbot Queries 2 08-29-2008 01:55 AM
How To Define This Query? Chipcom Queries 3 04-10-2007 07:51 PM
Define search Spikemannen Queries 1 04-20-2006 12:33 AM
Define cascade from SQL misunders2d Tables 1 10-13-2004 04:08 AM




All times are GMT -8. The time now is 03:25 PM.


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