Loading data from excel to access with VBA 1

[alibongo]

Hello,

So far I have written the following code to load an excel spreadhseet into my database, I use a form with which to control the entry of path names (see below). First my code takes the data into a unprocessed file from its original location and once it has loaded it it sends it to a processed file, at least it seems to work like that!

Private Sub LoadNewData_Click()
Dim subdir As String, strfilename As String, strFullName As String
Dim counter As Integer, Path As String, path2 As String, path3 As
String

strfilename = ""
strFullName = ""

Path = DLookup("[pathdetail]", "tblpaths", "[path]=1&quot
path2 = DLookup("[pathdetail]", "tblpaths", "[path]=2&quot
path3 = DLookup("[pathdetail]", "tblpaths", "[path]=3&quot

strfilename = Dir(Path & "*.XLS&quot
strFullName = Path & strfilename
counter = 0

Do Until strFullName = Path
FileCopy strFullName, path2 & strfilename
Kill strFullName
strfilename = Dir
strFullName = Path & strfilename
counter = counter + 1
Loop

If counter = 0 Then
MsgBox "No files loaded - original directory was empty", vbOKOnly
ElseIf counter > 0 Then
MsgBox counter & " files have been sucessfully moved to the correct
directory to be processed", vbOKOnly
End If

counter = 0
strfilename = Dir(path2 & "*.XLS&quot
strFullName = path2 & strfilename

Do Until strFullName = path2

DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9,
"tblMainData", strFullName, True
counter = counter + 1
FileCopy strFullName, path3 & "\" & strfilename
Kill (strFullName)
strfilename = Dir
strFullName = path2 & strfilename

Loop

If counter = 0 Then
MsgBox "No files imported as directory was empty", vbOKOnly
ElseIf counter > 0 Then
MsgBox counter & " files have been sucessfully been processed",
vbOKOnly
End If

End Sub

What I would like to do is this (FINALLY getting to the point, sorry)- Is there a way of specifying the row at which you start to load data from on the excel spreadsheet and specifying the last row you wish to export.

i.e. Load data from row 50 to row 100 on spreadsheet x

I would like to do this thorugh a form that you could enter the rows you wished to load.
Thanks for any help, I know you could cut and paste the required data into the table but I would like to automate it, this however may be the completely wrong way of going about it.
Cheers, Ali

 

[GHolden]

If you're saying what I think you're saying....
With the TransferSpreadsheet you can specify the cell range to import, eg

DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel97, "tblTable", "C:\FileName", False, "A150"

 

[UrbaneRove]

Addendum to the post

Linking to a particular sheet in the speadsheet

DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel97, "tblTable", "C:\FileName", False, "A150"


To reference a particular sheet in the Excel SpeadSheet

sheet "Budget"
row/column "A150"

"Budget!A150"


UrbaneRove

 

[alibongo]

Hi,

Thanks for the help this works for specifying the correct row. If I wanted to to be able to change the row from a form would I just change the "A150" reference in the code to reference the textbox on the form I was using to enter the number range with an input mask on the textbox?

Thanks again, I've tried a couple of things but I keep getting errors. Sorry not very good at VBA, I only use it occasionally.

Alistair

 

[GHolden]

If you use an imput mask in a text box eg. something like
LA##\:LA## (allows A1:A1 to ZZ99:ZZ99) the only problem is that the literal character ':' is not copied to the string, so you would have to do some code to add it.

Might be easier to have two text boxes one for start of range one for finish of range then join them together.

eg.

txtRangeStart - mask LA##
txtRangeFinish - mask LA##

strRange = txtRangeStart & ":" & txtRangeFinish

You would still have to do some validation because someone could still enter AA as one of the values.
If you know your values will go no higher than single character (ie. no higher than Z colunm) then it's a bit easier, just use the mask

L0# for each text box.

So, however you decide to set up your text boxes, if you have the following sub....


Sub mImportXLS(ByVal vstrRange As String)
On Error Goto ErrorHandler

DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel97, "tblTable", "D:\Test.xls", False, vstrRange

Exit Sub

ErrorHandler:

MsgBox Err.Description

End Sub

then call it say from a command button.

Private Sub cmdImportXLS_Click()

mImportXLS (txtRangeStart & ":" & txtRangeFinish)

End Sub

Hope this is some help.

 

[alibongo]

Hello again

I have one more problem regarding this. I don't know how to combine the range and the path functions. I have tried to set it up like this but I am not sure where to put the import of the RangeStart and RangeFinish function. This is how I tried to change it so far. It is important that I call the spreadsheet from both the path and the range.

Private Sub LoadNewData_Click()

Dim subdir As String, strfilename As String, strFullName As String

Dim counter As Integer, path2 As String, txtRangeStart As String, txtRangeFinish As String

strfilename = ""
strFullName = ""
strRange = ""

Path = DLookup("[pathdetail]", "tblpaths", "[path]=1&quot

counter = 0
strfilename = Dir(path2 & "*.XLS&quot
strFullName = path2 & strfilename
strRange = txtRangeStart & ":" & txtRangeFinish

Do Until strFullName = path2

DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, "tblTest", strFullName, True
counter = counter + 1
strfilename = Dir
strFullName = path2 & strfilename

Loop

If counter = 0 Then
MsgBox "No files imported as directory was empty", vbOKOnly
ElseIf counter > 0 Then
MsgBox counter & " files have been sucessfully moved to be processed", vbOKOnly
End If

End Sub

Any more help would be gratefully appreciated. Thanks

 

[GHolden]

It might be me, but you don't appear to set a value to path2. Path has a value set but isn't used.
Just to clarify....
Are you trying to move to a particular path, and import all the xls files in that path to a particular table?

 

[alibongo]

hello G Holden

I was trying to import to a particular path thanks for noticing that. I had multiple paths in my first set of code but cut the wrong one out when I was changing the code.

I have changed it so that Path now has a value but I still don't know where to put the RangeStart and RangeFinish part of the code.

If you could help any more it would be great

Thanks

 

[GHolden]

You should just be able to plonk it on the end of the import statement....

DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel97, "tblTest", strFullName, True, strRange

 

[alibongo]

Hello,

Sorry still not working, I keep getting a Run-time error 2391 saying field "F1" not found. I have checked the spreadsheet that I am importing and it has no field name f1 and the tbl I am importing to has no field name f1, so I assume I must still have the code wrong. Sorry to bother you with this again. I will need to read more and get better at this.

Sub ImportXLS(ByVal vstrRange As String)
Dim subdir As String, strfileextension As String, strFullName As String
Dim counter As Integer, Path As String, StartRange As String, FinishRange As String

strfileextension = ""
vstrRange = ""
strFullName = ""

Path = DLookup("[pathdetail]", "tblpaths", "[path]=1&quot
StartRange = DLookup("[StartRange]", "tblpaths", "[path]=1&quot
FinishRange = DLookup("[FinishRange]", "tblpaths", "[path]=1&quot

counter = 0
strfileextension = (".xls&quot
strFullName = Path & strfileextension
vstrRange = StartRange & ":" & FinishRange

If strFullName = strFullName Then
counter = counter + 1
DoCmd.TransferSpreadsheet acImport, , "tblTest", strFullName, False, vstrRange
End If

If counter = 0 Then
MsgBox "No spreadsheet at this pathname, please check and try again", vbOKOnly
ElseIf counter > 0 Then
MsgBox counter & " spreadsheet has been successfully imported", vbOKOnly
End If
End Sub

Private Sub LoadNewData_Click()

ImportXLS (StartRange & ":" & FinishRange)

End Sub

Thanks very much for your help. I have also tried False and True on the transfer part.

 

[GHolden]

There seems to be a problem with the range, you are passing it to the sub with the line

ImportXLS (StartRange & ":" & FinishRange)

then you set it to "" with

vstrRange = ""

and then look it up and set it with

StartRange = DLookup("[StartRange]", "tblpaths", "[path]=1&quot
FinishRange = DLookup("[FinishRange]", "tblpaths", "[path]=1&quot

vstrRange = StartRange & ":" & FinishRange

If you intend to look it up remove it from the ImportXLS declaration and the sub call. If you want to pass it remove the code that resets it in the sub.

This may not be the problem exactly, but probably isn't helping !!

ps. it's no bother, we're here to help each other