Excel VBA: Problem with porting selected data from one excel workbook to another
Hi guys,
I have an issue regarding porting data from one excel workbook to another workbook.
I want to select specific data from a specific sheet in an excel file and copy it into a new excel file.
ScreenShot005.jpg
e.g. copy C4 of excel1.xlsx into a new excel file excel2.xlsx
I've tried the following codes and links but to no avail:
Codes:
Private Sub CommandButton1_Click()
Dim oExcel as Excel.Application
Dim oWB as Workbook
Set oExcel = new Excel.Application
Set oWB = oExcel.Workbooks.Open(C:\Users\Sam\Desktop\MP Stuff\TP Monthly Report v0.xlsx)
Dim DateRange As Integer
Dim AcceptedCalls As Single
Dim PortedData As Workbook
Worksheets("sheet2").Select
'DateRange = Range("A4")
'DateRange = Range("B4" + "B27")
DateRange = Sum(B4, B27)
Set PortedData = Workbooks.Open("C:\Users\Sam\Desktop\MP Stuff\PortedData.xlsx")
Worksheets("sheet1").Select
Worksheets("sheet1").Range("A1").Select
RowCount = Worksheets("sheet1").Range("A1")
With Worksheets("Sheet1").Range("A1")
.Offset(RowCount, 0) = DateRange
.Offset(RowCount, 1) = AcceptedCalls
End With
PortedData.Save
End Sub
Re: Excel VBA: Problem with porting selected data from one excel workbook to another
You declared AcceptedCalls as single but I don't see where the variable is set, so it will probably default to 0.
BTW when posting code please use code tags. Highlight your code and press the # button in the toolbar.
Thanks,
Solus
Please remember the following:
1. Use [code] code tags [/code]. It keeps posts clean, easy-to-read, and maintains VBA formatting.
Highlight the code in your post and press the # button in the toolbar.
2. Show appreciation to those who have helped you by clicking below their posts.
3. If you are happy with a solution to your problem, mark the thread as [SOLVED] using the tools at the top.
Re: Excel VBA: Problem with porting selected data from one excel workbook to another
Originally Posted by Norie
Then there's no need to create another instance of Excel, and doing so can cause problems when copying/pasting.
Erm, i need to have a "3-tier" system because
the source file is a file with raw data generated from a Microsoft Access function. You might suggest that we could just change the queries in the Microsoft Access to suit my end product, however it'll be messy if everything is ported straight from the Microsoft Access as you can see in the previous posts, there're addition of columns. I hope that explains my need for another instance of excel. haha
Re: Excel VBA: Problem with porting selected data from one excel workbook to another
Sorry, I still don't see why you need another instance of Excel.
Why can't you have all the 3 workbooks in the same instance?
Option Explicit
Private Sub CommandButton1_Click()
Dim wbReport As Workbook
Dim PortedData As Workbook
Dim DateRange As Long
Dim AcceptedCalls As Single
Dim RowCount As Long
Set wbReport = Workbooks.Open("C:\Users\Sam\Desktop\MP Stuff\TP Monthly Report v0.xlsx")
DateRange = wbReport.Worksheets("sheet2").Range("B4").Value + wbReport.Worksheets("sheet2").Range("B27").Value
Set PortedData = Workbooks.Open("C:\Users\Sam\Desktop\MP Stuff\PortedData.xlsx")
With PortedData
RowCount = .Worksheets("Sheet1").Range("A1")
With .Worksheets("Sheet1").Range("A1")
.Offset(RowCount, 0) = DateRange
.Offset(RowCount, 1) = AcceptedCalls
End With
.Save
End With
PortedData.Close SaveChanges:=True
wbReport.Close SaveChanges:=True
End Sub
Re: Excel VBA: Problem with porting selected data from one excel workbook to another
The least i can afford is 2 excel files - Source and Executable file.
It is impossible to store everything because the actual source file have 16 sheets in it and I need a separate executable excel file to select the source file because it'll be a different source file every month (I'm kind of doing a monthly report program).
and yes, what i meant was like the codes you provided - all the vba codes into one excel file but there will be 3 excel files (the monthly report, porteddata.xlsx and the excel file with the vba codes).
Re: Excel VBA: Problem with porting selected data from one excel workbook to another
Originally Posted by Norie
Like I said I based it on your code.
If that code didn't have the correct sheet names then you would get the 'Subscript out of range error(s).
Oh so that's how the 'Subscript out of range' error comes about. Ermm, but i still get the error even after i changed it to the correct sheet (Sheet3). Mind having a look at my excel file? http://www.4shared.com/file/Wt_gpxc8...ourceData.html
Re: Excel VBA: Problem with porting selected data from one excel workbook to another
I guess I'll have to provide some background then.
I'm a student doing my internship at my school's call centre. My group is doing a project for the centre to reduce the number of human errors in monthly reports. This is the outline of my plan:
The source file - 'TP Monthly Report.xlsx' - will be generated by a Microsoft Access function. The proper file name for it is actually 'TP Monthly Report [Month].xlsx' ,thus I need a function that can prompt a dialog box to choose the source file instead of modifying the code every month.
The ExecutableFile.xlsm is the main thing in this entire process. By right, it's suppose to prompt a dialog box to choose the source file but right now it's hard coded. This executable file will pull data from different sheets in the source file and paste them into the output file. Below are some examples: ScreenShot006.jpg ScreenShot007.jpg
This is my code progress so far:
Option Explicit
Private Sub CommandButton1_Click()
Dim wbReport As Workbook
Dim PortedData As Workbook
Dim DateRange As Long
Dim AcceptedCalls As Single
Dim RowCount As Long
Dim PortedData2 As Object
Dim sheetno As Integer
Set wbReport = Workbooks.Open("C:\Users\Sam\Desktop\MP Stuff\TP Monthly Report.xlsx")
DateRange = wbReport.Worksheets("2").Range("B4").Value + wbReport.Worksheets("2").Range("B27").Value
'Set PortedData = Workbooks.Open("C:\Users\Sam\Desktop\MP Stuff\PortedData.xlsx")
Set PortedData2 = CreateObject("EXCEL.APPLICATION")
Set PortedData = PortedData2.Workbooks.Add
'PortedData.Worksheets.Add Count:="1"
sheetno = 1
PortedData.Worksheets(sheetno).Activate
With PortedData
RowCount = .Worksheets("Sheet1").Range("A1")
With .Worksheets("Sheet1").Range("A1")
.Offset(RowCount, 0) = DateRange
.Offset(RowCount, 1) = AcceptedCalls
End With
End With
PortedData.SaveAs ("C:\Users\Sam\Desktop\MP Stuff\test.xlsx")
PortedData.Close SaveChanges:=True
wbReport.Close SaveChanges:=True
End Sub
My expected output will be something like this excel file - https://app.box.com/s/9zoetex1zxd2ll82fmxw
I want those selected data to be placed into a table in the output excel file. Is it possible to do that??
PS: I hope this roughly paint a picture of what I'm doing haha.
What i meant in the pictures is that I want to create tables in the output file and fill them up with the selected values (e.g. A4 from sheet 2 of TP Monthly Report.xlsx). The rest of the columns such as 'Dropped Calls' will be hard-coded.
Re: Excel VBA: Problem with porting selected data from one excel workbook to another
Hey guys, i've solved this part already. I'll post the code here in case anyone wants to reference it (it's hard-coded):
PHP Code:
Option Explicit
Private Sub CommandButton1_Click()
Dim wbReport As Workbook
Dim wbSource As Variant
Dim PortedData As Workbook
'Table1 -Service Desk Monitoring Variables
Dim AcceptedCalls1 As Long
Dim AcceptedCalls2 As Long
Dim AcceptedCalls3 As Long
Dim AcceptedCalls4 As Long
Dim AcceptedCalls5 As Long
Dim DateRange1 As String
Dim DateRange2 As String
Dim DateRange3 As String
Dim DateRange4 As String
Dim DateRange5 As String
Dim DroppedCalls As Single
'Table2 -Customer Reporting Source Variables
Dim Tickets1 As Long
Dim Tickets2 As Long
Dim Tickets3 As Long
'Table3 -Service Request Tickets(IIT) Variables
Dim SRDate1 As String
Dim SRDate2 As String
Dim SRDate3 As String
Dim SRDate4 As String
Dim SRDate5 As String
Dim SRTickets1 As Long
Dim SRTickets2 As Long
Dim SRTickets3 As Long
Dim SRTickets4 As Long
Dim SRTickets5 As Long
Dim TotalSRTickets As Long
Dim SRResponseTime As String
'Table4 -Incident Tickets(IIT) Variables
Dim INDate1 As String
Dim INDate2 As String
Dim INDate3 As String
Dim INDate4 As String
Dim INDate5 As String
Dim INTickets1 As Long
Dim INTickets2 As Long
Dim INTickets3 As Long
Dim INTickets4 As Long
Dim INTickets5 As Long
Dim INResponseTime As String
'Table5 -Top Incidents(IIT) Variables
'Table6 -Top Service Requests(IIT)
'Table7 -Service Request Tickets(AAO)
Dim AAOSRdate1 As String
Dim AAOSRdate2 As String
Dim AAOSRdate3 As String
Dim AAOSRdate4 As String
Dim AAOSRdate5 As String
Dim AAOSRtix1 As Long
Dim AAOSRtix2 As Long
Dim AAOSRtix3 As Long
Dim AAOSRtix4 As String
Dim AAOSRtix5 As Long
Dim AAOSRrt As String
'Table8 -Incident Response Tickets(AAO)
Dim AAOINdate1 As String
Dim AAOINdate2 As String
Dim AAOINdate3 As String
Dim AAOINdate4 As String
Dim AAOINdate5 As String
Dim AAOINtix1 As Long
Dim AAOINtix2 As Long
Dim AAOINtix3 As Long
Dim AAOINtix4 As Long
Dim AAOINtix5 As Long
Dim AAOINrt As String
Dim RowCount As Long
Dim PortedData2 As Object
Dim sheetno As Integer
'Source File Directory
'Opens the dialog
wbSource = Application _
.GetOpenFilename("Excel Files (*.xlsx), *.xlsx")
On Error GoTo noSource
If wbSource <> False Then
MsgBox "Open " & wbSource
End If
'opens the selected file
Set wbReport = Workbooks.Open(wbSource)
LinkBack URL
About LinkBacks
hahas018 
Register To Reply
below their posts.
Originally Posted by Norie
Bookmarks