Does Sheet Exist in another Workbook, how to reference sheet

H

hayden

Board Regular
Joined
Sep 23, 2005
Messages
188
I think I am in way over my head. I have been writing some code to import a new csv file. I have two workbooks of interest. The first workbook is Main Data Entry.xls and this is where the code will be run. The second workbook is Location Details and it contains data entered by the user at another time.

The ultimate goal is to have this new file be placed on a new worksheet (in the Main Data Entry.xls file) named something like Grout 9-30-05 (ie. a location and a date). The user supplies this information via two userforms. One is called CreekSlection the other is called sampledate (both located under the Main Data Entry.xls workbook). Each of these forms aquires its input and then sets a cell on the Main Data Sheet equal to that input (this is the only way that I know of to transfer data from a userform into a module, although I am quite new at all this so I am sure that there is a better way).

This new sheet needs a few things added to it. Like the date the data was created, the location that it came from, and the hard part, information from the Location Details workbook.

A little more about the Location Details workbook before I continue. The location details workbook is a storage of details about the site and specifically info about when this csv file that is to be imported was aquire (it is from a field datalogger). The user enters the information like date data downloaded, did level loger change elevation, etc. This information is then saved to a new sheet using the date the user enters as date data was downloaded. Here is an example of the user created sheet in Location Data:
LocationDetails.JPG


So as I am sure most of you can see I have quite a few issues to deal with. Such as well what if the dates don't match up in the two workbooks. There would be no sheet to get data from.

The question that I need help with ... atleast right now is this:

Is is possible to ask the user to select the corresponding sheet from the Location Data Workbook that matches the csv file that they are about to import? With this sheet selected how do I call it when copying data from it to the Main Data Entry Workbook, (on the new sheet just created).

Here is my code so far. I have tried to comment it so that I know what I am doing. I have also left a few lines blank where I am trying to implement the user selected data.

Code: 
Sub ImportCSVa()
'This routine imports the csv file that was just saved from global loggers software.

Dim CurrentDump As Variant
Dim Wb As Workbook
Dim ws As Worksheet
Dim wsNew As Worksheet
Dim location As String
Dim importdate As String
Dim importwb As Worksheet

' This doesn 't update the screen as the program progesses.
    Application.ScreenUpdating = False

'set currentdump = filename of selected file (must be csv or txt file)
    CurrentDump = Application.GetOpenFilename("Please select a Global Logger CSV File,*.csv; *.txt", _
    1, "Please select a Global Logger CSV File", , False)

'Checks to see if user selected a file if they don't it just ends the routine
    If TypeName(CurrentDump) = "Boolean" Then Exit Sub

'Calls the userform to determine which creek this data is from
    Call UserFormLoad
    
' Sets the location variables to the name of the creek slected by the user.
    location = Workbooks("Main Data Entry.xls").Sheets("main").Cells(3, 16).Value
    
'Calls the userform to determine the corresponding date
    Load sampledate
    sampledate.Show

'Sets the variable ldsheetname = to the date selected by the user.

    ldsheetname = Workbooks("Main Data Entry.xls").Sheets("main").Range("p4").Value
    
    
'USER SELECTION OF WORKSHEET FROM LOCATION DATA HERE!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!





    

'Opens the workbook of currentdump and sets it equal to the variable wb
    Workbooks.OpenText Filename:=CurrentDump, DataType:=xlDelimited, Comma:=True
    Set Wb = ActiveWorkbook

'Sets the ws worksheet vairable to the  Wb workbook variable active sheet.
    Set ws = Wb.ActiveSheet
'Opens a new workbook and sets it equal to wsnew.

    Set wsNew = Workbooks("Main Data Entry.xls").Sheets.Add(After:=Sheets(Sheets.Count))
'Select Sheet main for if statement
  'Workbooks("Main Data Entry.xls").Sheets.Select ("main")

'Renames the sheet.
    wsNew.Name = Workbooks("Main Data Entry.xls").Sheets("main").Range("P3").Text & " " & Month(Now) & _
    "-" & Day(Now) & "-" & Year(Now)
'Clears contents just in case
    wsNew.Cells.ClearContents
'Copies info from imported CSV file
    ws.Cells.Copy Workbooks("Main Data Entry.xls").Sheets(wsNew.Name).Range("A1")
'Closes New Workbook
    Wb.Close

'Below this is the formating of the new worksheet that was just created.
'Insert seven new rows at the top of the sheet
    Sheets(wsNew.Name).Select
    Rows("1:1").Select
    Selection.Insert Shift:=xlDown
    Selection.Insert Shift:=xlDown
    Selection.Insert Shift:=xlDown
    Selection.Insert Shift:=xlDown
    Selection.Insert Shift:=xlDown
    Selection.Insert Shift:=xlDown
    Selection.Insert Shift:=xlDown
' Set up the information headings that never change
    Sheets(wsNew.Name).Range("a1") = "DATE IMPORTED:"
    Range("a2").Value = "LOCATION DETAILS:"
    Range("a3").Value = "Gauge Height Before"
    Range("a4").Value = "LL Reading Before:"
    Range("a5").Value = "Gauge Height After:"
    Range("a6").Value = "LL Reading After:"
    Range("b9").Value = "Feet Imported"
    Range("d8").Value = "DATE AND TIME INFORMATION"
    Range("D9").Value = "Year"
    Range("e9").Value = "Month"
    Range("f9").Value = "Day"
    Range("g9").Value = "Time"
'Now the harder part, assigning values that change.
'Sets the date imported today's date
    Range("b1").Value = " " & Month(Now) & _
    "-" & Day(Now) & "-" & Year(Now)

    Range("d2").Value = location & "Creek"


'Aquires the info from the Location Details worksheet.
'Need an if statement to detrmine which info to get.




'CALL DATA FROM USERSELECTED SHEET HERE!!!!!!!!!!!!!!!!!!!!!!!!!!!!






        If location = "Grout" Then
        Range("b3").Value = Workbooks("Location Details.xls").Sheets(ldsheetname.Value).Range("b9")
        Range("b4").Value = Workbooks("Location Details.xls").Sheets(ldsheetname.Value).Range("b10")
        Range("b5").Value = Workbooks("Location Details.xls").Sheets(ldsheetname.Value).Range("b11")
        Range("b6").Value = Workbooks("Location Details.xls").Sheets(ldsheetname.Value).Range("b12")
        
        Else
        End If
'Updates Screen
    Application.ScreenUpdating = True
End Function

Also here is an example of the final sheet that I am after in the Main Data Entry.xls workbook. Originally the csv file contains all the information located in a8:b40 (The 40 rows is arbitrary as it could be bigger or smaller).

MainDataEntry.JPG


Thanks for all your help. I have gone from not knowing much about VBA to writitng all of this in only a matter of days.
 

Excel Facts

Move date out one month or year
Use =EDATE(A2,1) for one month later. Use EDATE(A2,12) for one year later.
Sort by date Sort by votes
Well I have solved some of this. The frist problem that I had, of checking to see if a page exsisted or not has been solved. I used the following code.

Code: 
'USER SELECTION OF WORKSHEET FROM LOCATION DATA HERE!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
ldsheetname = Workbooks("Main Data Entry.xls").Sheets("main").Range("p5").Value
Do Until ldsheetname = Workbooks("Main Data Entry.xls").Sheets("main").Range("p6").Value
    
    
    Select Case (MsgBox("There is no Location Data sheet with that date, please try again.", _
    vbOKCancel, "Try again."))
    Case vbOK: Load sampledate
    sampledate.Show
    Case vbCancel: Exit Sub
    End Select
    
    
    If sheetex(ldsheetname, "Location Details.xls") Then
    
    Else
    End If
    Loop
And
Code: 
Function sheetex(ldsheetname, BkName) As Boolean
Dim x As Object
      On Error Resume Next
      Set x = Workbooks(BkName).Sheets(ldsheetname)
      If Err = 0 Then sheetex = True _
      Else sheetex = False

However I am now stuck with calling data from a sheet in another workbook with a variable name. The name of the worksheet changes (and is always a date). The user just confirmed that the name of the sheet they want to use is the same as the value in Range P6 of the current workbook, which happens to be set to the variable ldsheetname which is dimmed as a string.

I tried to create a new variable ldsheet dimmed as a worksheet and use the line
Code: 
Set ldsheet.name = ldsheetname

But I get the error message invalid use of proerty with the .name highlighted.

As you can see in my frist post on this topic, I used this trick previously and it worked fine. What is the difference this time?

Just in case here is the entire code.

Code: 
Sub ImportCSVa()
'This routine imports the csv file that was just saved from global loggers software.

Dim CurrentDump As Variant
Dim Wb As Workbook
Dim ws As Worksheet
Dim wsNew As Worksheet
Dim location As String
Dim importdate As String
Dim importwb As Worksheet
Dim ll1 As String
Dim ll2 As String
Dim sg1 As String
Dim sg2 As String
Dim ldsheetname As String
Dim ldsheet As Worksheet


' This doesn 't update the screen as the program progesses.
    Application.ScreenUpdating = False

'set currentdump = filename of selected file (must be csv or txt file)
    CurrentDump = Application.GetOpenFilename("Please select a Global Logger CSV File,*.csv; *.txt", _
    1, "Please select a Global Logger CSV File", , False)

'Checks to see if user selected a file if they don't it just ends the routine
    If TypeName(CurrentDump) = "Boolean" Then Exit Sub
    
'Calls the userform to determine which creek this data is from
    Call UserFormLoad
    
' Sets the location variables to the name of the creek slected by the user.
    location = Workbooks("Main Data Entry.xls").Sheets("main").Cells(3, 16).Value
    
'Calls the userform to determine the corresponding date
    Load sampledate
    sampledate.Show

     
'USER SELECTION OF WORKSHEET FROM LOCATION DATA HERE!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
ldsheetname = Workbooks("Main Data Entry.xls").Sheets("main").Range("p5").Value
Do Until ldsheetname = Workbooks("Main Data Entry.xls").Sheets("main").Range("p6").Value
    
    
    Select Case (MsgBox("There is no Location Data sheet with that date, please try again.", _
    vbOKCancel, "Try again."))
    Case vbOK: Load sampledate
    sampledate.Show
    Case vbCancel: Exit Sub
    End Select
    
    
    If sheetex(ldsheetname, "Location Details.xls") Then
    
    Else
    End If
    Loop
    
    Set ldsheet.Name = ldsheetname
    
    If location = "Grout" Then
        sg1 = Workbooks("Location Details.xls").Sheets(ldsheet.Name).Range("b9")
        ll1 = Workbooks("Location Details.xls").Sheets(ldsheet.Name).Range("b10")
        sg2 = Workbooks("Location Details.xls").Sheets(ldsheet.Name).Range("b11")
        ll2 = Workbooks("Location Details.xls").Sheets(ldsheet.Name).Range("b12")
    
    ElseIf location = "Rathbun" Then
    
        sg1 = Workbooks("Location Details.xls").Sheets(ldsheetname).Range("e12")
        ll1 = Workbooks("Location Details.xls").Sheets(ldsheetname).Range("e10")
        sg2 = Workbooks("Location Details.xls").Sheets(ldsheetname).Range("e11")
        ll2 = Workbooks("Location Details.xls").Sheets(ldsheetname).Range("e12")
    
    ElseIf location = "Kinckerbocker" Then
        sg1 = Workbooks("Location Details.xls").Sheets(ldsheetname).Range("h12")
        ll1 = Workbooks("Location Details.xls").Sheets(ldsheetname).Range("h10")
        sg2 = Workbooks("Location Details.xls").Sheets(ldsheetname).Range("h11")
        ll2 = Workbooks("Location Details.xls").Sheets(ldsheetname).Range("h12")
    Else
        sg1 = Workbooks("Location Details.xls").Sheets(ldsheetname).Range("k12")
        ll1 = Workbooks("Location Details.xls").Sheets(ldsheetname).Range("k10")
        sg2 = Workbooks("Location Details.xls").Sheets(ldsheetname).Range("k11")
        ll2 = Workbooks("Location Details.xls").Sheets(ldsheetname).Range("k12")
        End If

'Opens the workbook of currentdump and sets it equal to the variable wb
    Workbooks.OpenText Filename:=CurrentDump, DataType:=xlDelimited, Comma:=True
    Set Wb = ActiveWorkbook

'Sets the ws worksheet vairable to the  Wb workbook variable active sheet.
    Set ws = Wb.ActiveSheet
'Opens a new workbook and sets it equal to wsnew.

    Set wsNew = Workbooks("Main Data Entry.xls").Sheets.Add(After:=Sheets(Sheets.Count))
'Select Sheet main for if statement
  'Workbooks("Main Data Entry.xls").Sheets.Select ("main")

'Renames the sheet.
    wsNew.Name = Workbooks("Main Data Entry.xls").Sheets("main").Range("P3").Text & " " & _
    Workbooks("Main Data Entry.xls").Sheets("main").Range("p4").Text
    
'Clears contents just in case
    wsNew.Cells.ClearContents
    
'Copies info from imported CSV file
    ws.Cells.Copy Workbooks("Main Data Entry.xls").Sheets(wsNew.Name).Range("A1")
'Closes New Workbook
    Wb.Close

'Below this is the formating of the new worksheet that was just created.
'Insert seven new rows at the top of the sheet
    Sheets(wsNew.Name).Select
    Rows("1:1").Select
    Selection.Insert Shift:=xlDown
    Selection.Insert Shift:=xlDown
    Selection.Insert Shift:=xlDown
    Selection.Insert Shift:=xlDown
    Selection.Insert Shift:=xlDown
    Selection.Insert Shift:=xlDown
    Selection.Insert Shift:=xlDown
' Set up the information headings that never change
    Sheets(wsNew.Name).Range("a1") = "DATE IMPORTED:"
    Range("a2").Value = "LOCATION DETAILS:"
    Range("a3").Value = "Gauge Height Before"
    Range("a4").Value = "LL Reading Before:"
    Range("a5").Value = "Gauge Height After:"
    Range("a6").Value = "LL Reading After:"
    Range("b9").Value = "Feet Imported"
    Range("d8").Value = "DATE AND TIME INFORMATION"
    Range("D9").Value = "Year"
    Range("e9").Value = "Month"
    Range("f9").Value = "Day"
    Range("g9").Value = "Time"
'Now the harder part, assigning values that change.

'Sets the date imported today's date
    Range("b1").Value = Workbooks("Main Data Entry.xls").Sheets("main").Range("p4").Value
    Range("d2").Value = location & "Creek"
' Because I defined all the variables that I needed at the top I should be able to output them here
    Range("d3").Value = sg1
    Range("d4").Value = ll1
    Range("d5").Value = sg2
    Range("d6").Value = ll2

     
              
     'Updates Screen
    Application.ScreenUpdating = True
    
    End Sub


'Loads the userform that selects which item goes with whcih csv File.
Private Sub UserFormLoad()
    Load CreekSelection
    CreekSelection.Show
End Sub



Function sheetex(ldsheetname, BkName) As Boolean
Dim x As Object
      On Error Resume Next
      Set x = Workbooks(BkName).Sheets(ldsheetname)
      If Err = 0 Then sheetex = True _
      Else sheetex = False
            
      End Function

Thanks again for all the help.

Hayden
 
Upvote 0
You must log in or register to reply here.

Similar threads

D
  • Solved
VBA Excel OpenDialogBox then Transfer the information to cell
Replies
5
Views
180
Daniza Morla
D
P
  • Solved
VBA to open file using Cell for the path
Replies
4
Views
547
Peter Davison
P
J
  • Question
Macro to load 10 files from different folder into different sheets in Master File
Replies
2
Views
124
joxyin
J
I
  • Question
VBA - Copy Sheet from a workbook saved as a date - paste to specifically named sheet in another workbook based on the date
Replies
2
Views
205
Insurance Dave
I
D
  • Solved
Run-time error 9, Subscript out of range - Copying sheet from closed workbook to active workbook as new sheet
Replies
9
Views
291
dvward
D

Forum statistics

Threads
1,214,651
Messages
6,120,739
Members
448,989
Latest member
mariah3

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top