Solved

Insert data into another workbook using vba

Posted on 2004-04-04
11
2,452 Views
Last Modified: 2007-12-19
Hello, I keep getting the type mismatch error on this code i have written.  What I have is a useform when the command button is clicked it will open another workbook and input data from the current workbook to the workbook that was just opened.  The problem i am having is it is not recognizing the Data1 values which comes from my listbox on my userform.  The listbox contains names of all the sheets.  For example I select "Jan" in the listbox and press the command button, it will recognize the Listbox value of "Jan" but then when it gets to the copying data from "ah13" to c32 it give me the runtime error, any suggestions.  THank you
Private Sub CommandButton5_Click()
Set Data1 = ListBox2

Application.ScreenUpdating = False
Workbooks.Open Filename:="\\Flieaircwt1\elibrary\20 Temp\QA Statistics Beta"

       
Workbooks("QA Statistics Beta").Sheets(Data1).Range("C32") = Workbooks("EOM_Beta").Sheets(Data1).Range("ah13")
Workbooks("QA Statistics Beta").Sheets(Data1).Range("C33") = Workbooks("EOM_Beta").Sheets(Data1).Range("ah25")
Workbooks("QA Statistics Beta").Sheets(Data1).Range("C34") = Workbooks("EOM_Beta").Sheets(Data1).Range("ah37")
Workbooks("QA Statistics Beta").Sheets(Data1).Range("C35") = Workbooks("EOM_Beta").Sheets(Data1).Range("ah49")
Workbooks("QA Statistics Beta").Sheets(Data1).Range("C36") = Workbooks("EOM_Beta").Sheets(Data1).Range("ah61")
Workbooks("QA Statistics Beta").Sheets(Data1).Range("C37") = Workbooks("EOM_Beta").Sheets(Data1).Range("ah73")
0
Comment
Question by:sandramac
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
11 Comments
 
LVL 50

Expert Comment

by:Dave Brett
ID: 10755210
If its an OLEObject (from the Controls Toolbox) try

Dim Data1 As OLEObject
Set Data1 = ActiveSheet.OLEObjects("ListBox2")

Application.ScreenUpdating = False
Workbooks.Open Filename:="\\Flieaircwt1\elibrary\20 Temp\QA Statistics Beta"
     
Workbooks("QA Statistics Beta.xls").Sheets(Data1.Object.Value).Range("C32").Value = 12 'Workbooks("EOM_Beta").Sheets(Data1.Object.Value).Range("ah13").Value
etc

Cheers

Dave
0
 
LVL 50

Accepted Solution

by:
Dave Brett earned 250 total points
ID: 10755235
sorry misread the form, same idea but try

Private Sub CommandButton1_Click()
Set Data1 = Me.ListBox2

Application.ScreenUpdating = False
Workbooks.Open Filename:="\\Flieaircwt1\elibrary\20 Temp\QA Statistics Beta"

'exit if no value chosen in ListBox
If IsNull(Data1.Value) Then Exit Sub
Workbooks("QA Statistics Beta.xls").Sheets(Data1.Value).Range("C32").Value = 12 'Workbooks("EOM_Beta").Sheets(Data1.Value).Range("ah13").Value

Cheers

Dave

0
 
LVL 33

Expert Comment

by:Jeroen Rosink
ID: 10755559
Is Data1 defined in your macro? And if so in what way?

Is it like
data1 = listbox1.value

where listbox1 is the name of the listbox.

regards,

Jeroen
0
[Live Webinar] The Cloud Skills Gap

As Cloud technologies come of age, business leaders grapple with the impact it has on their team's skills and the gap associated with the use of a cloud platform.

Join experts from 451 Research and Concerto Cloud Services on July 27th where we will examine fact and fiction.

 
LVL 11

Expert Comment

by:lbertacco
ID: 10757021
Just remove the "set" and leave it as:
data1=listbox1
0
 
LVL 50

Expert Comment

by:Dave Brett
ID: 10757122
Why remove the Set?
0
 
LVL 11

Assisted Solution

by:lbertacco
lbertacco earned 250 total points
ID: 10757254
Set is used to assign a value to an object. Here data1 must be a string and to set a value to a string variable you don't have to use set but just
var = "something"
When you do
data1=listbox1
this is interpreted as
data1=listbox1.value
since "value" is the default property for the listbox control
On the other hand, if you do
set data1=listbox
then data1 becomes a listobox object  itself and then Sheets(Data1) fails since Sheets() expect a string or number as the argument.
Also I'd recommend to declare variables as in
Dim data1 as string
to improve readability and type checking.

Ofcourse you could also do directly
Workbooks("QA Statistics Beta").Sheets(listbox1.value).Range("C32") = Workbooks("EOM_Beta").Sheets(listbox1.value).Range("ah13")
but I prefer the use of an auxiliary variable as "data1", maybe just call it "month" rather than "Data1".

0
 
LVL 50

Expert Comment

by:Dave Brett
ID: 10763572
I know what set is used for - I was questioning why you would leave it out

I left Dim out of my second example by mistake, but I prefer to use Dim and Set to properly access the object rather than rely on Variants.

Dim data1 As MSForms.ListBox
Set data1 = Me.ListBox2
0
 
LVL 11

Expert Comment

by:lbertacco
ID: 10763619
Ok then:
leave out "set" because you don't want to have data1 to be a listbox object but a string.
You may argue that you WANT data1 to be a listbox, but then (besides the fact that this is not what sandramac is doing) data1 would be totally useless since it would just be an alias for litsbox2 (and you could directly write Sheets(listbox2.Value) in place of Sheets(Data1.Value)).
0
 
LVL 50

Expert Comment

by:Dave Brett
ID: 10763856
I didn't think or intend my last post to be offensive - sorry if it came accross that way

Its a matter of techique rather than the final outcome that we are debating  so we may as well agree to disagree about how we choose to return the value via data 1  



0
 
LVL 11

Expert Comment

by:lbertacco
ID: 10766385
brettdj, I didn't mean to be offensive either (I was was late for something so I tried to be concise, maybe too much..)
0
 

Author Comment

by:sandramac
ID: 10766544
Thank you both for the help, it helped me out alot.  thanks again
0

Featured Post

[Webinar] How Hackers Steal Your Credentials

Do You Know How Hackers Steal Your Credentials? Join us and Skyport Systems to learn how hackers steal your credentials and why Active Directory must be secure to stop them. Thursday, July 13, 2017 10:00 A.M. PDT

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
Outlook for dependable use in a very small business   This article is about using the Outlook application (part of Microsoft Office) in a very small business, or for homeowners where dependability and reliability are critical requirements. This …
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…

623 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question