PDA

View Full Version : data transfer



labud
08-04-2017, 09:09 PM
Been using Excel for a few versions, but this is my first try at VBA.
I have no idea if this is the best method to do what I want. I am just trying different things, in hopes of finding one that works well.
I have a template 'invoice', from which I create all my invoices. I want to take certain data from those invoices and put it in a database, to be used in a quarterly fashion.
Prior to this I had been using the template wizard with data tracking, but it no longer works well in Excel 2010.Doing some googling I came across a few videos on how to transfer data from workbooks/worksheets to workbooks/worksheets using VBA.I followed the code in one of the videos and tried to adapt it for my own uses.
I am not allowed to provide link at this time.

I enter the data on the template invoice [Service Invoice] [It uses a CommandButton, on the invoice template, which one clicks on to transfer the data].

It opens the database worksheet [TRACKING] and then the following error occurs.
"runtime error 438. Object doesn't support this property or method."
It gives me the opportunity to DEBUG, but when I try to debug, I get the above error at the line - "Worksheets("Tracking").Range ("a1")".
I have googled the error but, cannot understand the various solutions given, mainly because I see no clear explanation of what "runtime error 438" means,so therefore do not know what to look for in solving the problem.
What could be the problem? Does it not like the range? The worksheet has a row of titles [a1 to o1] . I have tried removing them, thinking it would create it's own titles, but it changed nothing.This is my code:


Private Sub CommandButton1_Click()
Dim INVOICE As Single
Dim DATEs As Date
Dim TOTAL As Currency
Dim GNGInvDBase As Workbook

Worksheets("Service Invoice").Select
INVOICE = Range("D6")
Worksheets("Service Invoice").Select
DATEs = Range("D4")
Worksheets("Service Invoice").Select
TOTAL = Range("D41")

Set GNGInvDBase = Workbooks.Open("G:\GNGRen\GNGInvDBase.xlsx")
Worksheets("Tracking").Select
Worksheets("Tracking").Range ("a1")
RowCount = Worksheets("Tracking").Range("a1").CurrentRegion.Rows.Count
With Worksheets("Tracking").Range("a1")
.Offset(RowCount, 1) = INVOICE
.Offset(RowCount, 2) = DATEs
.Offset(RowCount, 3) = TOTAL
End With
GNGInvDBase.Save
End Sub

Bob Phillips
08-05-2017, 02:38 AM
You can't just reference an object, which is what the range on that worksheet is, you have to do something to it (such as set the value of the range), or use it to do to something (such as set a variable to that range).

In this case, I think you can just delete that statement, your next line does something with the currentregion around that cell, so that should be okay and get you started.

Enjoy VBA, you are entering a new and powerful world :)

mdmackillop
08-05-2017, 03:42 AM
Avoid selecting cells. It slows down execution.

Private Sub CommandButton1_Click() Dim INVOICE As Single
Dim DATEs As Date
Dim TOTAL As Currency
Dim GNGInvDBase As Workbook


With Worksheets("Service Invoice")
INVOICE = .Range("D6")
DATEs = .Range("D4")
TOTAL = .Range("D41")
End With


Set GNGInvDBase = Workbooks.Open("G:\GNGRen\GNGInvDBase.xlsx")
With Worksheets("Tracking")
'Omit Worksheets("Tracking").Range ("a1")
RowCount = .Range("a1").CurrentRegion.Rows.Count
With .Range("A1")
.Offset(RowCount, 1) = INVOICE
.Offset(RowCount, 2) = DATEs
.Offset(RowCount, 3) = TOTAL
End With
End With
GNGInvDBase.Save
End Sub

labud
08-08-2017, 05:30 AM
Thanks xld and mdmackillop.
Removing that line

Worksheets("Tracking").Range ("a1")
corrected the problem.
It seems that this will do what I want, but I will have to try it for a bit to see if other problems arise.