How to set the source file path

[baltog]

Hi,

Can u help me how to set the path for my excel application?

Private xlSrcApp As Excel.Application
Private xlSrcBook As Excel.Workbook
Private xlSrcSheet As Excel.Worksheet

How do i set the path or source for xlSrcApp?

tnx in advance,
Rodel

 

[RobBroekhuis]

Not sure what you mean. You can use

set xlSrcApp=new excel.application

to create an instance of Excel (alternatively, you can use

set xlSrcApp=CreateObject("excel.application&quot


Rob

 

[baltog]

Hi Rob,

Here's the problem. I want to copy the data from one excel file to another excel file. I already have done the destination file which im created using filesystemobject in my code. Before im getting data to be copied in the destination file from one excel file but in this case i used recordset. I used the path of the excel file(source file) as datasource of the recordset. THis is working already but it came to another problem. The recordset is holding only 255 chars but some cell in the source file is holding more than 255 chars. So i have come to an idea that i should copy the data from cell to cell. From Source file to another file. Question: How do i activate the source file which is existing. Let say: C:\SourceExcelFile.xls


tnx,
ROdel

 

[CubeE101]

is this the same question as thread222-442272 Sometimes... the BASIC things in life are the best...

or at least the most fun ;-)
-Josh Stribling

 

[CubeE101]

Try This...
Copy Paste this in Excel macro Editor (Personal.xls)

'*** a macro to open a new workbook and copy cells from an existing (active) one
Sub CopyData()
Dim xlBook1 As Excel.Workbook, xlBook2 As Excel.Workbook
Dim xlSheet1 As Excel.Worksheet, xlSheet2 As Excel.Worksheet

'*** Set xlBook1 to the Active WorkBook in Excel
Set xlBook1 = Excel.ActiveWorkbook
'*** You can also use this to open a file
'set xlbook1 = excel.Workbooks.Open("MyFile.xls")
Set xlSheet1 = xlBook1.ActiveSheet

'*** Set xlBook2 to a NEW WorkBook in Excel
Set xlBook2 = Excel.Workbooks.Add
Set xlSheet2 = xlBook2.ActiveSheet

'*** Copy a few cells accross to the new workbook
For x = 1 To 5
  For y = 1 To 5
    xlSheet2.Cells(y, x) = xlSheet1.Cells(y, x)
  Next
Next

'*** Save the new work book
xlBook2.SaveAs
End Sub

Sometimes... the BASIC things in life are the best...

or at least the most fun ;-)
-Josh Stribling

 

[CubeE101]

Question: How do i activate the source file which is existing. Let say: C:\SourceExcelFile.xls

Answer:
Excel VBA:
Dim xlbook1 as Excel.Workbook
set xlbook1 = Excel.Workbooks.Open("C:\SourceExcelFile.xls&quot

VB 5/6:
Dim xlApp as Excel.Application
Dim xlbook1 as Excel.Workbook
set xlApp = new Excel.Application
xlApp.Visible = True
set xlbook1 = xlApp.Workbooks.Open("C:\SourceExcelFile.xls&quot

Let me know if this does not work...
Good Luck ;-)
-Josh Sometimes... the BASIC things in life are the best...

or at least the most fun ;-)
-Josh Stribling

 

[baltog]

Hi Josh,

Tnx so much for codes you provided. Sorry for the late response. I was able to fix the bug using the first code you gave:

Workbooks.Add "C:\SourceExcelFile.xls"
Set xlSrcSheet = Workbooks(2).Worksheets(1)
Workbooks(2).Activate

Regards,
Rodel Ocfemia

 

[CubeE101]

Sure...
No Problem Sometimes... the BASIC things in life are the best...

or at least the most fun ;-)
-Josh Stribling